Hi All, I'm trying to migrate a dts package to MS SSIS 2008, the package is called dynamically from a stored procedure, which passes all the variables needed by the package (i.e, servername, importfilename and variables need within the package). In the DTS package, the package properties are configured within an ActiveX script(Function Main() Dim package Dim importTextFile set package=DTSGlobalVariables.Parent set importTextFile=package.Connections("Connection1") importTextFile.DataSource=DTSGlobalVariables("Import_Filename") set importSQLServer=package.Connections("Connection2") importSQLServer.DataSource=DTSGlobalVariables("ServerName") importSQLServer.Catalog=DTSGlobalVariables("DatabaseName") Main = DTSTaskExecResult_Success End Function ) I have tried to recreate the package in SSIS, firstly, there's an error on the activeX task, 2ndly i am not sure how i will call this package from a stored procedure(this is the code i used when calling the DTS package from a stored procedure declare @objPackage int declare @rc int declare @ServerName varchar(128) declare @DatabaseName varchar(128) declare @FileName varchar(128) set nocount on select @ServerName = @@servername , @DatabaseName = db_name() select @Import_Enabled, @Import_Filename, @Import_Package_Name, @ServerName, @DatabaseName exec sp_OACreate 'DTS.Package', @objPackage output select 'obj', @objPackage exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @Import_Package_Name if @rc <> 0 BEGIN select 'Package Create' exec sp_displaypkgerrors EXEC @hr = sp_OAGetErrorInfo @objPackage, @source OUT, @description OUT select @source, @description set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ServerName").value', @ServerName if @rc <> 0 BEGIN select 'srvname' set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("DatabaseName").value', @DatabaseName if @rc <> 0 BEGIN select 'dbname' set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Import_Filename").value', @Import_Filename if @rc <> 0 BEGIN select 'Filename' set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Segment").value', @ID_Segment if @rc <> 0 BEGIN select 'ID_Seg' set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Channel").value', @ID_Channel if @rc <> 0 BEGIN select 'ID_C' set @output=@rc RETURN END exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Product").value', @ID_Product if @rc <> 0 BEGIN select 'ID_C' set @output=@rc RETURN END exec @rc = sp_OAMethod @objPackage, 'Execute' if @rc <> 0 BEGIN set @output=@rc RETURN END exec @rc = sp_OADestroy @objPackage if @rc <> 0 BEGIN set @output=@rc RETURN END set @output=0 ). Please help me set this package in SSIS 2008, i'm loosing my mind here.
You can use the DTS itself by intalling bc module... http://msdn.microsoft.com/en-us/library/bb500440.aspx