Using Tablename as variable in ssis

2

I am trying to build a dynamic export using SSIS.

I set up a variable TableName-string and scoped to the package. I set up a sql task to find the table name I wanted and mapped the result set to above variable. (This task completes fine)

Then I have added another variable as SQL("SELECT * FROM [CRDATA].[dbo]."+@[User::TableName]) -string and scoped to the package and set up a dataflow task. Inside dataflow I add an OLEDB source and set up the input to SQL Variable and selected the above variable. This task fails. The Error is:

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0
 Hresult: 0x80040E14  Description: "Statement(s) could not be prepared..
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0  
Hresult: 0x80040E14  Description: "Incorrect syntax near '+'..
------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

I have set up delay validation to true on the sql task, dataflow and set the validatemetadata to false in the OLEDB Source property.

I have a feeling I made some mistake in the SQL Variable. Any help here would be very much appreciated.

sql-server
ssis
oledb
asked on Stack Overflow Oct 7, 2013 by Anbu Dhan • edited Jan 24, 2017 by Brian Tompsett - 汤莱恩

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0