Post reply

Using different queries in SSIS Foreach Loop container

  • October 12, 2009 at 9:51 am

    #156306

    Hello,

    1. I have lot of different tables data to be extracted to flat files like table1, table2, table3.....tableXXX.

    2. I need to run select * from each table and capturing the data in flat files by naming the file as table name.

    I am trying to use foreach loop in the case but getting error...I captured table names (table1, table2 etc.,) and Query (select * from table1, select * from table2 etc., ) to a temp table and then using the variables tablename and query as variables in the foreach loop container and ending with error.

    Error at Getting Data to Flatfiles [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

    So my question is ...I heard some where that each table contains different columns and the each query leads to different columns and hence the columns are changing flatfile extraction is not possible. Is that correct?

    Can you kindly suggest some method to my scenario?

    thanks in advance.

    Krishna.

  • October 13, 2009 at 8:36 am

    #1065221

    vvkp (10/12/2009)

    Hello,

    1. I have lot of different tables data to be extracted to flat files like table1, table2, table3.....tableXXX.

    2. I need to run select * from each table and capturing the data in flat files by naming the file as table name.

    I am trying to use foreach loop in the case but getting error...I captured table names (table1, table2 etc.,) and Query (select * from table1, select * from table2 etc., ) to a temp table and then using the variables tablename and query as variables in the foreach loop container and ending with error.

    Error at Getting Data to Flatfiles [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

    So my question is ...I heard some where that each table contains different columns and the each query leads to different columns and hence the columns are changing flatfile extraction is not possible. Is that correct?

    Can you kindly suggest some method to my scenario?

    thanks in advance.

    Krishna.

    Yes, this is correct. The standard data flow is static in nature and if you want to make your scenario work you have to create SSIS packages in code.

    If you can use third-party component, check the commercial CozyRoc Data Flow Task Plus. It does include support for dynamic data flows where you can specify a data source and destination to be dynamic. The enhanced Data Flow Task Plus will then construct dynamically the additional columns at runtime.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply