Browse DevX
Sign up for e-mail newsletters from DevX


Coordinate Your DTS Tasks, Part II: Import an Indeterminate Amount of Excel Data into SQL Server-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The File Name Extraction
So the global variables enabled the plumbing to feed the stream of Excel workbook file names into the child package. As mentioned above, the Dynamic Properties task acts as the receiver for the value contained in the Global Variable gcFileNam (see Figure 3). Every iteration of the process feeds a new file name to the child. The Dynamic Properties task lets you reference the Parent's Outer Variables, which the Parent assigns in the header package and which the child reads as "incoming". The contents of the Global Variable are used to dynamically change the data source property of the Excel connection (see Figure 4). Sweet.

Click to enlarge
Figure 3: Specifying the Named Package Variables as Global

Click to enlarge
Figure 4: Specifying the Data Source for the Child's Dynamic Properties Task

Specifying the Data Source for the child's Dynamic Properties task, Dynamic, because they can be changed during package execution via the Global Variable.

The problem remaining was how to use these dynamically assigned data sources to invoke the child package's extraction routine. Enter the Active X scripting task. Active X scripts are as useful as they are unfriendly (i.e., the script will parse correctly but that doesn't mean it will do as you intend). There is no IntelliSense available and you can't include other objects by References. In other words, any objects you need have to be created by CreateObject rather than New. All in all, it is a pretty hostile environment.

Luckily, you don't have to stay there long. You need only create a reference to the child package, load up the Global Variables, and send it. The following is the code for the Active X script:

'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() dim rst set rst = createobject("adodb.recordset") set rst = dtsglobalvariables("gncount").value rst.movefirst Dim oPackage Dim sServer, iSecurity, sPackageName sServer = "NTSRV2" iSecurity = DTSSQLStgFlag_UseTrustedConnection sPackageName ="DrewExperiment" set oPackage = CreateObject("DTS.Package2") oPackage.Name = sPackageName oPackage.LoadFromSQLServer sServer, , , iSecurity, , , ,sPackageName for i = 1 to rst.recordcount oPackage.GlobalVariables.Item(2).Value=rst.fields(0) oPackage.GlobalVariables.Item(1).Value=rst.fields(1) oPackage.Execute rst.movenext next oPackage.Uninitialize Set oPackage = Nothing Main =DTSTaskExecResult_Success End Function

The result of the select statement in the parent package is used as an index for the for next loop, and is transmitted by the Global Variable gncount.

DTS Package for the Child Package
The next issue is to create an instance of a DTS Package that represents the child package, whose functionality I described at the beginning of the article (basically, an Excel connection, a Transform Data task, and a SQL Server connection). Once the references are created, the for next loop refreshes the globals with the data contained in the file location table and executes the child package (see Figure 5). This way, you can feed the extraction mill with the information it needs to perform the data transfer from as many tables as exist in the Excel directory.

Click to enlarge
Figure 5: Active X Script Transformation

The last step was to create another small script in the child package to update the contents of the scrubbing table with the contents of the Global Variables. This one does not need a loop, because you are controlling the child package execution from the parent. It fires only once every time it's called. Figure 6 shows the result of the import.

Click to enlarge
Figure 6: Consolidation of Check Register Data in One Table

This figure shows the finished product: a consolidation of fifty workbooks worth of check register data in one SQL Server table.

Notice that over 11,000 lines of Excel spreadsheets have been consolidated into a SQL Server table. This seems to be crying out for some application development, since SQL Server is much more secure and robust. But you know Finance guys, they love them spreadsheets!

Finally, I created a wrapper for the parent and child process so that they could be executed with one click (or one call from a GUI). I accomplished this by connecting two Execute Process tasks with an "On Completion" precedence constraint (see Figure 7).

Click to enlarge
Figure 7: Two Execute Package Tasks Communicating with Each Other

This figure shows two Execute Package tasks communicating with one another via a precedence constraint, demonstrating the ability to modularize complex DTS packages.

The Execute Process tasks are very simple to invoke. You only have to provide the name of the task to run. Using these tasks lets you make multi-step DTS packages more modular and, paradoxically, easier to debug. For some reason, the error handling and messages are better from these top-level tasks. Whereas I found the lower-level error reporting, shall we say, inscrutable (see Figure 8 and Figure 9)?

Click to enlarge
Figure 8: Execute Package Dialog Asks for the Name and Location of the Other Package to Run

Click to enlarge
Figure 9: Execute Package Dialog Asks for the Name and Location of the Other Package to Run

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date