How to Get/Access Stored Procedure OUTPUT Parameters in SSIS
I often use/access stored procedure output parameter using c# etc. But this time I have an requirement in which I need to access newly inserted record id(stored procedure output parameter) using SSIS and capture this output parameter in an SSIS variable. Before that I have never access output parameter using SSIS. I want to access newly inserted record id and store it in SSIS variable.
First Create a table:
Example script: In this example I am creating country table.
After that set the Parameter section, which is use to hold the output Parameter value in SSIS Variable.
Check your table also:
First Create a table:
Example script: In this example I am creating country table.
CREATE TABLE [dbo].[Country](
[CountryID]
[int] NOT NULL,
[CountryName]
[varchar](80) NOT NULL
) ON [PRIMARY]
Now Create a procedure with an Output parameter, which will return the newly inserted record id, In this example it will return the countryID
Now Create an SSIS Package, and create a variable
Now take "Execute SQL Task" (set connection property)
and In SQL Statement section use the following statement
After that set the Parameter section, which is use to hold the output Parameter value in SSIS Variable.
How to use this variable in Insert statement or display it in a Messagebox?
Till now we have done every thing to access varialbe, But how to show or confirm that we have done out task,
For that you can display this SSIS variable in a MessageBox.
To display this variable value in messagebox you need to take a "Script task" and edit it as shown.
Now execute your package. You will see the result in Messagebox.
Check your table also:
Comments