Professional SQL Server™ 2005 Programming

19.3. Building a Simple Package

Okay, it's time for us to put some application to all this. This is going to be something of a quick-and-dirty example run, but, in the end, we will have shown off several of the key features of SSIS.

Let's start with a little prep work. For this sample, we're going to be making use of a vbScript file that will generate some data for us to import. You can think of this script as simulating any kind of preprocess script you need to run before a major import or export.

Create a text file called CreateImportText.vbs with the following code:

Dim iCounter
         Dim oFS
         Dim oMyTextFile

         Set oFS = CreateObject("Scripting.FileSystemObject")
         Set oMyTextFile = oFS.CreateTextFile("C:\TestImport.txt", True)

         For iCounter = 1 to 10
              oMyTextFile.WriteLine(cstr(iCounter) & vbTab & quot;""TestCol" & cstr(iCounter) &
"""")
         Next

        oMyTextFile.Close

This script, when executed, will create a new text file (or replace the existing file if it's there). It will add 10 rows of text to the file containing two tab-separated columns with a new line row terminator. We will use this in conjunction with a few other tasks to create and populate a table in SQL Server.

This is a pretty simplistic sample, so please bear with me here. What, in the end, I hope you see from this is the concept of running a preprocess of some sort (that's our script that generates the file in this case, but it could have been any kind of script or external process), followed by SQL Server scripting and data pump ...