locked
Calling ALL Experts...SSIS Package keep failing in Debug Mode..SQL 2005 RRS feed

  • Question

  • Hello everyone,

    I just want to thank you in advance for all the help that you can give today. 

    I just started at a new Company, as a junior DBA, the previous person left. Before that person left he created a lot of SSIS packages and turn them into jobs basically to refresh many different tables on the SQL server. The datas are being import from another datasource, which is why the SSIS packages was created so we can bring the datas over to SQL side.  Whenever I want to create a new SSIS Package for a new job, I always get the same failure as below...


    "Error: 0xC0047062 at Data Flow Task, Source - Query [1]: System.OverflowException: Arithmetic operation resulted in an overflow.
    at System.Data.Odbc.OdbcStatementHandle.RowCount(SQLLEN& rowCount)
    at System.Data.Odbc.OdbcDataReader.GetRowCount()
    at System.Data.Odbc.OdbcDataReader.FirstResult()
    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
    at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
    at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
    Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Source - Query" (1) failed the pre-execute phase and returned error code 0x80131516.
    Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
    Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - GLCGLDS" (103)" wrote 0 rows.
    Task failed: Data Flow Task
    SSIS package "SSIS_GLCGLDS_GL.dtsx" finished: Failure.
    The program '[16836] SSIS_GLCGLDS_GL.dtsx: DTS' has exited with code 0 (0x0)."

    All the SSIS packages are failing for me, not just the one I created, even the ones that were working before, the ones that were created by the previous person, I just went to Start Debugging and it would failed at the same exact stage


    Below are the exact steps that I do to create the SSIS Package (based on the notes from previous person)

    1. right click on SSIS Package and select sql server import and export wizard and click next
    2. Select data source as .Net framework Data Provide for ODBC
    3. Input Datasource, and driver as: TS ODBC Multi – Tier Driver
    4. Choose destination as Microsoft OlE DB Provide for SQL Server
    5. Write a basic select statement.
    6. Click edit Mappings
    7. Here's the important part... I am ABLE to preview the DATA.
    8. Click ok  next  finished
    9. Click debug start debugging
    10. The preparation SQL task will be green, but the Data Flow Task will failed everytime for me.

    If anyone has any suggestion please let me know, b/c I have try every possible ways and out of luck, and I don't want to explain to my boss I don't know how to do it or how the previous person does it without any failures.

    TL


    • Edited by tkl_2014 Wednesday, February 26, 2014 5:03 PM
    Wednesday, February 26, 2014 5:02 PM

Answers

  • Hi tkl_2014,

    The error “Arithmetic operation resulted in an overflow” sometimes may occur due to the 32-bit/64-bit driver issue. Since BIDS is a 32-bit software, the current DSN used in the ADO NET Source should use 32-bit TS ODBC Multi – Tier Driver (e.g. the DSN is created by C:\Windows\SysWOW64\odbacad32.exe if it is a 64-bit OS). In this case, the package should run in 32-bit runtime mode, therefore, you should make sure the Run64BitRuntime property of the Project is set to False. Please see the screenshot:

    If you have installed 64-bit TS ODBC Multi – Tier Driver and created a DSN using this driver, you can run the package in 64-bit runtime mode by setting the Run64BitRuntime property to True.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, March 9, 2014 11:21 AM
    Thursday, March 6, 2014 7:11 AM

