SQL Server Performance Forum – Threads Archive
Error in DTS packages
–I am executing DTS packages from stored procedures <br />–Here is the Code<br />–***************<br />——————————————————————–<br />Declare <br />@Server varchar(255),<br />@PkgName varchar(255),<br />@ServerPWD varchar(255),<br />@IntSecurity bit ,<br />@PkgPWD varchar(255)<br /><br /><br />set @Server=’111.111.111.111′<br />set @PkgName=’New Package'<br />set @ServerPWD=’xxxxx'<br />set @IntSecurity=0<br />set @PkgPWD=”<br /><br />DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)<br /><br />– Create a Pkg Object<br />EXEC @hr = sp_OACreate ‘DTS.Package’, @oPKG OUTPUT <br />IF @hr <> 0<br />BEGIN<br />PRINT ‘*** Create Package object failed'<br />select 1,’*** Create Package object failed'<br />END<br /><br />– Evaluate Security and Build LoadFromSQLServer Statement<br />IF @IntSecurity = 0 <br />SET @Cmd = ‘LoadFromSQLServer("’ + @Server +’", "’ + SUSER_SNAME() + ‘", "’ + @ServerPWD + ‘", 0, "’ + @PkgPWD + ‘", , , "’ + @PkgName + ‘")'<br />ELSE<br />SET @Cmd = ‘LoadFromSQLServer("’ + @Server +’", "", "", 256, "’ + @PkgPWD + ‘", , , "’ + @PkgName + ‘")'<br /><br />–=====[<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]============<b>Here is problem</b>============[8D]=====<br />EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL<br /><br />IF @hr <> 0<br />BEGIN<br /> PRINT ‘*** LoadFromSQLServer failed'<br /> select 1,’*** LoadFromSQLServer failed'<br />END<br /><br />– Execute Pkg<br />EXEC @hr = sp_OAMethod @oPKG, ‘Execute'<br />IF @hr <> 0<br />BEGIN<br /> PRINT ‘*** Execute failed'<br /> select 1,’*** Execute failed'<br />END<br /><br />– Clean Up<br />EXEC @hr = sp_OADestroy @oPKG<br />IF @hr <> 0<br />BEGIN<br />select 1<br />END<br /><br />———————————————————————————-<br /><br />************************************************<br /><b>It gives Error: *** LoadFromSQLServer failed ***</b><br /><br />Where as if i execute from vb 6.0 it is working.<br />Here is vb 6.0 code<br />************************************************<br />Dim obj1 As New DTS.Package<br /><br />obj1.LoadFromSQLServer "111.111.111.111", "sa", "xxxxx", DTSSQLStgFlag_Default, , , , "New Package"<br />obj1.Execute<br />obj1.UnInitialize<br /><br />Set obj1 = Nothing <br />************************************************<br /><br /><br /><br />Rajeev Kumar Srivastava<br /> –ALWAYS BE POSITIVE!–
]]>