Passing table_name as a variable into an oledb source

Question

Passing table_name as a variable into an oledb source

david_h_edmonds

Default port

Points: 1430
More actions
September 26, 2018 at 5:45 am

#364214

Hi, I am using BIDS on SQL Server 2008 R2
and I have a sql script that loops through tables returned in a previous query and then executes this SQL from a variable
"select * from database.dbo." + @[User::Table_Name] + " where convert(date,invoiced_date_key) = convert(date,getdate()-1)"
If I execute this as an "execute Sql task" on the control flow it works fine but I need to output the results to a flatfile for handoff to another process.
I therefore tried to call the variable in an oledb command setting the data access mode to "SQL command from variable" but it errors on the SQL as the variable @[User::Table_Name] is empty until runtime.
I have delayed validation and set validate external metatdata to false everywhere I can think of but it is still giving incorrect sql errors due to the missing table name.
Can anyone help on this please?
Regards
Dave

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

david_h_edmonds - Wednesday, September 26, 2018 5:45 AM
Hi, I am using BIDS on SQL Server 2008 R2
and I have a sql script that loops through tables returned in a previous query and then executes this SQL from a variable
"select * from database.dbo." + @[User::Table_Name] + " where convert(date,invoiced_date_key) = convert(date,getdate()-1)"
If I execute this as an "execute Sql task" on the control flow it works fine but I need to output the results to a flatfile for handoff to another process.
I therefore tried to call the variable in an oledb command setting the data access mode to "SQL command from variable" but it errors on the SQL as the variable @[User::Table_Name] is empty until runtime.
I have delayed validation and set validate external metatdata to false everywhere I can think of but it is still giving incorrect sql errors due to the missing table name.
Can anyone help on this please?
Regards
Dave

Can you set a (valid) default value for the variable (which will be overwritten at runtime)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

david_h_edmonds Default port Points: 1430 More actions · Answer 2

Hi Phil, worked great thanks. Only issue now is that the select * for each table will return a variable number of columns. I know from past experience that SSIS hates flat files with variable columns.
Have you ever managed to get around this?

Dave

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

david_h_edmonds - Wednesday, September 26, 2018 6:04 AM
Hi Phil, worked great thanks. Only issue now is that the select * for each table will return a variable number of columns. I know from past experience that SSIS hates flat files with variable columns.
Have you ever managed to get around this?
Dave

Nothing simple in SSIS.

A slightly complex way would be to use a Script Component source to generate the output as a single column (regardless of the number of input columns), for whichever table is specified.

The script component would have to add in the delimiters & handle datatypes to force everything out as a long, single-column text string. Not pretty.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel