none
Insert Select and then Select in OLE DB source returns 0 rows RRS feed

  • Question

  • I am trying to write a result set to a flat file

    the query i am running from an OLE DB source SQL command

    contains a insert select into a staging table and then a select from the staging table.

    next i have a Flat File destination.

    i was expecting to get multiple row in the flat file but i got 0 rows.

    i understand that the insert select rus but the script does not continue to the next select which is supposed to return the result set.

    how can i fix this?

    thanks

    Monday, September 16, 2019 7:46 PM

All replies

  • Hi Yaakov2018,

    It should be a two step process:

    • OLE DB source SQL command should be just a SELECT ... statement.
    • Just before that you can use SSIS Execute SQL Task for the INSERT in the SSIS Control Flow.



    Monday, September 16, 2019 7:57 PM
  • OK

    and in the Execute SQL Task should i use an Object variable to save the result set ?

    Thanks!


    • Edited by Yaakov2018 Monday, September 16, 2019 8:22 PM
    Monday, September 16, 2019 8:20 PM
  • Hi Yaakov2018,

    It is not so clear what is your ETL process is supposed to do. In particular, it is not clear what is the real source of the data.

    I am assuming that it should be a two step process:

    1. SSIS Execute SQL Task for the INSERT into a staging table in the SSIS Control Flow.
    2. OLE DB Source SQL command should be just a SELECT ... statement, yo retrieve data from the staging table.

    Monday, September 16, 2019 8:27 PM
  • Thanks.

    works great!

    • Edited by Yaakov2018 Monday, September 16, 2019 8:33 PM
    Monday, September 16, 2019 8:30 PM
  • Hi Yaakov2018,

    I am glad that the proposed solution is working for you.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Monday, September 16, 2019 8:33 PM