All replies

  • what is in Data Flow Task, Source - Query?

    It looks like just an incorrect SQL and variable size combo.


    Arthur My Blog

    Wednesday, February 26, 2014 5:17 PM
  • It can't be an incorrect SQL, b/c it is just a basic select statement from a table of the data source, I am able to preview the data on the select statement...

    The source query is what failing in the Data Flow Task...

    Keep in mind that this is happening on all SSIS packages...not just the one I created

    TKL

    Wednesday, February 26, 2014 5:27 PM
  • Please show screenshots and share how I would be able to repro

    Arthur My Blog

    Wednesday, February 26, 2014 7:17 PM
  • Unfortunately I can't insert image yet, b/c they need to verify my account first...but just to paint you a picture...

    I remote desktop onto the SQL server

    Open up any SSIS Production Package

    Click Debug and Start debugging

    And it would failed at the Source Query

    the failed message is from my original post...

    This happens for all SSIS Production package that I opened up.

    TKL

    Wednesday, February 26, 2014 8:26 PM
  • Unfortunately I can't insert image yet, b/c they need to verify my account first...but just to paint you a picture...

    I remote desktop onto the SQL server

    Open up any SSIS Production Package

    Click Debug and Start debugging

    And it would failed at the Source Query

    the failed message is from my original post...

    This happens for all SSIS Production package that I opened up.

    TKL

    We have enough image hosting sites around to cope with this temporary limitation e.g. OneDrive,  ImageShack, GDrive, etc.

    You cannot debug a package w/o creating a SSIS project 1st.

    Why do you need all these 10 steps I cannot comprehend, you do not have any credentials in the config or stored in the package?


    Arthur My Blog

    Wednesday, February 26, 2014 8:47 PM
  • https://onedrive.live.com/#cid=82BC4A3085967BD9&id=82BC4A3085967BD9%21110

    thank you for letting me know about Onedrive...I've upload the screenshots on the link above...

    The 10 steps are what I was taught to create a new SSIS package and then run it as a job....

    2nd part of your comment intrigue me...what you mean by "you do not have any credentials in the config or stored in the package?"

    TKL

    Wednesday, February 26, 2014 8:59 PM
  • What drives the Source - Query?

    The query is what I'd like to see.


    Arthur My Blog

    Wednesday, February 26, 2014 9:05 PM
  • I have uploaded a screen shot of the source query onto Onedrive, please let me know if you need anything else.

    TKL

    Wednesday, February 26, 2014 9:14 PM
  • Oh gosh, SELECT *

    so if you look at:

    Right-click on the source, advance editor, output columns, what is the metadata definition?

    What do you get when you run this SELECT *?


    Arthur My Blog

    Wednesday, February 26, 2014 10:01 PM
  • Hah. After some pondering I started to suspect the built in SSIS` SELECT COUNT(*) to check for records dies because there are too many to retrieve.

    Try SELECT A, B, C WHERE A > DATEADD(dd, -1, GETDATE()) and see if it works (or whatever SQL would be correct).


    Arthur My Blog

    Wednesday, February 26, 2014 10:05 PM
  • The job must have a configuration file attached to it.  That is why it doesn't run when you just try to run it. 

    I normally set the default settings in the SSIS package to garbage to make sure a configuration file is used.

    Wednesday, February 26, 2014 10:08 PM
  • I think its just to do with you selecting so many rows like Arthur explained. Can you try selecting specific columns only? And see if it works? I would suggest putting a where clause just to regulate the number of rows.

    if you get a similar error can you please try doing something like "Select top 10000 * from Tablename"?

    By the way what is the count of this table you are doing a select * from?

    Look forward to your responses.

    Cheers

    Nimish

    Thursday, February 27, 2014 6:07 AM
  • I modify the select query to select top 10 * only and this time I get a different failure message:

    ""component "Source - Query" (1)" failed validation and returned validation status "VS_ISBROKEN"

    I searched for this same error online and was able to come across one with the same issue and that person was able to fix it by the following

    ".the TargetConnectionString in Data flow task points to the correct database but in ssis configuration its value coming from parent packages which was pointing to different database..so fixed the connection string..."

    I am fairly new at this SSIS stuffs, so if someone could be more specific on what I need to do regarding with the above solution, please tell me more on how to solve this issue.

    TKL

    Thursday, February 27, 2014 1:59 PM
  • Can you describe and show how you set the connectivity up?

    I guess you either have a problem with the connection string or the driver.


    Arthur My Blog

    Thursday, February 27, 2014 3:13 PM
  • Below are the exact steps that I do to create the SSIS Package (based on the notes from previous person)


    1. right click on SSIS Package and select sql server import and export wizard and click next
    2. Select data source as .Net framework Data Provide for ODBC
    3. Input Datasource, and driver as: TS ODBC Multi – Tier Driver
    4. Choose destination as Microsoft OlE DB Provide for SQL Server
    5. Write a basic select statement. (select * from tableA)
    6. Click edit Mappings to modify few column with their data type
    7. Next -> Here's the important part... when I clicked on Preview Data, I am ABLE to preview the DATA.

    Finished.

    Thursday, February 27, 2014 4:19 PM
  • I started to suspect it is the TS ODBC Multi – Tier Driver

    you have other machines with it, right? Then go check how it is configured elsewhere.

    Bear in mind the preview does not grantee any success because it does not push the data to the target, it only puts into grid a sample.


    Arthur My Blog

    Thursday, February 27, 2014 4:28 PM
  • What confusing to me is, all the existing SSIS Packages that was created before by the previous person it must have to work then b/c they were able to schedule jobs off of those packages...

    But now when I open up those same SSIS Packages in BIDS and run Debug on those, it fails.  

    So let forget on the failure of creating a new SSIS package, why is it failing on the existing ones as well.

    TKL

    Thursday, February 27, 2014 4:40 PM
  • I again suspect your local setup of the driver "TS ODBC Multi – Tier Driver".

    Arthur My Blog

    Thursday, February 27, 2014 4:45 PM
  • This driver is from a 3rd party Vendor, Thoroughbred Software (TS), it allows SQL server to communicate with the datasource, when you say you suspect this driver issue, I'm not exactly sure what else you would need to see / check out to help with this issue.  Please let me know what you need and I'll get it to you, thanks.

    TKL

    Thursday, February 27, 2014 5:01 PM
  • You have been unable to pull data using this driver. Do you have alternative means of connecting? Or try using a newer / different build of the driver. Or it could be that the target got upgraded and now this driver is unsupported

    Arthur My Blog

    Thursday, February 27, 2014 5:08 PM
  • I have an alternative mean of connecting right now by creating a Linked Server on SQL and run a query as such: 

    SELECT * FROM OPENQUERY (linkedserver, 'SELECT * FROM TABLE1')

    The linkedserver would be the datasource, and the table would be from that same particular datasource.

    After getting the result of the query I then append the data to the table on SQL side.

    Nothing has changed that I am aware of regarding to target got upgraded and what not...then again I'm not so sure entirely myself

    TKL

    Thursday, February 27, 2014 5:24 PM
  • But you do you have any idea on the below resolution

    "".the TargetConnectionString in Data flow task points to the correct database but in ssis configuration its value coming from parent packages which was pointing to different database..so fixed the connection string..."

    like how to actually do it.

    TKL

    Thursday, February 27, 2014 6:58 PM
  • I had a similar issue several years ago. Extracting data from a Progress database using Multi Tier ODBC from Open Link.

    All packages using the ODBC system DSN started failing after a security update to dot net (2.0).

    The failure message was something like the following:

    [DataReader Source [302]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source" (302)" failed because error code 0x80131937 occurred, and the error row disposition on "output column ...

    The solution was to redirect DataReader Source Output to Konesans' Trash Destination

    Data Flow with Trash Destination


    Chaos, Disorder and Panic ... my work is done here!


    • Edited by DaveBurk Thursday, February 27, 2014 7:24 PM Add image link
    Thursday, February 27, 2014 7:02 PM
  • But you do you have any idea on the below resolution

    "".the TargetConnectionString in Data flow task points to the correct database but in ssis configuration its value coming from parent packages which was pointing to different database..so fixed the connection string..."

    like how to actually do it.

    TKL


    Check the configuration (a file, table)

    Arthur My Blog

    Thursday, February 27, 2014 7:03 PM
  • I had a similar issue several years ago. Extracting data from a Progress database using Multi Tier ODBC from Open Link.

    All packages using the ODBC system DSN started failing after a security update to dot net (2.0).

    The failure message was something like the following:

    [DataReader Source [302]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source" (302)" failed because error code 0x80131937 occurred, and the error row disposition on "output column ...

    The solution was to redirect DataReader Source Output to Konesans' Trash Destination


    Chaos, Disorder and Panic ... my work is done here!

    Please show me how to redirect DataReader Source Output to Konesans's Trash..I do not see where I could do that after clicking on the Datasource Reader...I've attached a screen shot so you can see what I see within Datasource

    link to pic:

    https://onedrive.live.com/?cid=82BC4A3085967BD9&id=82BC4A3085967BD9%21110&ppud=4

    Thursday, February 27, 2014 7:15 PM
  • Google Konesans trash destination.

    Add the component to your toolbox.

    See image link in my previous post.


    Chaos, Disorder and Panic ... my work is done here!

    Thursday, February 27, 2014 7:27 PM
  • Right Click any where in the Control Flow or in SSIS menu choose Package Configuration.

    Uncheck "Enable Package Configuration" and try to run the package see if this works.

    If this works then you need to change the configuration accordingly... as while running package it is assigning different connection string...

    Thanks,

    Amit 

    Thursday, February 27, 2014 7:32 PM
  • Right Click any where in the Control Flow or in SSIS menu choose Package Configuration.

    Uncheck "Enable Package Configuration" and try to run the package see if this works.

    If this works then you need to change the configuration accordingly... as while running package it is assigning different connection string...

    Thanks,

    Amit 

    The Enable Package was not check in the first place and the package still fail.
    Thursday, February 27, 2014 8:51 PM
  • Can you go to the dataconnection and see its properties? I am more keen on seeing if you have any expressions for them. If you do, can you please remove them? and then try running the data flow again.

    How to do this:

    1) Select the connection and right click -> "Properties". In the pane that pops up there will be a drill down for Expressions. Drill down and check if you have anything set. if you do, please remove it.

    2) Can you also go to Control Panel -> Administrative Tools -> Data Sources (ODBC). You should see the DSN you select while creating the package. Can you test the DSN connection?

    3) I am probably thinking all over the place but have you tested your connection in SSIS? I know you are able to see the preview but have you tested the connection that is created per se? For this please double click the connection and then click Test Connection.

    let me know how it goes.

    N




    Friday, February 28, 2014 5:34 AM
  • There is nothing in the drill down under Expression.

    The system DSN connection is working like it should b/c I was able to test it.

    Same thing for the SSIS package connection, it tested successfully as well, I have attached a pic for your reference.

    https://onedrive.live.com/?cid=82BC4A3085967BD9&id=82BC4A3085967BD9%21110&ppud=4

    Please let me know if you want to try anything else.

    TKL

    Monday, March 3, 2014 10:39 PM
  • Hi tkl_2014,

    The error “Arithmetic operation resulted in an overflow” sometimes may occur due to the 32-bit/64-bit driver issue. Since BIDS is a 32-bit software, the current DSN used in the ADO NET Source should use 32-bit TS ODBC Multi – Tier Driver (e.g. the DSN is created by C:\Windows\SysWOW64\odbacad32.exe if it is a 64-bit OS). In this case, the package should run in 32-bit runtime mode, therefore, you should make sure the Run64BitRuntime property of the Project is set to False. Please see the screenshot:

    If you have installed 64-bit TS ODBC Multi – Tier Driver and created a DSN using this driver, you can run the package in 64-bit runtime mode by setting the Run64BitRuntime property to True.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, March 9, 2014 11:21 AM
    Thursday, March 6, 2014 7:11 AM