- Package2 Object
- Connection2 Object
- Task Object
- Step Object
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Task Object
For a package to do any work, we must have tasks. Task objects are responsible for the work done in a package. SQL Server 2000 has added several new tasks, and enhanced many of the original ones from SQL 7. In our simple example, we only use a Transform Data task, but you can see all available tasks by clicking on the Task menu item at the top of the DTS Designer window. You can also right-click anywhere in the DTS Designer window, and choose Add Task to see a list of the available tasks. You can even create your own tasks if you're so inclined (and if you have some advanced programming skills). You can find information in BOL about doing this to learn more.
To create our Data Transformation task, we declare a DTS.DataPumpTask2 object, and create it as follows:
Set <TaskObject> = <PackageObject>.Tasks.New("DTSDataPumpTask")
There are three very important properties that you need to pay attention to. The first one is the Name property, which we will need later when we create a step for this task. The other two are the source and destination ConnectionID. Remember that we discussed putting connection IDs into an enumeration. Here, we either have to look at our connection objects to get the Connection IDs for our connections; or if we had an enumeration, we could use them to make the code more readable and make it easier to tell what ID is for each connection. The Task object also needs to know the Source and Destination object names. The Source object in our case is the table we want to pull data from. The object names for SQL Server connections can be tables, views, stored procedures, or a SQL statement. Here, we give it the Customers table name. Because our destination connection is Excel, the ObjectName is the name of the worksheet of the Excel file (we called ours NWCustomers). We can also specify a FetchBufferSize property, how many rows to import as a batch, the MaximumErrorCount to indicate how many errors we should allow before aborting the package execution, whether to allow identity inserts, the first and last rows to export, and some exception file information.
We recommend using constants when setting DTS object properties. Setting a value to "1" may not have much meaning to you or someone else looking at your code down the road, but setting the same property to DTSExceptionFile_SingleFile70 tells us that we want a single exception file in the SQL 7.0 format just by looking at the code. Therefore, using the predefined constants makes your code more self-documenting and easier to maintain.
Within our task, we need to specify columns from the source and destination files we are using, and how to move the data between the two connections. We create a new DTSPump.DataPumpTransformScript object off of our transformation object to specify that we are going to use a script to control how the data will be transferred. We give this new object a name, and set several other properties. Then we add source and destination columns to this object's SourceColumns and DestinationColumns collections. We create a DTS.Column object, set the column name, an ordinal position, flags (see the DTS Help for more info on this one), information about the datatype, and whether the column is nullable. After we have defined the column, we add it to the proper source or destination column collection. After we have all of the columns added, we specify the oTransProps("Text ") property that contains the script to do the transformations. This part can be a bit tricky because we are writing VB code that is dynamically generating VBScript code for use in the Transformation object. Finally, we set the language and the entry point into the script, add this Transformation object to the Task object, and then add the Task object to the Package object.