huge data loading using bulk insert in Script task RRS feed

  • Question

  • Hi all,

    I am facing an issue while working on the SSIS data load using script task.

    I am fetching data from linked server using OPEN_QUERY syntax and inserting data into my staging table. All using bulk insert feature in SSIS Script task. It is working fine for small tables(having 1-2  millions rows). But when the source row count is like 80-90 millions, it insert  around 10 millions in the destination table(I check it using rowcnt) then suddenly it start showing 0 rowcount and the package Script task continue running without fail and now no row is getting inserted.

    Is anyone having any idea about the what is causing this issue .

    Source is Informix connecting using Linked server in SQL server.




    • Edited by Avhilash Thursday, September 20, 2018 11:28 AM
    Thursday, September 20, 2018 11:27 AM

All replies

  • Probably it would be good to load data in batches as your task involves Linked server and data volume is huge.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 20, 2018 12:22 PM
  • Hi Avhilash, 

    To troubleshoot this issue, please post the script and execution result or log if possible. 

    I'm not sure whether linked server is a good choice in this scenario.

    You can also try OLEDB Connection with ODBC driver for Informix: 

    Relative links:

    How to Connect to Informix from an SSIS Package

    Setting up an ODBC data source for Informix on Windows

    Connecting to a database from Windows systems

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 21, 2018 8:28 AM
  • Hi ,

    Thanks for reply.

    What do you mean by batches and  how to implement here in script task. I am loading only one table at one time.

    Friday, September 21, 2018 8:29 AM
  • Hi ,

    Thanks for reply.

    What do you mean by batches and  how to implement here in script task. I am loading only one table at one time.

    what suggestion meant was in case of tables with large data divide the INSERT logic to be multiple batches by iterating based on a loop logic may be using a date based column or by using primary key column value

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, September 21, 2018 9:10 AM
  • My first guess would be Informix  is throttling your query.  I suggest you contact your Informix administrator to determine what is happening on Informix.

    Friday, September 21, 2018 11:53 AM