One of the most difficult tasks to convert in DTS to SSIS is the ActiveX Script Task. DTS xChange converts this to a similar task inside of SSIS, the ActiveX Script Task but the task may not work. DTS xChange can also profile your packages to determine what types of obejcts you're using to see how much of an effort there will be with the conversion and help you do a project plan.
The main reason that the task may not work is that you may be referencing the DTS object model in the script. After the conversion, you will want to review each of the ActiveX Script Tasks in SSIS to ensure that you're not using the old DTS object model. ActiveX Script tasks that try to access the package object model by using the Parent property of the GlobalVariables collection will fail after package migration. The former functionality must be replaced by using different script code or new SQL Server 2005 features.The code snippet to look for will look like the following:
DTSGlobalVariables("StartDate").value = "NewValue"
This would read the DTS global variable called StartDate. You would want to convert this same functionality to a Script Task or an Expression on the variable. To do a Script Task in SSIS, simply use the following this code snippet to replace the functionality:
DTS.Variables("StartDate").value = "NewValue"
One of the most common objects that will work in SSIS is the FileSystemObject (shown below). This object will reference the Windows file system for copying files, renaming files or reading properties of the files among other things. The following snippet will show you how to look for in the ActiveX Script Task to indiicate that you are using this function:
CreateObject("Scripting.FileSystemObject")
This functionality can easily be replaced by using a File System Task typically or a ForEach Loop Container with a ForEach File Enumerator. After replacing the code with one of those two tasks, the ActiveX script can be removed potentially.
The last common one that we see often is when a developer references a data connection in the ActiveX Script task as shown below:
CreateObject("ADODB.Connection")
This is done to typically run a query that can be done easily with an Execute SQL Task. Doing this inside an ActiveX Script Task is never a best practice and should be removed and replaced with either a Data Flow or Execute SQL Task.