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