none
Existing SSIS Packages Generating New Errors.

    Question

  • Good Afternoon!

    I have quite a few SSIS Packages that periodically pull data from our ERP system via ODBC.  These packages have been running fine for over a year and this weekend they began generating errors and failing.  Here is the Error that the packages generate:

     

    - Executing (Error)
     Messages
     * Error 0xc0209029: Data Flow Task: The "component "Source - Query" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "WhseCode" (12)" specifies failure on error. An error occurred on the specified object of the specified component.
      (SQL Server Import and Export Wizard)
     
     * Error 0xc02090f5: Data Flow Task: The component "Source - Query" (1) was unable to process the data.
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5.  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.
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
      (SQL Server Import and Export Wizard)

     


     In the first section of the error where it says "Output Column "WhseCode" (12)", this value is different for each package and is always the name of the first column in the import.

    Thanks!
    Jeremy Lane, MCSE

    Tuesday, February 03, 2009 6:41 PM

Answers

  • I do not know if this relates to you:

    Windows server 2003 RC1
    SQL Server 2005 std ed.

    I had quite a few SSIS packages developed using BIDS.
    The packages extracted data from a Progress box using Open Link ODBC (3.2) drivers.

    These packages worked without problems for over a year. Then I installed dot net framework 3.5 sp1.

    These packages stopped working. They would fail at the data flow task  (data reader source component).
    Nothing changed with my progress box and system DSN did not change.

    Uninstalled dot net 3.5 sp1. Still did not work.

    Also, uninstalled dot net 3.0 sp1, and dot net 2.0 sp2.

    Re-installed dot net 2.0, applied dot net 2.0 sp1, SSIS packages working properly again.
     
    I do not not know what was the culprit. Whether it was dot net 3.5 sp1 (which apparently also patches dot net 2.0 to sp2)
    or dot net 2.0 sp2.

    For forewarned, if you run IIS on the same box, you may have to re-target the dot framework.

    I hope this helps.

    Best of luck!



    • Marked as answer by jdlcomputer Tuesday, February 03, 2009 7:49 PM
    Tuesday, February 03, 2009 7:14 PM

