EXEC SQL TASK to FLAT FILE - questions

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

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

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

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

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

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.