|
|
You're in the
sql server dts
group:Executing a DTS Form Store Proc & Changing the connection
sql server dts:
The below SP (thanks to Database Journal (but simplified)) executes my DTS.
However, I need to modify one of the connections (XLS file path) before execution. Thus, I would need to add another parameter @Path for example. But how do I change the connection information within the Stored Procedure. In otherwords how do I the same thing as this code: oPkg.Connections.Item(2).DataSource = strPath Here is the sp: CREATE PROCEDURE dbo.spExecuteDTSPKG @Server varchar(255), @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 = 1, -- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = '' -- Package Password AS SET NOCOUNT ON DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) Select @ret = 0 -- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")' EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL -- Execute Pkg EXEC @hr = sp_OAMethod @oPKG, 'Execute' -- Unitialize the Pkg EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' -- Clean Up EXEC @hr = sp_OADestroy @oPKG RETURN @ret GO --
You will have to use sp_OASetProperty on the Connection object and the
property you want to set is the DataSource Property -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "WhiskRomeo" <wrlucasD0N0TSPAM@Xemaps.com> wrote in message news:8C896A13-6DE3-4F47-8BA9-3CEF0A74D6E4@microsoft.com... > The below SP (thanks to Database Journal (but simplified)) executes my DTS. > > However, I need to modify one of the connections (XLS file path) before execution. > Thus, I would need to add another parameter @Path for example. > > But how do I change the connection information within the Stored Procedure. In otherwords how do I the same thing as this code: > > oPkg.Connections.Item(2).DataSource = strPath > > Here is the sp: > > CREATE PROCEDURE dbo.spExecuteDTSPKG > @Server varchar(255), > @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 = 1, -- 0 = SQL Server Security, 1 = Integrated Security > @PkgPWD varchar(255) = '' -- Package Password > AS > SET NOCOUNT ON > > DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) > Select @ret = 0 > > -- Create a Pkg Object > EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT > > SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")' > > EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL > > -- Execute Pkg > EXEC @hr = sp_OAMethod @oPKG, 'Execute' > > -- Unitialize the Pkg > EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' > > -- Clean Up > EXEC @hr = sp_OADestroy @oPKG > > RETURN @ret > GO > > -- > William R
I have added a parameter to the sp for the path and the following statement following sp_OACreate.
EXEC @hr = sp_OASetProperty @oPKG, "Connections.Item(2).DataSource", @XLSPath The new sp executes just fine but it pulls from the hardcoded datasource instead of the new path. The path is a string like "D:\PPS\GradFoto\GF1-062404.xls" where d is a drive on the server. -- William R [quoted text, click to view] "Allan Mitchell" wrote:
> You will have to use sp_OASetProperty on the Connection object and the > property you want to set is the DataSource Property > > -- > -- > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > > "WhiskRomeo" <wrlucasD0N0TSPAM@Xemaps.com> wrote in message > news:8C896A13-6DE3-4F47-8BA9-3CEF0A74D6E4@microsoft.com... > > The below SP (thanks to Database Journal (but simplified)) executes my > DTS. > > > > However, I need to modify one of the connections (XLS file path) before > execution. > > Thus, I would need to add another parameter @Path for example. > > > > But how do I change the connection information within the Stored > Procedure. In otherwords how do I the same thing as this code: > > > > oPkg.Connections.Item(2).DataSource = strPath > > > > Here is the sp: > > > > CREATE PROCEDURE dbo.spExecuteDTSPKG > > @Server varchar(255), > > @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 = 1, -- 0 = SQL Server Security, 1 = Integrated Security > > @PkgPWD varchar(255) = '' -- Package Password > > AS > > SET NOCOUNT ON > > > > DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) > > Select @ret = 0 > > > > -- Create a Pkg Object > > EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT > > > > SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD > + '", , , "' + @PkgName + '")' > > > > EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL > > > > -- Execute Pkg > > EXEC @hr = sp_OAMethod @oPKG, 'Execute' > > > > -- Unitialize the Pkg > > EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' > > > > -- Clean Up > > EXEC @hr = sp_OADestroy @oPKG > > > > RETURN @ret > > GO > > > > -- > > William R > >
Actually, the connection object should be in sinqle quotes.
The error I get back is the index of 2 (base 1) is out of range. But this is the connection I want. When I specify by name instead of index, the error is: Connection 'PPSInputBatch' was not found -- William R [quoted text, click to view] "Allan Mitchell" wrote:
> You will have to use sp_OASetProperty on the Connection object and the > property you want to set is the DataSource Property > > -- > -- > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > > "WhiskRomeo" <wrlucasD0N0TSPAM@Xemaps.com> wrote in message > news:8C896A13-6DE3-4F47-8BA9-3CEF0A74D6E4@microsoft.com... > > The below SP (thanks to Database Journal (but simplified)) executes my > DTS. > > > > However, I need to modify one of the connections (XLS file path) before > execution. > > Thus, I would need to add another parameter @Path for example. > > > > But how do I change the connection information within the Stored > Procedure. In otherwords how do I the same thing as this code: > > > > oPkg.Connections.Item(2).DataSource = strPath > > > > Here is the sp: > > > > CREATE PROCEDURE dbo.spExecuteDTSPKG > > @Server varchar(255), > > @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 = 1, -- 0 = SQL Server Security, 1 = Integrated Security > > @PkgPWD varchar(255) = '' -- Package Password > > AS > > SET NOCOUNT ON > > > > DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) > > Select @ret = 0 > > > > -- Create a Pkg Object > > EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT > > > > SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD > + '", , , "' + @PkgName + '")' > > > > EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL > > > > -- Execute Pkg > > EXEC @hr = sp_OAMethod @oPKG, 'Execute' > > > > -- Unitialize the Pkg > > EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' > > > > -- Clean Up > > EXEC @hr = sp_OADestroy @oPKG > > > > RETURN @ret > > GO > > > > -- > > William R > >
Found the problem.
I have to load the package before I can access the conneciton property (duh)! I was trying the set the connection right after the create. The below sp works CREATE PROCEDURE dbo.spExecuteDTSPKG @Server varchar(255), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @XLSPath varchar(255), -- Path to Source File @ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME()) @IntSecurity bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = '' -- Package Password AS SET NOCOUNT ON DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000) Select @ret = 0 -- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")' EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL EXEC @hr = sp_OASetProperty @oPKG, 'Connections.Item("PPSBatchInput").DataSource', @XLSPath If @hr <> 0 BEGIN PRINT '*** OASetProperty for Connection Failed***' EXEC sp_OAGetErrorInfo @oPkg RETURN END -- Execute Pkg EXEC @hr = sp_OAMethod @oPKG, 'Execute' -- Unitialize the Pkg EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' -- Clean Up EXEC @hr = sp_OADestroy @oPKG RETURN @ret GO -- William R
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |