Calling DTS from C#

Hi all,

i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy.

I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work.

Can anyone tell me what i may be doing wrong.

heres the calling code from C#

if(f.Name.Substring(13,7).ToLower() =="product"){try{activity.Log("Starting Product DTS Package..."); DTS.Package2Class package =new DTS.Package2Class();object pVarPersistStgOfHost =null;package.LoadFromSQLServer("192.168.8.8","username","thepassword",Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,null,null,null,(string)ConfigurationSettings.AppSettings["productDTSPackage"],ref pVarPersistStgOfHost);package.GlobalVariables.Item(1).Value = f.FullName.ToString();package.Execute();package.UnInitialize();//force release of COM objectSystem.Runtime.InteropServices.Marshal.ReleaseComObject(package);package =null;}catch(Exception e){activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message);}

and the activex i tried to use for the transformations is:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("Yesmail Id") = DTSSource("product series")
DTSDestination("Customer CKM CustId") = DTSSource("product family")
DTSDestination("Product SKU") = DTSSource("transaction date")
DTSDestination("product model name") = DTSSource("serial number")
DTSDestination("serial number") = DTSSource("product model name")
DTSDestination("transaction date") = DTSSource("Product SKU")
DTSDestination("product family") = DTSSource("Customer CKM CustId")
DTSDestination("product series") = DTSSource("Yesmail Id")
DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable.
Main = DTSTransformStat_OK
End Function

the weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor?

BTW: i do have my assembly signed in C# for the COM wrapper.

thanks in advance,

mcm

I have answered here in the other thread the next step and it is not complicated and I have not found anybody who could not get it to run so run a search on this site for setting up the proxy account link. Hope this helps.

http://forums.asp.net/thread/1440879.aspx