Dynamically Setting properties for DTS tasks

DTS is a workflow application designed by microsoft for as the name suggests Data Transformation Services these can be extensively used in data import and export routines to multiple data sources.

I recently has this project where we had to export data to certain folders on an FTP folder on a preset time interval. Like every 10 minutes we have to create file for all the sales orders that had been shipped in that interval. First we thought of creating an application to do this but then as we were short of time we thought of using the inbuilt DTS services to achieve the same.

Designing the workflow was an easy task the issue was how to dynamically rename the file being created every 10 min we wanted a mechanism to create the text file name based on the server time stamp. Finally we knew that we could used the Active script to achieve the same.

The script we used was pretty simple below is the code.

Function Main()
DTSGlobalVariables("SaleLineFileName").Value = "C:\Tmp\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Time()) & "-" &Minute(Time()) & "-" & Second(Time()) & ".txt"

Set pkg = DTSGlobalVariables.Parent
Set conTextFile = pkg.Connections("salesline")
conTextFile.DataSource = DTSGlobalVariables("SaleLineFileName").Value

Main = DTSTaskExecResult_Success
End Function


Another requirement was to read a no of lines from a folder and to import all the lines that existed there below is the script

Function Main()

'Defines the folder to read filenames from
sFolder = "D:\Folder\"

'Defines an object variable for the package
Set pkg = DTSGlobalVariables.Parent

'Defines the connection for the text file source on which you will read for the data import
'This is what we will be setting as a dynamic value
Set conTextFile = pkg.Connections("Text File (Source)")

'Defines the object variable for the Transform Data Task that transforms the data from the text file to the database
Set pumpTask = pkg.Steps("DTSStep_DTSDataPumpTask_1")

'This is where we will read through the filenames
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set folder = oFSO.GetFolder(sFolder)
Set files = folder.Files

'Iterate through all the files in the folder and retrieve their filenames
For each itemFiles In files
'Use a variable for the complete path of the file
sFileName=sFolder & itemFiles.Name

'Assign the Data Source of the text file
conTextFile.DataSource = sFileName

'Execute the Transform Data Task
pumpTask.Execute

'Assign the path of the filename to a global variable. This is needed as you will have to
'define a global variable for your text file source in you DTS package
DTSGlobalVariables("importFilename").Value = sFileName
Next

'Returns a value to the task that the execution was successful
Main = DTSTaskExecResult_Success

End Function