Bug in SSIS 2005 - or is it a feature?

Discussion in 'ASP .Net' started by John Kotuby, Mar 16, 2009.

  1. John Kotuby

    John Kotuby Guest

    Hi all,

    I was trying to accomplish I thought would be a simple task as part of a
    much larger data migration. Import a comma delimited CSV file into a SQL
    table. As it turns out, if there is a bad-data row in the source CSV the
    data-flow task does not simply pass that row via the "error" workflow arrow
    into another Row Count task meant to keep track of the error count. It then
    should move on to the next record (that was easy to configure in DTS 2000).

    Instead the whole Data Flow task is failed. It appears that I don't even get
    a chance to intercede in the event of a bad source record. I read a few
    posts about older packages failing after .NET 3.5 SP1 is installed.

    Apparently the PrimeOutput() method of the data-flow task simply fails the
    task upon encountering a malformed record

    I tried redirecting failed rows to a Row Count transform which redirects the
    output to a variable of INT32.
    The red line from the mdc_msc.csv source is even labelled "Flat File Source
    Error Output".

    However, the package is stall halted with a Fatal error.
    It is clear to me after looking at the CSV file in a text editor that on row
    1032 (of 155,000) there is a
    missing comma delimeter. Of course I could fix that line only to discover
    another 20 or so bad records.

    This package must be run daily to update a working database. It must run
    unattended and merely keep track of failed rows.

    I get the "input" files from a 3rd party so cannot fix whatever might be
    causing the error in the CSV file used for input.

    Below I have typed the error messages.

    Error: The column delimeter for column "Column 13" was not found.
    Error: An error occurred while processing file "mdc_msc.csv" on data row
    2003.
    Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on
    component "Source -
    mdc_msc_csv"(1) returned error code 0xC0202092. The componenet 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. There may be error messages posted
    before this with more
    information about the failure.
    Error: SSIS Error code DTS_E_THREADFAILED. Thread "SourceThread0" has exited
    with error code 0xC0047038.

    Thanks for any comment. I wish I could present this to a Microsoft SSIS
    expert.
     
    John Kotuby, Mar 16, 2009
    #1
    1. Advertising

  2. Alexey Smirnov, Mar 16, 2009
    #2
    1. Advertising

  3. John Kotuby

    John Kotuby Guest

    An expert said to direct the error output to a Row Count task. Unfortunately
    that does not work. The failure to "prep" the input souce row apparently
    causes the Data Flow task to abend. This really does appear to be a bug, if
    there is no way to capture the error at the source in a Data Flow task.

    Just wondered if anybody in this group has run across the same problem. I am
    putting this out there so that if someone does have a similar problem, they
    will know it's probably not due to a coding error on their part.

    A work-around is to run a Bulk Insert task (rather than DataFlow) with the
    help of a Format file. However, that method does not produce any Error File
    output.

    "Mark Rae [MVP]" <> wrote in message
    news:...
    > "John Kotuby" <> wrote in message
    > news:%...
    >
    >> Thanks for any comment.

    >
    > SSIS = SQL Server Integration Services; this is the ASP.NET newsgroup...
    >
    >> I wish I could present this to a Microsoft SSIS expert.

    >
    > What did they tell you in microsoft.public.integrationsvcs...?
    >
    >
    > --
    > Mark Rae
    > ASP.NET MVP
    > http://www.markrae.net
     
    John Kotuby, Mar 16, 2009
    #3
  4. John Kotuby

    John Kotuby Guest

    Thank you Alexey,
    I will try to post in the proper groups next time.

    I have scanned the forum posting and what I get out of it is that I may have
    to run a Script task to examine each row for errors (meaning I need to
    anticipate what may be wrong with the source data -- a difficult
    maybe impossible task) and then route the rows to a certain pipeline
    depending on the results of the Script task

    In the data I was using there appeared to be 1 bad row out of 150,000. The
    bad data was caused by too many quotes surrounding a character field. That
    apparently threw off the Field synchronization between the source and the
    meta-data table created by the package. In my opinion that occurrence should
    then spit the row out through the Error path (the little red arrow) to
    whatever garbage collector I wish to use, and not cause the task to fail
    completely.

    I admit that there is probably a lot that I don't understand about SSIS
    2005, however I think my take on the situation is just good common sense.
    Let's just say that if I were coding a tool like this, a malformed row would
    be spit and recorded...simple as that.

    I have seen numerous posts about slight variations in SQL Service packs or
    even just patches that will cause a SSIS package to run on one machine and
    fail on another. Whatever happened to backward compatability within the same
    Product generation?

    "Alexey Smirnov" <> wrote in message
    news:...
    On Mar 16, 2:10 pm, "John Kotuby" <> wrote:
    > Thanks for any comment. I wish I could present this to a Microsoft SSIS
    > expert.


    Although it is a wrong group, did you already see this?
    http://social.msdn.microsoft.com/Fo.../thread/674a1ce3-fe1b-4d14-81cb-ea235fbf8855/
     
    John Kotuby, Mar 16, 2009
    #4
  5. John Kotuby

    Paul Shapiro Guest

    I believe there is a data transfer task property to set the allowed number
    of failures. It defaults to 0, so any error cancels the task, but I seem to
    remember you could increase that limit. Then you'll get the error rows in
    the error output, and the good rows should be processed.

    You could also import the data into a pre-qualifying table with a single
    varchar field, if you can check for erroneous rows with sql.

    "John Kotuby" <> wrote in message
    news:...
    > Thank you Alexey,
    > I will try to post in the proper groups next time.
    >
    > I have scanned the forum posting and what I get out of it is that I may
    > have to run a Script task to examine each row for errors (meaning I need
    > to anticipate what may be wrong with the source data -- a difficult
    > maybe impossible task) and then route the rows to a certain pipeline
    > depending on the results of the Script task
    >
    > In the data I was using there appeared to be 1 bad row out of 150,000. The
    > bad data was caused by too many quotes surrounding a character field. That
    > apparently threw off the Field synchronization between the source and the
    > meta-data table created by the package. In my opinion that occurrence
    > should then spit the row out through the Error path (the little red arrow)
    > to whatever garbage collector I wish to use, and not cause the task to
    > fail completely.
    >
    > I admit that there is probably a lot that I don't understand about SSIS
    > 2005, however I think my take on the situation is just good common sense.
    > Let's just say that if I were coding a tool like this, a malformed row
    > would be spit and recorded...simple as that.
    >
    > I have seen numerous posts about slight variations in SQL Service packs or
    > even just patches that will cause a SSIS package to run on one machine and
    > fail on another. Whatever happened to backward compatability within the
    > same Product generation?
    >
    > "Alexey Smirnov" <> wrote in message
    > news:...
    > On Mar 16, 2:10 pm, "John Kotuby" <> wrote:
    >> Thanks for any comment. I wish I could present this to a Microsoft SSIS
    >> expert.

    >
    > Although it is a wrong group, did you already see this?
    > http://social.msdn.microsoft.com/Fo.../thread/674a1ce3-fe1b-4d14-81cb-ea235fbf8855/
    >
     
    Paul Shapiro, Mar 17, 2009
    #5
  6. John Kotuby

    Paul Shapiro Guest

    Nice coincidence- I just saw an article on SSIS error management:
    http://www.sqlservercentral.com/articles/SSIS/65758/

    "Paul Shapiro" <> wrote in message
    news:%23kf1Y%...
    >I believe there is a data transfer task property to set the allowed number
    >of failures. It defaults to 0, so any error cancels the task, but I seem to
    >remember you could increase that limit. Then you'll get the error rows in
    >the error output, and the good rows should be processed.
    >
    > You could also import the data into a pre-qualifying table with a single
    > varchar field, if you can check for erroneous rows with sql.
    >
    > "John Kotuby" <> wrote in message
    > news:...
    >> Thank you Alexey,
    >> I will try to post in the proper groups next time.
    >>
    >> I have scanned the forum posting and what I get out of it is that I may
    >> have to run a Script task to examine each row for errors (meaning I need
    >> to anticipate what may be wrong with the source data -- a difficult
    >> maybe impossible task) and then route the rows to a certain pipeline
    >> depending on the results of the Script task
    >>
    >> In the data I was using there appeared to be 1 bad row out of 150,000.
    >> The bad data was caused by too many quotes surrounding a character field.
    >> That apparently threw off the Field synchronization between the source
    >> and the meta-data table created by the package. In my opinion that
    >> occurrence should then spit the row out through the Error path (the
    >> little red arrow) to whatever garbage collector I wish to use, and not
    >> cause the task to fail completely.
    >>
    >> I admit that there is probably a lot that I don't understand about SSIS
    >> 2005, however I think my take on the situation is just good common sense.
    >> Let's just say that if I were coding a tool like this, a malformed row
    >> would be spit and recorded...simple as that.
    >>
    >> I have seen numerous posts about slight variations in SQL Service packs
    >> or even just patches that will cause a SSIS package to run on one machine
    >> and fail on another. Whatever happened to backward compatability within
    >> the same Product generation?
    >>
    >> "Alexey Smirnov" <> wrote in message
    >> news:...
    >> On Mar 16, 2:10 pm, "John Kotuby" <> wrote:
    >>> Thanks for any comment. I wish I could present this to a Microsoft SSIS
    >>> expert.

    >>
    >> Although it is a wrong group, did you already see this?
    >> http://social.msdn.microsoft.com/Fo.../thread/674a1ce3-fe1b-4d14-81cb-ea235fbf8855/
    >>

    >
     
    Paul Shapiro, Mar 17, 2009
    #6
  7. Alexey Smirnov, Mar 17, 2009
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. LilC

    How to replace SSIs

    LilC, Jun 12, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    726
    Alan Silver
    Jun 19, 2006
  2. Carlos A.

    How can I call SSIS from ASP.NET (VB)

    Carlos A., Aug 10, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    6,399
    Carlos A.
    Aug 10, 2006
  3. =?Utf-8?B?TWljaGFlbA==?=

    How to call an SSIS package from ASP.NET 2.0?

    =?Utf-8?B?TWljaGFlbA==?=, Jun 7, 2007, in forum: ASP .Net
    Replies:
    3
    Views:
    1,169
    Steven Cheng[MSFT]
    Jun 8, 2007
  4. David Lozzi

    Error running SSIS from .Net 2

    David Lozzi, Dec 7, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    1,127
    David Lozzi
    Dec 7, 2007
  5. Luigi
    Replies:
    1
    Views:
    781
    Gregory A. Beamer
    Dec 9, 2009
Loading...

Share This Page