Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  DTS Step Failed in SQL Store Procedure

Author  Topic  emasik Starting Member 1 Post Posted - 2007-03-05 : 11:33:15 I try to execute DTS package through SQL procedure. First, I created and ran DTS in ‘Enterprise Manager’. Package executed successfully. When I call this DTS package in SQL Query Analyzer - only one step of DTS is executed (Step Create Table). Other step (Step Copy Data) is failed. Here is a massage from SQL Query Analyzer: ___________________________________________________________ Step Copy Data from csv1 to [Alex].[dbo].[csv1] Step (Copy Data from csv1 to [Alex].[dbo].[csv1] Step) Failed Step Create Table [Alex].[dbo].[csv1] Step (Create Table [Alex].[dbo].[csv1] Step) Succeeded Package had 1 failed step(s) ___________________________________________________________ If any one know what can be cause that, please advice. You help will be appreciated. THANKS emasik Below is procedure code: CREATE PROC sp_ExecutePKG @Server varchar(255), @User_Name Varchar(25), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME() @IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = '' -- Package Password AS SET NOCOUNT ON /* Return Values - 0 Successfull execution of Package - 1 OLE Error - 9 Failure of Package */ DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) -- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT IF @hr <> 0 BEGIN PRINT '*** Create Package object failed' EXEC sp_displayoaerrorinfo @oPKG, @hr RETURN 1 END -- Evaluate Security and Build LoadFromSQLServer Statement IF @IntSecurity = 0 SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @User_Name + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")' ELSE SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")' EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL IF @hr <> 0 BEGIN PRINT '*** LoadFromSQLServer failed' EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END -- Execute Pkg EXEC @hr = sp_OAMethod @oPKG, 'Execute' IF @hr <> 0 BEGIN PRINT '*** Execute failed' EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END -- Check Pkg Errors EXEC @ret=sp_DisplayPkgErrors @oPKG -- Unitialize the Pkg EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' IF @hr <> 0 BEGIN PRINT '*** UnInitialize failed' EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END -- Clean Up EXEC @hr = sp_OADestroy @oPKG IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END RETURN @ret GO