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.

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.

excel vba to generate flat file definition

What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.

I don't think this is possible. VBA is a COM based environment whereas the SSIS API is dotnet.

I stand to be corrected. If there is a way of calling dotnet APIs from VBA then I don't know about it.

-Jamie

|||As Jamie notes, our documentation is aimed at using the SSIS API through managed code. Using the native API is unsupported. You might be able to work backwards from the managed examples, though, if you're feeling adventurous. I'd suggest you start here: http://msdn2.microsoft.com/en-us/library/ms345167.aspx