Link to home
Start Free TrialLog in
Avatar of mjvsk

asked on

Recommended way to have SSIS package process dynamic SQL statement

I'm attempting to create a SSIS package that selects a column containing a list of source tables from one database table and then using the table name in a dynamic SQL statement that performs a select count(*) from table_name.

I have a For Each container selects the tablename column from the database table into a variable--> User::TableName

I then have 1 ExecuteSQL task in which I am attempting to dynamically create a SQL statement that has the value of the User::TableName variable.

The dynamically generated statement I am interested in getting the ExecuteSQLTask to create is:
select count(*) from User::TableName.

What is the best way to get the dyamic SQL to be generated?
Avatar of chapmandew
Flag of United States of America image

Are you just asking how to generate a dynamic SQL statement?

declare @tbl varchar(20), @sql varchar(2000)
set @tbl = 'mytablename'  --can also get this value from a table, etc
set @sql = 'select * from ' + @tbl
exec sp_executesql @sql
Dear Friend,
1. In the control flow of bids, select SQL Task, goto proporties right panel and click on expressions.
2. In the Property expression editor, select in the combobox the property SqlStatmentSource, and write your dinamyc query as this "SELECT YourField  FROM " + @[User::TableName]
3. Unsure the varibale TableName is created! :-)

Pedro Perfeito
Avatar of mjvsk


Thanks PedorCGD.
The dynamic SQL was successfully generated.

If I wanted to have the dynamic SQL save the count(*) as an Output  variable similar to what is  below:
SELECT count(*) as record_count FROM dbo." + @[User::TableName], how would I preceed?

I have defined the record_count variable in the Parameter Mapping and ResultSet Tabs for the Execute SQL Task.  In the Parameter Mapping Tab I have Varibale_Name = User::record_count
Direction = Output
Data Type = Long
Parameter Name = record_count

I continue to get error messages similar to the one below:
[Execute SQL Task] Error: Executing the query "SELECT count(*)  as record_count FROM dbo.DLctbltfunstuff" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Avatar of PedroCGD
Flag of Portugal image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mjvsk


Your recommendations did the job!  I'll be building this out a bit more by taking the User::record_count variable value as well as other variables I haven't yet derined and will use an ExecuteSQL task to insert into an audit table
Ok Friend cool!! :-)
Use the audit transform inside your dataflow to get the system variables with start and end times, version, task names... or use derived column...
Cheers! Visit my blog and comment to hear some of your work too!