June 20, 2012 at 9:40 pm
I have been trying this all day but don’t seem to get to work. What I am trying to do is to capture the number of rows deleted/inserted/updated by a stored procedure. I have two proc to do these. I captured @@rowcount values to an integer output parameter in store procedure. I then call this sp from execute sql task and map the parameter to a package variable as output like this EXEC ? = dbo. procname ? OUTPUT, this is right? or is it exec dbo.procname ?output.What difference is between return code and output parameter? After that I need to insert these variable values and current timestamp. How in the world do I insert SSIS variable values into a table? Any suggestions will be greatly appreciated.
June 21, 2012 at 8:09 am
To get the output of a stored procedure for a single value you can use a select for example
CREATE PROCEDURE Test AS
BEGIN
SELECT 10
END
Then have your execute SQL task within package and call the stored procedure and store the result in an SSIS Variable.
You can then write the value of the variable into another table using agaijn an execute SQL task and using the input parameters.
The Execute SQL task would have something like
DECLARE @result int
SET @result = ?
INSERT INTO X ()
VALUES (@result)
MCITP SQL 2005, MCSA SQL 2012
June 21, 2012 at 8:47 am
If you want to receive an output value from a store procedure in SSIS package, you need to select
1- Create a SSIS variable such as MyVariable
2- Drag and drop Execute SQL task
3- Call your store procedure in SQL Statement option
EXEC Demo ? Output
CREATE PROCEDURE Demo
@ReturnValue int
As
BEGIN
SET @ReturnValue = 10
END
4- In Result section, select Single Row
5- In Parameter Mapping, set SSIS variable MyVariable = @ReturnValue and Direction = Output
6- Use the SSIS variable to insert base on your expectation
June 21, 2012 at 1:06 pm
Dung Dinh (6/21/2012)
If you want to receive an output value from a store procedure in SSIS package, you need to select1- Create a SSIS variable such as MyVariable
2- Drag and drop Execute SQL task
3- Call your store procedure in SQL Statement option
EXEC Demo ? Output
CREATE PROCEDURE Demo
@ReturnValue int
As
BEGIN
SET @ReturnValue = 10
END
4- In Result section, select Single Row
5- In Parameter Mapping, set SSIS variable MyVariable = @ReturnValue and Direction = Output
6- Use the SSIS variable to insert base on your expectation
Actully parameter mapping was not the biggest trouble here. Inserting a ssis variable value into a table is not straight forward as it appears. I tried the above method b by declaring a variable within the execute sql task as declare @result int and setting it as set @result=? And inserting that variable into the table. Keeps telling me ,invalid parameter number, result set blab la…….does n e one know a working procedure to insert SSIS variable value into a table?
June 21, 2012 at 1:40 pm
I assume that you know how to retrieve value for SSIS variable from a store procedure
1- Create new a store procedure to insert SSIS variable into a table
CREATE PROCEDURE InsertValueFromSSISVariable
@SSISVariable int
AS
BEGIN
INSERT INTO TableA(Value)
SELECT @SSISVariable
END
2- Drag and drop a SQL Execute SQL task
3- Call store procedure in SQL Statement
EXEC InsertValueFromSSISVariable @SSISVariable = ?
In Parameter Mapping, set SSIS variable as a parameter of store procedure
MyVariable = SSISVariable and set Direction = Input and Data Type = int
June 21, 2012 at 4:28 pm
Dung Dinh (6/21/2012)
I assume that you know how to retrieve value for SSIS variable from a store procedure1- Create new a store procedure to insert SSIS variable into a table
CREATE PROCEDURE InsertValueFromSSISVariable
@SSISVariable int
AS
BEGIN
INSERT INTO TableA(Value)
SELECT @SSISVariable
END
2- Drag and drop a SQL Execute SQL task
3- Call store procedure in SQL Statement
EXEC InsertValueFromSSISVariable @SSISVariable = ?
In Parameter Mapping, set SSIS variable as a parameter of store procedure
MyVariable = SSISVariable and set Direction = Input and Data Type = int
Dung, I think we are misunderstanding each other. Here are my procedural flow.
1. Stored procedure performs delete operation.
2. Capture the number of rows deleted @@rowcount and return it as Output Parameter in SP.
3. Call the SP in SSIS through Execute SQL Task (Exec dbo.myproc ? output)
4. Map the parameter into a SSIS variable in Parameter Mapping
Variable Name: User::deletedrows
Direction:OutPut
DataType: Long
ParameterName:0
Parametersize;-1
5. This should load whatever was returned from StoredProcedure to SSIS variable (deletedrows),correct?
6. Now I want to insert the number of rows into a table, but how?
The reason I am passing into ssis variable and not doing from SP is because I also have a datetime column that needs to consume datetime when the package is done running.
Insert into tableA(effectivedate,RowsDeleted) values (getdate(),_____????) –this is where I need help with?
June 21, 2012 at 9:25 pm
Hi gravitysucks
Are you sure you are using a Package level variable to collect the value returned by your stored procedure instead of a task level variable?
If you are using a task level variable, it will be out of scope on the next task, that could be your problem.
Cheers,
Hope this helps,
Rock from VbCity
June 22, 2012 at 1:21 am
Rock from VbCity (6/21/2012)
Hi gravitysucksAre you sure you are using a Package level variable to collect the value returned by your stored procedure instead of a task level variable?
If you are using a task level variable, it will be out of scope on the next task, that could be your problem.
Cheers,
its package scoped variable.
what is the syntax/method to insert a ssis variable value into a table through execute sql task?
June 22, 2012 at 7:06 am
Hi Gravitysucks,
As Rtaylor made it clear the syntax is pretty straightforward.
e.g. have a look on below dummy example
INSERT INTO RunInfo
(Col_1, Col_2)
VALUES (?, ?);
In SQL statement property of Exec SQL Task put above command.Where Col_1 & col_2 mapped with SSIS variables.
In short use ? placeholder is used for these 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply