Search code examples
variablesssisforeach-loop-container

Dynamically Passing Table Names - using SQL Command from Variable

I read this thread: SSIS : Dynamically passing Table names and this is exactly what I'm trying to achieve. I got to the point of creating the string variable.

However, in the OLEdb source editor, when I chose to use SQL Command from Variable, the preview gave the following error prompt:

Exception from HRESULT: 0xC0202009 Error at Data Flow Task [OLE DB Source [37]]: 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 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Must declare the table variable "@".".

Appreciate some help on what could be the issue here.

Below are the components that I have set up.

EXECUTE SQL TASK:

SELECT name from sys.tables WHERE name like'%$Company Information'Order by name Result Set: Variable Name = User::IndivTableName; Result Name = 0; Data Type=Object

For Each Loop Container:

Enumerator: Foreach ADO Enumerator

ADO Object source variable: User::IndivTableName

Enumeration mode: Rows in the first table

(Is this needed?) Variable mappings: Variable = User::IndivTable; Index=0; Data Type = Object

String Variable:

Variable Name: User::CompanyInformation

Expression: "select [name],[address],[address 2],[city],[phone no_],[fax no_], [VAT Registration no_],[registration no_],[business type] from " + "@[User::IndivTableName] "

Evaluated Value: select [name],[address],[address 2],[city],[phone no_],[fax no_], [VAT Registration no_],[registration no_],[business type] from @[User::IndivTableName]

Data Source Editor:

Data Access Mode: SQL command from variable

Variable Name: User::CompanyInformation

Variable Value: select [name],[address],[address 2],[city],[phone no_],[fax no_], [VAT Registration no_],[registration no_],[business type] from @[User::IndivTableName]

Solution

  • Variable mappings at ForEach ADO Enumerator are mandatory, your mappings are ok. Please change datatype of User::IndivTable variable to string.

    Change Variable 'User::CompanyInformation' expression to 

    "select [name],[address],[address 2],[city],[phone no_],[fax no_],  
[VAT Registration no_],[registration no_],[business type] from " + [User::IndivTable]

    Variable User::IndivTableName contains DataTable, so it cannot be used in expression. You iterate over it and store the first column into User::IndivTable variable.