Need help!! - output from stored procedure to flat file

[katbear]

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do??

Thanks

 

[paulkeys]

Are you setting the command text to be equal to the stored procedure name, or a string with 'exec ' + spName?

 

[katbear]

I am not setting the command text to anything.

I think the problem has to do with the fact that the DATA FLOW task cannot handle what I am asking it do, which is take a variable with multiple values (stored procedure names) and output each one to a flat file... even using a Parent Package Configuration to pass down the variable to a child package does not work (which is the proposed workaround to the "command text" problem).

It just isn't designed to do this... so I am having to use a scripted task instead.