SQL Server Performance

Using different queries in SSIS foreach loop container

Discussion in 'SQL Server 2005 Integration Services' started by vvkp, Oct 12, 2009.

  1. vvkp New Member

    Hello friends,
    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 thenusing the variables tablename and query as variables in the foreachloop 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.
    AnOLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for thecommand object.".

    So my question is ...I heard some where thateach table contains different columns and the each query leads todifferent 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.
  2. satya Moderator

    Can you share the query used in this package?
    Where is session("strSQL") getting set?
    It looks like strSQL is not getting set correctly, see
    Set oRS = conn.Execute(strSQL)

Share This Page