October 1, 2010 at 9:56 am
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
October 1, 2010 at 10:06 am
Try opening up your source and preview the data. Does the preview return any records?
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]
October 1, 2010 at 10:09 am
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
October 1, 2010 at 10:11 am
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?
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]
October 1, 2010 at 10:16 am
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
October 1, 2010 at 10:19 am
Preview shows records in the data source but viewer shows no data coming out of the data source? hmmm........
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]
October 1, 2010 at 10:23 am
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
October 1, 2010 at 10:27 am
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.
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]
October 1, 2010 at 10:41 am
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
October 1, 2010 at 11:00 am
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
October 1, 2010 at 11:44 am
great and you're welcome
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]
January 13, 2011 at 3:02 pm
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...
January 13, 2011 at 6:35 pm
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
January 14, 2011 at 12:51 am
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
January 14, 2011 at 7:05 am
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 25 total)
You must be logged in to reply to this topic. Login to reply