In this write up I am trying to demonstrate the following features of DTS (SQL Server 2000). These are features that you can use to enhance the packages that you must write or maintain on SQL Server 2000.

1. Manipulating Recordsets in DTS
2. Lookups in DTS
3. Logging errors to a Text file from DTS
4. Connecting to a database from script in DTS
5. Dynamically setting Source File/Folder path for connections in DTS 2000

Each of these examples was tested in an environment of Windows XP and SQL Server 2000. All the examples in this writeup assume you know how to work in the DTS Designer in Enterprise Manager. The focus is on demonstrating specific

features using workable examples.

TOPIC 1 - Manipulating Recordsets in DTS (SQL Server 2000)

Objective: To iterate through a recordset in DTS. These recordsets have all the full-blown features available in ADO recordsets.

Need: This feature will be helpful when we want to work with lookup tables, without going to the database everytime we wish to lookup something.

Caution: Note that this is a Disconnected recordset feature and can be a serious performance bottleneck, if you try to use this feature for large recordsets.

With the requirements clear, let us start straight away:

Step 1 - DDL and DML setup

Create the following table and insert the rows

create table jk_rstest(f1 int, f2 int)
insert into jk_rstest values(111,111)
insert into jk_rstest values(222,222)
insert into jk_rstest values(333,333)
insert into jk_rstest values(444,444)
insert into jk_rstest values(555,555)

Step 2

Add a "Microsoft OLE DB Provider for SQL Server" connection object on to the

designer. And select the database you create the table specified in Step 1.

Step 3

Write click on the designer surface (not on any task object) and select Properties

Select the Global Variables tab

Enter "rows" for the Name (without quotes)

Select <other> for Type and leave Value column blank, then click OK

Step 4

Add an "Execute SQL Task" task on to the designer

Enter the following sql statement in the query window

select * from jk_rstest

Click on Parameters -> Output Parameters

Select the Rowset radiobutton and select "rows" from the drop down

Click OK two times

Step 5

Add an "ActiveX Script Task" on to the designer and add the following code inside it:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
    Function Main()
dim countr
dim RS
set RS = CreateObject("ADODB.Recordset")
set RS = DTSGlobalVariables("rows").value
for countr = 1 to RS.RecordCount
    MsgBox "The value is " & RS.Fields("f1").value
    RS.MoveNext
Next
Main = DTSTaskExecResult_Success
    End Function

Step 6

Holding CTRL, select the Execute SQL Task first and then the ActiveX Script

task. Right click. Select Workflow -> On Success from the Menu at the top of the designer.

That's it. Go ahead and execute the package.

TOPIC 2 - Lookups in DTS

Objective: A straightforward use of this feature would be to lookup in a table to see if a particular record already exists.

Need: To ensure PK violations do not occur, for example we can lookup the table for the incoming value and log an error.

Straightaway to code.

Step 1 - DDL and DML setup

create the following table and insert the rows
create table jk_lookupTest(f1 int Primary key, f2 int)
insert into jk_lookupTest values(111,111)
insert into jk_lookupTest values(222,222)
insert into jk_lookupTest values(333,333)
insert into jk_lookupTest values(444,444)
insert into jk_lookupTest values(555,555)

Step 2

Create a textfile c:\whatever\lookuptestSource.txt with the following data:

111,111
999,999

The first row will exist in the table and the second row will not when we lookup the pk field.

Step 3

Add a "Text File (Source)" connection and select "c:\whatever\lookuptestSource.txt for the file"

Click on Properties

Click on Next

Click on the scale after the third character. You should see a divider between the two fields.

Click Finish

Click OK

Step 4

Add a "Microsoft OLE DB Provider for SQL Server" connection and select the database you create the table in Step 1

Step 5

Add a "Transform Data Task" on to the designer. The arrow should be pointing from Source to Destination.

Double click and open the Task's properties.

On the Destination tab select jk_lookuptest as the table

In the Lookups tab enter "MyLookup" for Name (without quotes)

Select the Database connection you made in Step 2 and click on the Query button

Enter this code

SELECT COUNT(f1) AS Expr1 FROM jk_lookupTest where f1 = ?

