ssis dynamic t-sql statement with use of parameter as table name

Question

I've downloaded Visual Studio 2015 and SQL Data Tools 2015, because I'm trying to make a simple SSIS package for copy some data rows from a table in a db on a server to a table in another db on another server.I have some complications that make my work harder, such as the need to dynamically build the table name.Until now I've create a Execute Sql Task for delete data in target table for avoid duplicate rows.At the output I've attached a Data Flow Activity consisting of an Ole DB Source and an Ole DB Destination.I must schedule the execution of this package for the future. At the moment I've created three package variables: "tablename" , "date" and "ats". I'm trying to passing these variables to a dynamic t-sql statement as parameters on the Execute Sql Task's editor and on the Ole DB Source's editor. Unfortunately I've got some building errors when put in the sql command editor a query like this:

"SELECT * FROM ? WHERE DATE_REF = ? AND ATS = ?"

If I use the table name instead of the first question mark I've no building error,so how can I use a variable or parameter as table name in this particular case of dynamic t-sql statament? I've tryed also with @[User::tablename] instead of the first question mark but I've got a new building error.

Thanks in advance for your help!

Answer 1

The way to do this is to create the query in another string variable, which has an expression of:

"SELECT * FROM " + @[User::tablename] + " WHERE DATE_REF = '" + @[User::date] + "' AND ATS = '" + @[User::ats] + "'"

(Note that I'm assuming your date is held in a string variable in a format that can be injected into an SQL statement - if it isn't, you would require further manipulation to get it into this new string variable.)

How to use this:

In an OLE DB Source component: Use the 'SQL command from variable' option in the 'Data access mode:' field, then select your new variable from the drop-down box below.

In an Execute SQL Task: Set the 'SQLSourceType' to 'Variable', then select your new variable from the drop-down box below ('SourceVariable').

"SELECT * FROM " + @[User::tablename] + " WHERE DATE_REF = '" + @[User::date] + "' AND ATS = '" + @[User::ats] + "'"

Note that for the Execute SQL Task you could also use a variable with question marks in the WHERE clause and then pass parameters in as you normally would.