| Groups | Blog | Home |
sql server dts : Don't know how to debug SSIS packages
I'm quite new to SSIS packages, so I don't know how to debug them very well. I have a Flat File Source, a Script Component, and an OLE DB Destination. When I run it, it looks like it is trying to import rows: "9,916 Rows" shows up on either side of the Script Componen. And then both the source and destination turn red and the thing stops. I switch to the progress tab to look up all of the error messages. These error messages are very cryptic. They won't tell me what row the error occured on or the data that caused the error. (Error messages are pasted at the end, after sig) On top of it all, it looks like Script Components do not have error redirection. Is there any way to decipher from the error messages what row the error occured on so I can look at the data and try to figure out why it is failing? (I definitely want to learn all I can about SSIS, but everything seems extremely complicated and cryptic. Doesn't seem like it is very easy trying to do it on your own w/o any books.) Thanks for any help anyone can provide, Conan Kelly [OLE DB Destination [555]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". [OLE DB Destination [555]] Error: There was an error with input column "oOpenDate" (647) on input "OLE DB Destination Input" (568). The column status returned was: "Conversion failed because the data value overflowed the specified type.". [OLE DB Destination [555]] Error: The "input "OLE DB Destination Input" (568)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (568)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (555) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029. [Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
Hello Coman Kelly:
Couple of things you can do: 1) Add Data Viewers to the data flow: Right CLick on the flow line between the Source adapter and the first transform, then select Data Viewer. 2) Have the Destination adapter error output go some place: Create a varialbe of type Int32, then in the data flow drag the RED arrow from the destination to a Row Count transform. Set the Row Count Transform to dump its row count to that variable. Set up the Destination Adapter to Redirect rows on error (cannot use "Fast Load" when doing redirection of errors). 3) Set up another Data Viewere between the Destination and that Error Row Count transform. From your description, it sounds like NONE of the rows get loaded in the destination. You may have issues with data types or mappings not set correctly. I strongly suggest you look at the Output Window after the package fails and find the FIRST few rows that start with "ERROR". Read them completely. Copy and paste them back here if you can. Script components are very powerful. Grab a copy of "The Rational Guide to Scripting SQ: Server 2005 Integration Services" by Donald Farmer (180 pages) ISBN 1-932577-21-1, about $20 from Amazon. I think I remember reading in there that you can script a row to be sent down the error path instead of the regular transform path. Hope this helps. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] [quoted text, click to view] "Conan Kelly" wrote:
> Hello all, > > I'm quite new to SSIS packages, so I don't know how to debug them very well. > > I have a Flat File Source, a Script Component, and an OLE DB Destination. > When I run it, it looks like it is trying to import rows: "9,916 Rows" > shows up on either side of the Script Componen. And then both the source > and destination turn red and the thing stops. I switch to the progress tab > to look up all of the error messages. These error messages are very > cryptic. They won't tell me what row the error occured on or the data that > caused the error. (Error messages are pasted at the end, after sig) > > On top of it all, it looks like Script Components do not have error > redirection. > > Is there any way to decipher from the error messages what row the error > occured on so I can look at the data and try to figure out why it is > failing? > > (I definitely want to learn all I can about SSIS, but everything seems > extremely complicated and cryptic. Doesn't seem like it is very easy trying > to do it on your own w/o any books.) > > Thanks for any help anyone can provide, > > Conan Kelly > > > > > > > > > [OLE DB Destination [555]] Error: An OLE DB error has occurred. Error code: > 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native > Client" Hresult: 0x80004005 Description: "Invalid character value for cast > specification". An OLE DB record is available. Source: "Microsoft SQL > Native Client" Hresult: 0x80004005 Description: "Invalid character value > for cast specification". > > > > [OLE DB Destination [555]] Error: There was an error with input column > "oOpenDate" (647) on input "OLE DB Destination Input" (568). The column > status returned was: "Conversion failed because the data value overflowed > the specified type.". > > > > [OLE DB Destination [555]] Error: The "input "OLE DB Destination Input" > (568)" failed because error code 0xC020907A occurred, and the error row > disposition on "input "OLE DB Destination Input" (568)" specifies failure on > error. An error occurred on the specified object of the specified component. > > > > [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB > Destination" (555) failed with error code 0xC0209029. The identified > component returned an error from the ProcessInput method. The error is > specific to the component, but the error is fatal and will cause the Data > Flow task to stop running. > > > > [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code > 0xC0209029. > > > > [Flat File Source [1]] Error: The attempt to add a row to the Data Flow task > buffer failed with error code 0xC0047020. > > > > [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" > (1) returned error code 0xC02020C4. The component returned a failure code > when the pipeline engine called PrimeOutput(). The meaning of the failure > code is defined by the component, but the error is fatal and the pipeline > stopped executing. > > > > [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code > 0xC0047038. > >
Todd,
Thank you for your feedback. I haven't read this post yet, but I will save it and read it when I get a chance. At first glance, it looks like it has some good information. I just hope that I can understand it and get it to work. Thanks again for all of your help, Conan [quoted text, click to view] "Todd C" <ToddC@discussions.microsoft.com> wrote in message news:2E373B65-CA8E-4068-8B2E-3E4B414C75F6@microsoft.com... > Hello Coman Kelly: > > Couple of things you can do: > 1) Add Data Viewers to the data flow: Right CLick on the flow line between > the Source adapter and the first transform, then select Data Viewer. > 2) Have the Destination adapter error output go some place: Create a > varialbe of type Int32, then in the data flow drag the RED arrow from the > destination to a Row Count transform. Set the Row Count Transform to dump > its > row count to that variable. Set up the Destination Adapter to Redirect > rows > on error (cannot use "Fast Load" when doing redirection of errors). > 3) Set up another Data Viewere between the Destination and that Error Row > Count transform. > > From your description, it sounds like NONE of the rows get loaded in the > destination. You may have issues with data types or mappings not set > correctly. > > I strongly suggest you look at the Output Window after the package fails > and > find the FIRST few rows that start with "ERROR". Read them completely. > Copy > and paste them back here if you can. > > Script components are very powerful. Grab a copy of "The Rational Guide to > Scripting SQ: Server 2005 Integration Services" by Donald Farmer (180 > pages) > ISBN 1-932577-21-1, about $20 from Amazon. I think I remember reading in > there that you can script a row to be sent down the error path instead of > the > regular transform path. > > Hope this helps. > -- > Todd C > > [If this response was helpful, please indicate by clicking the appropriate > answer at the bottom] > > > "Conan Kelly" wrote: > >> Hello all, >> >> I'm quite new to SSIS packages, so I don't know how to debug them very >> well. >> >> I have a Flat File Source, a Script Component, and an OLE DB Destination. >> When I run it, it looks like it is trying to import rows: "9,916 Rows" >> shows up on either side of the Script Componen. And then both the source >> and destination turn red and the thing stops. I switch to the progress >> tab >> to look up all of the error messages. These error messages are very >> cryptic. They won't tell me what row the error occured on or the data >> that >> caused the error. (Error messages are pasted at the end, after sig) >> >> On top of it all, it looks like Script Components do not have error >> redirection. >> >> Is there any way to decipher from the error messages what row the error >> occured on so I can look at the data and try to figure out why it is >> failing? >> >> (I definitely want to learn all I can about SSIS, but everything seems >> extremely complicated and cryptic. Doesn't seem like it is very easy >> trying >> to do it on your own w/o any books.) >> >> Thanks for any help anyone can provide, >> >> Conan Kelly >> >> >> >> >> >> >> >> >> [OLE DB Destination [555]] Error: An OLE DB error has occurred. Error >> code: >> 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native >> Client" Hresult: 0x80004005 Description: "Invalid character value for >> cast >> specification". An OLE DB record is available. Source: "Microsoft SQL >> Native Client" Hresult: 0x80004005 Description: "Invalid character >> value >> for cast specification". >> >> >> >> [OLE DB Destination [555]] Error: There was an error with input column >> "oOpenDate" (647) on input "OLE DB Destination Input" (568). The column >> status returned was: "Conversion failed because the data value overflowed >> the specified type.". >> >> >> >> [OLE DB Destination [555]] Error: The "input "OLE DB Destination Input" >> (568)" failed because error code 0xC020907A occurred, and the error row >> disposition on "input "OLE DB Destination Input" (568)" specifies failure >> on >> error. An error occurred on the specified object of the specified >> component. >> >> >> >> [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB >> Destination" (555) failed with error code 0xC0209029. The identified >> component returned an error from the ProcessInput method. The error is >> specific to the component, but the error is fatal and will cause the Data >> Flow task to stop running. >> >> >> >> [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code >> 0xC0209029. >> >> >> >> [Flat File Source [1]] Error: The attempt to add a row to the Data Flow >> task >> buffer failed with error code 0xC0047020. >> >> >> >> [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File >> Source" >> (1) returned error code 0xC02020C4. The component returned a failure >> code >> when the pipeline engine called PrimeOutput(). The meaning of the failure >> code is defined by the component, but the error is fatal and the pipeline >> stopped executing. >> >> >> >> [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code >> 0xC0047038. >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |