I started using this method to export other types of data, in an attempt to create a dashboard for our clients. I quickly found that Impromptu is not well suited or reliable enough for scheduling multiple jobs.
The requirements and caveats for using Impromptu in this manner are:
- Each report must be scheduled so as to not be running when the next is scheduled. Impromptu will not allow the next scheduled report to wait too long, and this may cause the "chain" of reports to fail.
- The workstation running the reports must have a user logged in with the Cognos Scheduler application running in order to fire the reports off. It cannot run via the Windows Scheduled Tasks (AT for us old-timers).
- If you run your reports against multiple companies, as we do, a macro will be required to be run to switch from company to company between reports.
What I discovered was that it was quite simple to connect to the Micro Focus Cobol Server Express data files. A DSN connection string is created for each company we connect to for AllScripts Query, so the following is a sample of how to retrieve all Insurance Plans for a given company:
Dim Conn As ADODB.Connection Dim Rs As ADODB.Recordset Dim oFile As Scripting.FileSystemObject Dim strSql As String Dim strConn As String strSql = "SELECT T1.PLAN_INS_NUM c1 , T1.PLAN_NUMBER c2 , T1.PLAN_NAME c3 , T1.PLAN_ADDRESS1 c4 , T1.PLAN_ADDRESS2 c5 , T1.PLAN_CITY c6 , T1.PLAN_STATE c7 , T1.PLAN_ZIP c8 , T1.PLAN_PAT_TYPE c9 , T1.PLAN_NOTES c10 , T1.PLAN_PHONE1 c11 , T2.P_NAME c12 from P01 T2, INS_PLANS T1 Where T2.P_NUMBER <= T1.PLAN_NUM_KEY order by 1 asc , 2 asc" strConn = "DSN=Company_" & strCompany & ";uid=MyUid;pwd=MyPass" Set Conn = New ADODB.Connection Conn.Open strConn Set Rs = New ADODB.Recordset Rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer Rs.Open strSql, Conn, adOpenForwardOnly, adLockReadOnly Set oFile = New Scripting.FileSystemObject Dim ouFile Set ouFile = oFile.OpenTextFile("\\MyServerPath\" & strName & "InsurancePlan.csv", ForWriting, True) ' Write the header ouFile.Write Chr(34) & "insurance_number" & Chr(34) & "," & _ Chr(34) & "plan_number" & Chr(34) & "," & _ Chr(34) & "name" & Chr(34) & "," & _ Chr(34) & "address1" & Chr(34) & "," & _ Chr(34) & "address2" & Chr(34) & "," & _ Chr(34) & "city" & Chr(34) & "," & _ Chr(34) & "state" & Chr(34) & "," & _ Chr(34) & "zip" & Chr(34) & "," & _ Chr(34) & "default_pt_type" & Chr(34) & "," & _ Chr(34) & "notes" & Chr(34) & "," & _ Chr(34) & "phone" & Chr(34) & vbCrLf Do While Not Rs.EOF ouFile.Write Chr(34) & Trim(Rs.Fields.Item(0)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(1)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(2)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(3)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(4)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(5)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(6)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(7)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(8)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(9)) & Chr(34) & "," & _ Chr(34) & Trim(Rs.Fields.Item(10)) & Chr(34) & vbCrLf Rs.MoveNext Loop Rs.Close Conn.CloseThe company number is set via a required argument to the program I've written, as well as the report type, as I have several sub-routines that determine the type of data to dump.
You can refer to the article mentioned above to review how to import via DTS (Data Transformation Services).
My data is sent to GoDaddy (ya, ya, ya...some day we'll get a real hosting company) via the Microsoft SQL Server Database Publishing Wizard. It is script-able, and this is the way I use it today. You can get it directly from CodePlex, or review GoDaddy's knowledgebase about it.
Running sqlpubwiz.exe help publish will give you a list of command line options. You may use these to form your connection manually, or you can take the lazy route like I did, and run the GUI. This will allow you to create your connections in a permanent manner, easily referenced by aliases in your script. You'll want to remember to include the path to the program in your PATH environment, or adjust your scripts accordingly.
There is a nice article at GoDaddy for the steps required to publish your database, accompanied by nice screenshots. You'll want to review this article to determine what can and can't be uploaded, and database space limitations. One of the things you won't find, is information regarding what I found; even though the data I attempted to send was described as being 156mb in size by running EXEC sp_spaceused on the table I intended to upload, GoDaddy will not allow it to be uploaded, because combined, the two exceed the 200mb limit, and the destination isn't dropped before the source gets uploaded. This required me to make some modifications to my data size, and what I needed to do before I uploaded it.
The biggest hurdle for me was to determine how to form my queries, as I had no intimate knowledge of the database schema. This was quickly remedied by Impromptu. After running a report, go to the Report pull-down menu item, and select Query.
SQL option. This will reveal the SQL query used to retrieve your data. You'll notice that if you've got some sophisticated grouping, sorting or calculations happening in your report that they aren't necessarily represented here. That's because there is a limited number of built-in functions in legacy C-ISAM "databases", and Improptu handles much of that, sometimes at a high processing cost to your workstation.
Your best approach is to strip all your grouping, sorting and calculations out of your report, then copy and paste this SQL into Transoft's Win U/SQLi32 to verify you are capturing the data you want. Then you can massage your data a bit in VB, during the DTS import process, or ultimately when you regurgitate your data for other uses, because you will find that there are only a few SQL functions available through Micro Focus Cobol Server Express.
|Image via CrunchBase|