All replies

  • Did you install anything on your system? Sounds similar to a problem I had.

    Tuesday, February 03, 2009 6:47 PM
  • In installed a few MS updates.  I had tested them on a sandbox server and had no issues, but that's the only thing I can think of.  I did roll back all of the updates and the issues is still there.
    Tuesday, February 03, 2009 6:51 PM
  • I do not know if this relates to you:

    Windows server 2003 RC1
    SQL Server 2005 std ed.

    I had quite a few SSIS packages developed using BIDS.
    The packages extracted data from a Progress box using Open Link ODBC (3.2) drivers.

    These packages worked without problems for over a year. Then I installed dot net framework 3.5 sp1.

    These packages stopped working. They would fail at the data flow task  (data reader source component).
    Nothing changed with my progress box and system DSN did not change.

    Uninstalled dot net 3.5 sp1. Still did not work.

    Also, uninstalled dot net 3.0 sp1, and dot net 2.0 sp2.

    Re-installed dot net 2.0, applied dot net 2.0 sp1, SSIS packages working properly again.
     
    I do not not know what was the culprit. Whether it was dot net 3.5 sp1 (which apparently also patches dot net 2.0 to sp2)
    or dot net 2.0 sp2.

    For forewarned, if you run IIS on the same box, you may have to re-target the dot framework.

    I hope this helps.

    Best of luck!



    • Marked as answer by jdlcomputer Tuesday, February 03, 2009 7:49 PM
    Tuesday, February 03, 2009 7:14 PM
  • Thanks Dave!  That worked perfectly.
    Tuesday, February 03, 2009 7:50 PM
  •  in a nutshell, the cause of the problem is that .net 2.0 isn't forward compatible with .net 3.X.  what this means is that applications that are written to target .net 2.0 won't work in a .net 3.X environment.  an ssis 2005 package is just another .net 2.0 application, so it won't work in a .net 3.5 environment.  fyi: this is also why ssis 2005 packages won't work in ssis 2008 unless they're upgraded.

    hth

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    Wednesday, February 04, 2009 6:44 AM
  •  
    I have to take issue with your assertion
    "an ssis 2005 package is just another .net 2.0 application, so it won't work in a .net 3.5 environment".

    On a clean system, with only dot net 2.0 SP1 installed, I created a simple package extracting data from a Progress box using ODBC.
    No problems. Installed dot net 3.5. Same package executed with no problems.

    Applied  #KB951847 Microsoft .Net Framework 3.5 Service Pack 1 and .Net Framework 3.5 Family Update(x86).

    Package fails. It fails at the data reader source component.

    Apparently #KB951847, also update dot net 2.0 to sp2. I suspect this is the culprit.
    Is it the intent of this update to break applications written with .net 2.0?


    Is anyone experiencing the same issue when then apply #KB951847 (it appears to affect ODBC sources)?

    Wednesday, February 04, 2009 8:46 PM
  • DaveBurk said:

     
    I have to take issue with your assertion
    "an ssis 2005 package is just another .net 2.0 application, so it won't work in a .net 3.5 environment".

    On a clean system, with only dot net 2.0 SP1 installed, I created a simple package extracting data from a Progress box using ODBC.
    No problems. Installed dot net 3.5. Same package executed with no problems.

    Applied  #KB951847 Microsoft .Net Framework 3.5 Service Pack 1 and .Net Framework 3.5 Family Update(x86).

    Package fails. It fails at the data reader source component.

    Apparently #KB951847, also update dot net 2.0 to sp2. I suspect this is the culprit.
    Is it the intent of this update to break applications written with .net 2.0?


    Is anyone experiencing the same issue when then apply #KB951847 (it appears to affect ODBC sources)?



    ok, i mispoke. you're correct.  .net 3.0 is just .net 2.0 plus indigo, so that won't affect applications written to target .net 2.0.

    .net 3.5 is just .net 3.0 plus linq and some other stuff, but the .net 2.0 core is still there. so, that won't affect applications written to target .net 2.0 either.

    .net 3.5 sp1 is .net 3.0, but with an overhauled version of ado.net plus some other stuff. thus, it breaks applications written to target .net 2.0, including ssis 2005 packages  this is the main reason why visual studio 2008 was designed to allow applications to be written to target either .net 2.0 or .net 3.5 -- because applications written to target .net 2.0 will break when attempted to be executed in .net 3.5 sp1 environment and vice-versa.  i think that this outcome was unavoidable, but it's a shame that it had to happen because it would and could blindside new .net developers who aren't familiar with earlier versions of the .net framework.



    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    Thursday, February 05, 2009 6:00 AM
  • I had the same problem and implemented the same solution which worked for me.  Uninstall NET Frame3.5, 3.0, and 2.0 then reinstalle 2.0 and 2.0 SP1 and it works.  I still have to figure out how to get IIS working again, but otherwise it is working.

    Monday, February 09, 2009 7:12 PM
  • If you have many site/subsites, you need to re-target the appropriate dot net framework. Since dot net framework 2.0 was un-installed. All sites which may have targeted dot net framework 2.0 have been re-targeted to dot net 1.1 (assuming you have 1.1 installed).


    In IIS Manager, you have to right click each node > Properties > ASP.Net tab > Select the appropriate dot net framework.


    Best wishes!
    Monday, February 09, 2009 7:38 PM
  • Thanks Dave.  I tried this and it doesn't seem to have worked, but I haven't had a chance to reboot the server, so I'll hold out hope.  I have another wrinkle that may be causing me trouble.  This is a 64 bit server and I use some things in 64bit mode and others in 32bit mode.  I'm not sure if IIS was in 32 or 64bit mode.  I registered the .NET2.0 64bit version, so now I'm wondering if it should have been 32bit.

    Now when I try to register 32bit it tells me IIS is in 64bit mode and won't let me apply 32bit .NET. 

    I guess I'll see after a reboot later today if it is still an issue.

    Thanks again,
    Hugh
    Monday, February 09, 2009 8:56 PM
  • My understanding is that .net 2.0 64 bit installs both 64 and 32 bit versions.

    Maybe the issue you are having is trying to apply changes while IIS is still running.

    Instead of waiting for the opportunity to re-boot the server, you may want to try this in the interim:

    (1) In IIS Manager, on the Default Web Site node, right click and stop your website(s)
    (2) Select  each site and apply the dot net version you want.
    (3) On your root computer node, right click > All Tasks > Restart IIS
    (4) Default web site node, start your website.

    I hope this helps.

    As always, best wishes!





    Monday, February 09, 2009 9:16 PM
  • I found that I needed to "Allow" ASP.NET v2.0 under the "Web Service Extensions" folder, but that still didn't do it.  I tried your suggestion, but still no luck.  Thanks for your help.  I'll post if it ends up working.
    Monday, February 09, 2009 9:38 PM
  • I found when querying by ODBC I had to wrap my varchar fields in a nullif(fieldx,'') to return null instead of blank, or a pad(fieldx) to fill out the length of the field, or simply add a space (fieldx + ' '), but the empty string value definately causes problems. (possible commands will be DBMS specific of course!)
    • Proposed as answer by obMana Monday, July 20, 2009 2:07 PM
    Thursday, June 25, 2009 12:43 AM
  • Hi all!

    I'd like to say i had very similar problem pulling data from Progress database with OpenEdge ODBC driver in MS SSIS 2005. Similar, low descriptive, not helping at all, error notes appeared as jdccomputer wrote in top post.
    As it appeared there was a character field (recognized by SSIS as DT_WSTR data type field 160 characters long) in table causing errors right in Data reader component.
    Workaround for solving this problem was in getting field in several parts and concatenate it later in one field.

    Let's say field's name is 'Description' and its 160 characters long. I'd get this field with following SELECT statement:

    SELECT RTRIM (LTRIM (SUBSTRING (Description,1,50))) AS Desc1, RTRIM (LTRIM (SUBSTRING (Description,51,50))) AS Desc2, RTRIM (LTRIM (SUBSTRING (Description,101,60))) AS Desc3

    Later in Derived column I'd create new column (eg. Description_Out) which value would be simple Desc1 + Desc2 + Desc3

    Hope it saved someone's time ...
    Monday, July 20, 2009 3:23 PM
  • Thanks to all who have helped out on this thread. After days and days of trying to figure out why my SSIS package stopped working, this thread finally helped me to find the answer. I will explain what happened to me, in case another reader happens to find themselves in a similar situation.

    I had been using an SSIS package to load in data from a remote database using the Sybase Adaptive Server Enterprise driver on my own machine and on two 64 bit servers.

    The load was working fine, but then it stopped working suddenly complaining of error code 0x80131937, just like the original poster. But the weird thing was that the exact same DTSX package stopped working on my PC and on only one of the two servers - it kept working on one of them. 

    So I tried everything:

    - Stopping the package from failing on error to see what rows would be loaded into the database - every row loaded but every column was set to null.
    - Rolled Sybase driver back to ASE ODBC 12.5 - this actually worked but I could not roll the drivers back on the server machines so I had to find another answer.
    - Tried different query formats, but it failed every time.

    Then, I stumbled across this thread and the good advice on the .NET framework versions proved to be the answer! On my local workstation machine and the server which stopped working, I had .NET 3.5 SP1 installed. But on the server which was still working, the .NET framework was 3.5 without SP1.

    Now I've rolled it back and it's working again. I can't believe that 3.5 SP1 would just break ADO like this. I don't have a problem with compatibility issues between versions of .NET, but some explanation or notification would be useful.

    Oh well, the things you learn.

    Friday, November 20, 2009 9:40 AM
  • well, here's my understanding: .net 3.5 sp1 includes the ado.net entity framework...which isn't compatible with previous versions of ado.net.  what this means is that ssis 2005 packages will break when they execute on a machine that has .net 3.5 sp1 installed because such packages target ado.net 2.0 which is incompatible with the ado.net entity framework (ado.net 3.5 sp1).  for some unknown reason, microsoft was unable to get the ado.net entity framework to be backward compatible with ado.net 2.0, so breaking applications was an unavoidable consequence.
    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

     

    SSIS Business Intelligence

    Monday, November 23, 2009 5:14 AM
  • I had same problem with ODBC drivers and I did as follows. I hope it will work for who having ODBC drivers problem

    Let me know if my procedure wrong or not advised to use....


    I changed column's error and truncation values from "fail component" to "redirect row" in  "error output" tab in source and added destination for error output.

    Thanks,
    Brahma


    ------------------------ Brahma http://vbrchowdary.info
    Tuesday, November 24, 2009 3:18 PM
  • This may be an older thread but I suspect it's still being seen by those who are experiencing this problem. Therefore I thought that I would suggest a workaround to this issue that was successful for me (this is similar to Brahma's post):

    https://connect.microsoft.com/SQLServer/feedback/details/416455/net-3-5-sp1-breaks-ssis-in-sql-server-2005?wa=wsignin1.0

    I did not have to remove .NET 3.5 SP1 and my packages started working again after applying this workaround. HTH

    Monday, May 31, 2010 10:04 AM
  • MS SOLUTION:

    http://support.microsoft.com/kb/969845

    Wednesday, April 27, 2011 8:31 AM