2

I have an SSIS data flow task where I am trying to load data from a source table using ADO.NET Source Editor into a SQL Server table.

The problem:

The source table name is dynamic and depends on the current date.

Example: If I want to load today's data then the table name would be Sample_03292017_data and if loading tomorrow's data then it would be Sample_03302017_Data.

I did some research and found how to pass parameters to an ADO.NET Source Editor to use in where conditions but I couldn't find anything on how to use this parameter in a table name.

Does anyone know how I can achieve this? My query is really simple:

select * from Sample_[DateParameter]_Data.

I am using Visual Studio Data Tools 2010.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Bhoomika
  • 23
  • 7

2 Answers2

2

Expression must be set on the Data Flow Task not in the Ado.net Source

Follow this steps to set an expression for an Ado.net Source:

  1. in the control flow tab click on the Data Flow Task and press F4 to show the properties tab
  2. Click on the expression button, it show up a form like shown below

enter image description here

  1. choose [Ado.net Source].sqlcommand property and click on the expression builder button

enter image description here

  1. write the following expression "select * from Sample_ " + @[User::DateParameter] + "_Data" (assuming that your data parameter is stored in a variable named DateParameter)

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Yes, I followed all those steps and right now the query is coming up correctly in the source editor but it shows no columns and when I try to preview the data it gives me the below error; There was an error displaying the preview. ------------------------------ ADDITIONAL INFORMATION: ERROR [37000] [Rocket U2][U2ODBC][1401257]Error ID: 21 Severity: ERROR Facility: DBCAPERR - SQL Syntax error. (U2ODBC.DLL) – Bhoomika Mar 29 '17 at 21:37
  • 1
    Check your sql command syntax, test it on mysql workbench before using it. – Hadi Mar 30 '17 at 02:07
  • @Bhoomika if this answer solved your issue you have to [accept it](http://www.stackoverflow.com/tour) else give me a feedback – Hadi Apr 01 '17 at 18:05
2

You should use dynamic SQL to achieve this. If you are using ADO.net as Connection Type, copy and paste the following to your SQL statement:

Declare @SQL VARCHAR(MAX)
SET @SQL = 'Select * from ' + @TABLE
EXEC(@SQL)

In the parameter page, you need to Add new parameter which has @TABLE as the parameter name and leave the size as -1. The @Table variable should be decided by the expression from variable setting page.

LONG
  • 4,490
  • 2
  • 17
  • 35