SSIS Package Not Writing Rows to OLE DB Destination Although Completion is Successful

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Hi,

    I have a package that simply sources from an OLE DB source to a OLE DB destination. The package runs successfully in the designer but does not copy any rows to the destination. Both connection managers correct. In fact I can preview the source data via the OLE DB source editor so I know it's correct. The Destination is local to the machine the package is being developed on and I am using Windows Authentication. Below is the message after the package runs.

    SSIS package "TestPackage.dtsx" starting.

    Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

    Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

    Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

    Information: 0x402090DF at Data Flow Task, OLE DB Destination [118]: The final commit for the data insertion has started.

    Information: 0x402090E0 at Data Flow Task, OLE DB Destination [118]: The final commit for the data insertion has ended.

    Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

    Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

    Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (118)" wrote 0 rows.

    SSIS package "TestPackage.dtsx" finished: Success.

    I am at a lost as I can't find anything on the MS blogs to point me in the right direction therefore any help is appreciated.

    Thanks,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    Try opening up your source and preview the data. Does the preview return any records?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Alvin Ramard (10/1/2010)


    Try opening up your source and preview the data. Does the preview return any records?

    Alvin thanks for your reply. Yes the preview works.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    If you're running this in BIDS, watch the data flow while the package is running. Does it show any records going through the data flow?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Alvin Ramard (10/1/2010)


    If you're running this in BIDS, watch the data flow while the package is running. Does it show any records going through the data flow?

    I just added a Grid data viewer and there is no data going through.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    Preview shows records in the data source but viewer shows no data coming out of the data source? hmmm........



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Alvin Ramard (10/1/2010)


    Preview shows records in the data source but viewer shows no data coming out of the data source? hmmm........

    That is the same thought I had...it is strange.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    Is there anything that changes the properties of the data source at run time? Check the expressions. It's possible the source gets changed at run time.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Alvin Ramard (10/1/2010)


    Is there anything that changes the properties of the data source at run time? Check the expressions. It's possible the source gets changed at run time.

    There are no package configurations on this package. The package contains just the single Data Flow Task with the OLE DB Source and Destination. The credentials for both source and destination are in the connection manager.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    MostInterestingMan (10/1/2010)


    Alvin Ramard (10/1/2010)


    Is there anything that changes the properties of the data source at run time? Check the expressions. It's possible the source gets changed at run time.

    There are no package configurations on this package. The package contains just the single Data Flow Task with the OLE DB Source and Destination. The credentials for both source and destination are in the connection manager.

    Alvin, thanks for the quick response on this but I found the problem. I am using "data access mode" of "SQL command" and I have the "USE DATABASE" statement in the query. Removing that solved the problem.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    great and you're welcome



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • brian.willis

    Old Hand

    Points: 381

    Hi there, I know this thread is a little aged, but I am having the same issue in that I have a SQL Agent job that has five steps, each step being a call to a server-stored SSIS package to execute. The entire job (all five steps) executed successfully, but steps 2-4 did not write any data to the destination tables. The connection mamagers for steps 2-4 are the exact same as step 1, but that first step was successful in transferring data across.

    I've tested and re-tested my connections, and I know there is data at the source. Just not sure why the job would execute and not pull back anything.

    Any thoughts? Novice-level SSIS skills here...

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    brian.willis (1/13/2011)


    Hi there, I know this thread is a little aged, but I am having the same issue in that I have a SQL Agent job that has five steps, each step being a call to a server-stored SSIS package to execute. The entire job (all five steps) executed successfully, but steps 2-4 did not write any data to the destination tables. The connection mamagers for steps 2-4 are the exact same as step 1, but that first step was successful in transferring data across.

    I've tested and re-tested my connections, and I know there is data at the source. Just not sure why the job would execute and not pull back anything.

    Any thoughts? Novice-level SSIS skills here...

    Make sure the sql statements in the other packages don't have the USE DATABASE statement.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    brian.willis (1/13/2011)


    Hi there, I know this thread is a little aged, but I am having the same issue in that I have a SQL Agent job that has five steps, each step being a call to a server-stored SSIS package to execute. The entire job (all five steps) executed successfully, but steps 2-4 did not write any data to the destination tables. The connection mamagers for steps 2-4 are the exact same as step 1, but that first step was successful in transferring data across.

    I've tested and re-tested my connections, and I know there is data at the source. Just not sure why the job would execute and not pull back anything.

    Any thoughts? Novice-level SSIS skills here...

    Is it an option to run those packages locally in BIDS to see what happens?

    (but check MostInterestingMan's - what's in a name 🙂 - suggestion first)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • brian.willis

    Old Hand

    Points: 381

    Thanks for the replies. USE DATABASE is not a part of my SQl command...it's a very simple SELECT that has a GROUP BY. Would USE DATABASE be something the package would append automatically behind the scenes, by chance, or is it only something that the user explicitly codes?

    When executing the sequence container(s) individually in BIDS I get mixed results. Day before yesterday all ran fine and added rows as expected. Yesterday I tweaked the GROUP BY and the container in question failed with:

    "The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020."

    I thought this error occurred because cached memory had run out of space, but the change to the GROUP BY was actually one that should return less rows, so I was confused and reverted back to the original groupings. When executed it failed again with the same error, and no matter what I tried I could not get this container to execute without failure again. My experience has been that the BIDS environment can sometimes be a bit buggy, so I completely deleted this container and started from scratch. It executed successfully (go figure...or my lack of knowledge is getting the best of me). Data Flow view showed rows written to tables from the source connection. It was at this point that I placed all related packages for this ETL into an Agent job and then executed successfully, but no rows were written to tables in steps 2-4 of the job (the container I was testing above is set as step #2 of the job).

    Step 1 in the job (the one that actually writes to the destination tables) runs a package that uses 'table or view - fast load' option (no SQL command), and again with the same connection managers.

Viewing 15 posts - 1 through 15 (of 26 total)

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