- The Project
- Project Challenges
- Resolving the Challenges
- Optimizing DTS Performance
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Optimizing DTS Performance
You can develop your own VB components using the DTS Object Model. The most important objects in the DTS hierarchy are Package, Task, and Steps. The DTS Package is the object that holds all other DTS objects. All work, logic, and flow of data are done within the DTS Package. Steps and Tasks are contained within the DTS Package. A Task is what you see in the Designer under the Add Task menu item. These Tasks do the actual work of the Package. A Step is something that you won't see in the DTS Designer (if you right-click a Task, you'll see an option to execute a Step). A Step is what controls the Task. There is a one-to-one correlation between a Task and a Step. For every Task you have, you'll have a corresponding Step that controls the Task and tells it when to "go." When you create Workflows, they actually connect to a Step, not a Task. You can see this if you go to a Workflow property: It asks you what Step to use for the Source and what Step for the Destination.
The following code module is borrowed from the postal application described earlier in the article. The business rules dictated that much of the information the DTS Package would use was not known until runtime. DTS seemed to be the best option for moving this data around, but because so much information was not known upfront, it also seemed best handled by dynamically building a DTS Package and then running it after it was built. The overhead to create the Package each time was minimal, and because each Package was different, it would have taken a lot of time to build enough Packages to handle every situation I planned to encounter. I also didn't save Packages on a normal operation. I had a flag that I could set at runtime to indicate to save the Package to assist in debuggging, so that we didn't have a myriad of Packages out there. After this engagement, I now often find it more practical to build a Package dynamically in all but small, simple Packages. To make the code efficient, I used enumerations, which is a good practice because it is difficult to keep track of many of the different options. I had enumerations for source and destination file types, Workflow constraint types, and data types. This way, I didn't have to remember that an Excel File was 1, Access was type 2, and so on. Here are the three enumerations that I used:
'The file types that are supported by this component... Public Enum DTSFileTypes DTSCSV = 1 DTSMDB = 2 DTSXLS = 3 DTSDBF = 4 DTSTXT = 5 End Enum 'The three types of Workflow Constraints... Public Enum DTSWorkFlowConstraints WFSuccess = 0 WFFail = 1 WFComplete = 4 End Enum 'The Datatypes and their corresponding values for column collections... Public Enum DTSXFormDataTypes DTSSmallInt = 2 DTSInt = 3 DTSReal = 4 DTSFloat = 5 DTSMoney = 6 DTSSmallMoney = 6 DTSBit = 11 DTSTinyInt = 17 DTSBigInt = 20 DTSUniqueIdentifier = 72 DTSTimeStamp = 128 DTSChar = 129 DTSDecimal = 131 DTSDateTime = 135 DTSSmallDateTime = 135 End Enum