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

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  September 26, 2018 at 5:49 am

  #2007057

  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)?

  ---

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  September 26, 2018 at 6:04 am

  #2007060

  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: 247159

  More actions

  September 26, 2018 at 6:37 am

  #2007063

  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.

  ---