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

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 (AT) Xemaps (DOT) com> wrote

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


"Allan Mitchell" wrote:

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


"Allan Mitchell" wrote:

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