in the bottom pane of the ensuing window.

On the Transformations tab click on New (If any Transformations showup in the Name drop down, select and delete them)

Select ActiveX Script and click on OK

Select all columns from Source Columns and Destination Columns tab

On the General tab click on Properties and in ensuing editor enter the following code and then click on OK three times

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
Function Main()
DTSDestination("f2") = DTSSource("Col002")
DTSDestination("f1") = DTSSource("Col001")
if DTSLookups("MyLookup").Execute(DTSSource("Col001"))  > 0 then
Msgbox( DTSSource("Col001") & "---" & " already exists in the db")
Main = DTSTransformStat_SkipRow
else
Msgbox(DTSSource("Col001") & "---" & " being entered into the db")
Main = DTSTransformStat_OK
end if
End Function

That's it. Go ahead and execute the package.

TOPIC 3 - Logging errors to a Text file from a DTS script

Extending the script in TOPIC 2 enter the following code outside of Main

Dim filesys, testfile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set testfile= filesys.CreateTextFile("c:\whatever\ErrorFIle.txt", True)
testfile.Write "Error records:          " & vbcrlf

and the following code in the error path of the 'IF'

testfile.Write DTSSource("Col001") & "---" & " already exists in the db" & 
vbcrlf
'testfile.Close

The entire code should look like this

Dim filesys, testfile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set testfile= filesys.CreateTextFile("c:\whatever\ErrorFIle.txt", True)
testfile.Write "Error records:          " & vbcrlf
Function Main()
DTSDestination("f2") = DTSSource("Col002")
DTSDestination("f1") = DTSSource("Col001")
if DTSLookups("MyLookup").Execute(DTSSource("Col001"))  > 0 then
Msgbox( DTSSource("Col001") & "---" & " already exists in the db")
testfile.Write DTSSource("Col001") & "---" & " already exists in the db" & 
vbcrlf
'testfile.Close
Main = DTSTransformStat_SkipRow
else
Msgbox(DTSSource("Col001") & "---" & " being entered into the db")
Main = DTSTransformStat_OK
end if
End Function

That's it. Go ahead and execute the package.

You should now see a file in c:\whatever with the details of the error records logged, like this

Error records:
111--- already exists in the db

Note that this is not exception handling. We are trying to handle "possibly could occur" errors gracefully.

TOPIC 4 - Connecting to a database from script in DTS

Step 1 - DDL and DML as always.

Create the following table and insert the rows

create table t1(f1 int, f2 int)
insert into t1 values(111,111)
insert into t1 values(222,222)
insert into t1 values(333,333)
insert into t1 values(444,444)
insert into t1 values(555,555)

Step 2 - Writing the script

Add an 'Active Script Task" on to the DTS Designer

In the ensuing editor window enter the following code. Note that I am using Windows Security (not sa)

Also change the database name to your database name in "Initial Catalog=MyDB"

Function Main()
   dim con, rs, conString
   set con = CreateObject("ADODB.Connection")
   set rs = CreateObject("ADODB.Recordset")
   conString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
   conString = conString & "Persist Security Info=False;Initial Catalog=MyDB;Data Source=(local)"
   con.Open conString
   set rs = con.execute("select * from t1")
   do while not rs.eof
      MsgBox "The value is " & rs.Fields("f1").value
      rs.MoveNext
   loop
   Main = DTSTaskExecResult_Success
End Function

That's it. Go ahead and execute the package.

TOPIC 5 - Dynamically setting Source File/Folder path for connections in DTS 2000

Step 1

p>

This part of the writeup demonstrates how to use environment variables to set source paths for these connections:

• "Text File (Source)" connection
• "Microsoft OLE DB Provider for SQL Server" connection
• "Microsoft Excel 97-2000" connection

and one example of how we can use Global variables as well. I would recommend usage of Env variables over global variables beecause, when we move from dev to QA or QA to production etc I would like to avoid any changes (ANY changes) to the DTS package. But I am including an example here, so that we know if situation demands, we have a way out as well.

Step 2

Setup two environment variables

Variable name: PathToSourceTextFiles

Variable value: c:\whateverTextFiles\

Variable name: PathToExcelFiles

Variable value: c:\whateverExcelFiles

Step 3

For testing the Global variables route, create a DTS Global variable "SourceFilePath" and set it to c:\whateverTextFiles

Step 4

Create two text files "File1.txt" and "File2.txt" in c:\whateverTextFiles

Create an Excel file "MyExcelFile1.xls" in c:\whateverExcelFiles

Step 5

Drop two "Text File (Source)" connections and name then "SourceFile1" and "SourceFile2" without the quotes.

Then drop a "Microsoft OLE DB Provider for SQL Server" connection and name it "DBConn1" without the quotes.

And then an "Microsoft Excel 97-2000" connection and name it "MyExcelConn" without the quotes.

While dropping these connections, if you want to set some initial values, it is fine. We will be changing them dynamically anyway, which is the objective of this script. Also do not worry about what is inside these text and excel files.

Then drop an ActiveX Script task. And here is the code. Follow the comments along with the code for understanding what I am doing.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
'USING ENV VARIABLES
'==
'declare some variables
'-----------------------------------
Dim wshShell, PathToSourceTextFiles
Dim SourceConn1, SourceConn2, DBConn1, x
'TEXT FILE CONNECTIONS
'===
'Get the value from the environment variable
'--------------------------------------------------------------------
Set wshShell = CreateObject("Wscript.Shell")
PathToSourceTextFiles = wshShell.ExpandEnvironmentStrings("%PathToSourceTextFiles%")
'Get the Text file connections and assign new path and filenames
'------------------------------------------------------------------------------------------------------
Set SourceConn1 = DTSGlobalVariables.Parent.Connections("SourceFile1")
SourceConn1.DataSource = PathToSourceTextFiles & "File1.txt"
Set SourceConn2 = DTSGlobalVariables.Parent.Connections("SourceFile2")
SourceConn2.DataSource = PathToSourceTextFiles & "File2.txt"
'CHANGING THE SERVER AND DATABASE
'
Set DBConn1 = DTSGlobalVariables.Parent.Connections("DBConn1")
DBConn1.DataSource = "MyDBServer"
DBConn1.ConnectionProperties("Initial Catalog").Value = "MyDB"
'EXCEL IS NO DIFFERENT
'===
Dim MyExcelConn, PathToExcelFiles
Set wshShell = CreateObject("Wscript.Shell")
PathToExcelFiles = wshShell.ExpandEnvironmentStrings("%PathToExcelFiles%")
Set MyExcelConn = DTSGlobalVariables.Parent.Connections("MyExcelConn")
MyExcelConn.DataSource = PathToExcelFiles & "MyExcelFile1.xls"
'USING GLOBAL VARIABLES
'=====
'Set SourceConn1 = DTSGlobalVariables.Parent.Connections("SourceFile1")
'SourceConn1.DataSource = DTSGlobalVariables("SourceFilePath").Value & "File1.txt"
'Set SourceConn1 = Nothing
'Set SourceConn2 = DTSGlobalVariables.Parent.Connections("SourceFile2")
'SourceConn2.DataSource = DTSGlobalVariables("SourceFilePath").Value & "File2.txt"
'Set SourceConn2 = Nothing
'This piece of code will be helpful for you to see all the properties for these objects
'==
'for each x in DBConn1.ConnectionProperties
'msgbox(x.Name & "---" & x.Value)
'next
'for each x in DBConn1.Properties
'msgbox(x.Name & "---" & x.Value)
'next
'msgbox(DBConn1.Properties.count)
Main = DTSTaskExecResult_Success
End Function

Step 6

That's it. Execute the ActiveX Script task alone. (Do not execute the entire package, because the objective of this writeup is only to the extent of showing how to set values DTS 2000 values dynamically. If you now go back to the designer and look into your objects, you will see the changes you made dynamically. Just for reference, here is an image:

Conclusion

Along the way we have seen how to use Global variables, File handling, Output Parameter handling from SQL, Parameterized queries etc. apart from the main focus of this writeup, which was on the five items

mentioned at the very beginning .

Thankz for reading!