Click here to Skip to main content
15,937,012 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SSIS For Each Loop is not accepting variable from SQL Query Task.
The SQL Query select table names, which should be looped through and each passed to a Data flow task to select * from TableName and write data to a TableName.txt file

Errors:
1. User::obj" does not contain a valid data object

When Using String Variable as Result Name for Tables query

2. Illegal characters in path
Using String Variable

What I have tried:

I have the SSIS package with
1. SQL Query Task (Get TableNames to ObjectVariable)
2. For Each Loop (ADO Enumerator ObjectVariable , mapped to string Variable TableName)
3. Data Flow task(Ole DB Source & FlatFile Destination)
3.a.Ole DB Source = Select * from TableName
3.b. Write data to TableName.txt
Posted
Comments
Homero Rivera 14-May-16 12:49pm    
Is your SQL Query Task actually outputing the variable, so it becomes available for the SSIS Variables?
SSIS can't check what the value in SQL Queery Task is... The SQL Query Task needs to output a value to Variables in SSIS Package for Loop to check the value.
wzkmafi 15-May-16 19:54pm    
Hi Homero, I have a GetTable Name variable in Resultset. the same variable is the For EachLoops ADO Source Variable.
In debug i see _COmObject when result set = Full result set. But, i only see one table name when i use single row resultset. The dataflow fails with variable not a valid object.
Although there is no failure with the dataflow using Full result set, but there is no output either...
Here is the setup's visual
http://www.sqlservercentral.com/Forums/Attachment18925.aspx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900