locked
Package Error - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified RRS feed

  • Question

  • Hi ALL ,

    If i try to manually execute the package from the sql server agent server the package runs fine ,next  if i schedule the package to run after half an hour from the last manual run then also it runs fine . Now the difference of the next schedule run is more than 14 hours i get the below error please help :

    Date        3/7/2014 2:58:00 AM
    Log        Job History (SSIS package)

    Step ID        1
    Server        AECRTYUEIN\SSISEXPRESS
    Job Name        SSIS package
    Step Name        Package
    Duration        00:00:00
    Sql Severity    0
    Sql Message ID    0
    Operator Emailed    
    Operator Net sent    
    Operator Paged    
    Retries Attempted    0

    Message
    Executed as user: AECRTYUEIN\Administrator. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:58:00 AM  Error: 2014-03-07 02:58:00.68     Code: 0xC0208449     Source: Data Flow Task 1 Source - Query [95]     Description: ADO NET Source has failed to acquire the connection {68D5B1D7-17B1-4F95-9B1D-7615425C4A07} with the following error message: "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".  End Error  Error: 2014-03-07 02:58:00.68     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source - Query failed validation and returned error code 0xC0208449.  End Error  Error: 2014-03-07 02:58:00.68     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2014-03-07 02:58:00.69     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:58:00 AM  Finished: 2:58:00 AM  Elapsed:  0.438 seconds.  The package execution failed.  The step failed.


    • Edited by SSISJoostMVP Friday, March 7, 2014 10:12 AM title little too general
    Friday, March 7, 2014 9:48 AM

Answers

  • Hi Priya,

    Is the DSN a System DSN or a User DSN? Please make sure it is a System DSN so that it can be accessed by each user or even no user logs onto the server.

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Mike Yin Monday, March 17, 2014 4:46 PM
    • Marked as answer by SSISJoostMVP Saturday, November 8, 2014 10:26 AM
    Friday, March 14, 2014 7:38 AM

All replies

  • Hi,

    Based on your error code, this could be the problem - http://www.codeproject.com/Articles/534651/How-to-solve-SSIS-error-code-0xC020801C-0xC004700C .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, March 7, 2014 10:02 AM
  • Have you tried running the package in 32bit mode? You can change that in the jobstep:

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Friday, March 7, 2014 10:14 AM
  • Hi SSISJoost ,

    Am running the package in 32 bit mode only and it runs fine when manually executed from the sql agent

    but even runs fine if i schedule it to run after 30 min after the manual run .It fails when the difference was about

    14 hours or so .

    Thanks

    Friday, March 7, 2014 10:22 AM
  • Hi SSISJoost ,

    Am running the package in 32 bit mode only and it runs fine when manually executed from the sql agent

    but even runs fine if i schedule it to run after 30 min after the manual run .It fails when the difference was about

    14 hours or so .

    Thanks

    Ok, so no driver / 32bit issues. It runs fine when executing job manually or when scheduled within a short period after a manual run... but not when there is a long period between the manual run and the scheduled run.

    Do you have some more details:
    - Which SSIS version/edition are you using?
    - Which ODBC driver are you using (or what's the source)?
    - Which operating system are you using?
    - Does your package have some form of error logging with more details?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, March 7, 2014 10:34 AM
  • Hi SSISJoost,

    1. Am using the sql server enterprise edition 2012

    2. ODBC driver came with the client installation of the AR remedy user client. It's an 32 bit ODBC . Requires

    server name ,username ,password and the port number all fields filled.

    3. Windows Server 2012 r2

    4.I actually don't have much idea from where i can extract the logging data . The information posted in the first post by me i got the from the history of sql agent .

    Please let me know if more info is required .

    Thanks

    Friday, March 7, 2014 10:39 AM
  • Hi ALL ,

    I noticed something when my sql management session is connected the agent will successfully run once the session is disconnected . The agent fails . Am using a proxy account with windows administrator credentials .

    Thanks

    Friday, March 7, 2014 2:07 PM
  • Hi Priya,

    Is the DSN a System DSN or a User DSN? Please make sure it is a System DSN so that it can be accessed by each user or even no user logs onto the server.

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Mike Yin Monday, March 17, 2014 4:46 PM
    • Marked as answer by SSISJoostMVP Saturday, November 8, 2014 10:26 AM
    Friday, March 14, 2014 7:38 AM