February 10, 2016 at 2:27 pm
I have a very simple stored procedure (getdata) with a parameter (@Id) which return a dataset from a table:
select * from table where Id=@id
I have to use this stored procedure as datasource in a data flow task
So I created a variable Id (I also set the value=2), and I chose the data source SQL Command as Data Access Mode.
In the Sql Command Text I wrote: exec getdata ?
In Parameters... I mapped it in this way:
Parameters|Variables|Param Direction
0|User::Id|Input
But when I run the package I have the error:" The SQL command requires a parameter named "@id", which is not found in the parameter mapping"
I have to do something else in order to pass the parameter to the stored procedure?
I expected it was enough to have in input the result of the procedure getdata 2.....but it isn't....
Thanks.
February 10, 2016 at 2:52 pm
Change this
Parameters|Variables|Param Direction
0|User::Id|Input
to this
Parameters|Variables|Param Direction
@id|User::Id|Input
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 10, 2016 at 4:50 pm
Thank you, this functions properly.
I found the solution at this link too:
But is it some way to test how many records return the stored procedure?I need to transfer data only if the stored procedure return more than 1 record.
February 10, 2016 at 7:02 pm
antonela (2/10/2016)
Thank you, this functions properly.I found the solution at this link too:
But is it some way to test how many records return the stored procedure?I need to transfer data only if the stored procedure return more than 1 record.
I am not sure I understand what you mean. If the stored procedure returns 0 rows then by definition you won't be transferring any rows via the Data Flow.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 11, 2016 at 3:07 am
Yes,it's true.
But before to transfer the data I have to truncate the destination table.
And if the stored procedure doesn't return the data I don't want to do the truncate.
What is the most simple way to test the number of records returned from a stored procedure?
I should test the number the records before to run the container containing the truncate +data flow task that import data.
February 11, 2016 at 6:06 am
You should always be OK truncating a target staging table at any time. That is what staging tables are for.
It sounds like you should create a staging table on your target database and truncate it instead of the table you are currently truncating. Then, after you have run your data flow, if your proc returned 0 rows you can skip the step that would truncate the target table and load it from your staging table using an INSERT...SELECT.
As for how you can tell how many rows your stored procedure returned, use a Row Count Transformation in your Data Flow.
So, here is the rundown:
1. Execute Sql Task truncates staging table on target system.
2. Data Flow sources data from stored procedure, captured row count in a variable, and loads staging table on target system.
3. Precedence Constraint on line between Data Flow and another Execute Sql Task only allows execution if variable with row count is greater than 0.
4. If row count greater than 0, Execute Sql Task runs TRUNCATE TABLE to clear target table, then INSERT...SELECT to load target table from staging table. Both statements should be done in a transaction to ensure if the INSERT...SELECT fails that the TRUNCATE is rolled back and the target table retains its data while you troubleshoot.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply