September 12, 2011 at 9:07 am
I don't have much experience using DTexec, so I'm hoping more experienced SSIS developers can provide assistance.
I have a stored procedure that captures an input parameter (@ClientID), and then assigns that value to an SSIS package variable (_ClientID) when executing the package via DTexec.
See the code below:
CREATE PROC DBO.usp_ProcessClientData (@ClientID int)
AS
BEGIN
DECLARE
@PackagePath varchar(256)
,@Cmd varchar(4000)
SET NOCOUNT ON;
/* GET LOCATION OF THE SSISPACKAGE FROM THE EXTENDED PROPERTIES OF THE STORED PROC */
SELECT
@PackagePath = cast(value as varchar (256))
FROM
sys.extended_properties
WHERE
name = 'SSISPackagePath'
and major_id = OBJECT_ID('DBO.usp_ProcessClientData')
SELECT
@Cmd = 'DTexec /FILE "' + @PackagePath + 'ProcessClient.dtsx" /MAXCONCURRENT 1 /CHECKPOINTS OFF /REPORTING EW'
+' /SET \Package.Variables[User::_ClientID].Properties[Value];' + CAST(@ClientID As varchar(10));
EXEC xp_cmdshell
@Cmd;
END
I'd like to modify the stored proc to pass two input parameters to the SSIS package (@ClientID and @ProductLine).
Is the following code correct?
ALTER PROC DBO.usp_ProcessClientData (@ClientID int, @ProductLine int)
AS
BEGIN
DECLARE
@PackagePath varchar(256)
,@Cmd varchar(4000)
SET NOCOUNT ON;
/* GET LOCATION OF THE SSISPACKAGE FROM THE EXTENDED PROPERTIES OF THE STORED PROC */
SELECT
@PackagePath = cast(value as varchar (256))
FROM
sys.extended_properties
WHERE
name = 'SSISPackagePath'
and major_id = OBJECT_ID('DBO.usp_ProcessClientData')
SELECT
@Cmd = 'DTexec /FILE "' + @PackagePath + 'ProcessClient.dtsx" /MAXCONCURRENT 1 /CHECKPOINTS OFF /REPORTING EW'
+' /SET \Package.Variables[User::_ClientID].Properties[Value];' + CAST(@ClientID As varchar(10))
+' /SET \Package.Variables[User::_ProductLine].Properties[Value];' + CAST(@ProductLine As varchar(10));
EXEC xp_cmdshell
@Cmd;
END
September 13, 2011 at 1:19 pm
Hi,
One or two things that needs to change:
Your code should look like this: Note the change in the variable/value sets.
ALTER PROC DBO.usp_ProcessClientData (@ClientID int, @ProductLine int)
AS
BEGIN
DECLARE
@PackagePath varchar(256)
,@Cmd varchar(4000)
SET NOCOUNT ON;
/* GET LOCATION OF THE SSISPACKAGE FROM THE EXTENDED PROPERTIES OF THE STORED PROC */
SELECT
@PackagePath = cast(value as varchar (256))
FROM
sys.extended_properties
WHERE
name = 'SSISPackagePath'
and major_id = OBJECT_ID('DBO.usp_ProcessClientData')
SELECT
@Cmd = 'DTexec /FILE "' + @PackagePath + 'ProcessClient.dtsx" /MAXCONCURRENT 1 /CHECKPOINTS OFF /REPORTING EW'
+' /SET \Package.Variables[User::_ClientID].Value;' + CAST(@ClientID As varchar(10))
+' /SET \Package.Variables[User::_ProductLine].Value;' + CAST(@ProductLine As varchar(10));
EXEC xp_cmdshell
@Cmd;
END
Also, do have an underscore prefix in your variable names? Just making sure that is correct.
Lastly, if your variables in SSIS are of type string/text, you have to enclose the value in double quotes, like this:
ALTER PROC DBO.usp_ProcessClientData (@ClientID int, @ProductLine int)
AS
BEGIN
DECLARE
@PackagePath varchar(256)
,@Cmd varchar(4000)
SET NOCOUNT ON;
/* GET LOCATION OF THE SSISPACKAGE FROM THE EXTENDED PROPERTIES OF THE STORED PROC */
SELECT
@PackagePath = cast(value as varchar (256))
FROM
sys.extended_properties
WHERE
name = 'SSISPackagePath'
and major_id = OBJECT_ID('DBO.usp_ProcessClientData')
SELECT
@Cmd = 'DTexec /FILE "' + @PackagePath + 'ProcessClient.dtsx" /MAXCONCURRENT 1 /CHECKPOINTS OFF /REPORTING EW'
+' /SET \Package.Variables[User::_ClientID].Value;"' + CAST(@ClientID As varchar(10)) + '"'
+' /SET \Package.Variables[User::_ProductLine].Value;"' + CAST(@ProductLine As varchar(10)) + '"';
EXEC xp_cmdshell
@Cmd;
END
September 13, 2011 at 2:56 pm
Martin -- thanks for the reply. The finer points you highlighted in your code are duly noted. Awesome!
--Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy