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.
User contributions licensed under CC BY-SA 3.0