In this post i descibe a real world example which i encountered during building SSIS packages. Initially, i built a stored procedure "uspGetRowCount" that calculates and returned the number of record in a table. This is calculated first, a data transfer takes place and then the records in table is calculated again. This works great when all of your databases resides on the same server but in most of the real world cases, databases will not placed on the same server. So i decided to replace the stored procedure with a simple SQL statement: SELECT COUNT(*) FROM ? and replace ? with a parameter. But this gives an error:
[Execute SQL Task] Error: Executing the query "SELECT COUNT(*) FROM ?" failed
with the following error: "Must declare the table variable "@P1".". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
So best practice in such cases is to create a variable. In my case: varSQLGetRowcount and give it the following value : "SELECT COUNT(*) FROM " + @[User::varDestinationObjectName]. Assign this variable to the Execute SQL task like this :
A small tip about SSIS but hopefully helpful.
Gr,
Hennie
Geen opmerkingen:
Een reactie posten