SolvedPrivate

ssis job scheduling issue

Posted on 2013-06-22
22
38 Views
Last Modified: 2016-02-11
Error-log
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
06/22/2013 14:25:19,DailyPBM,Error,0,DC1R3-MSVMPDB01,DailyPBM,(Job outcome),,The job failed.  The Job was invoked by User MED\bk.  The last step to run was step 1 (iBM).,00:00:01,0,0,,,,0
06/22/2013 14:25:19,ibm,Error,1,DC1R3-MSVMPDB01,ibm,PBM,,Executed as user: MED\bbb-sql. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:25:19 PM  Error: 2013-06-22 14:25:20.59     Code: 0xC0047062     Source: c_d_billingchute ADO NET Source [1]     Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle<c/> RetCode retcode)     at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection<c/> OdbcConnectionString constr<c/> OdbcEnvironmentHandle environmentHandle)     at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection<c/> OdbcConnectionString connectionOptions)     at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options<c/> Object poolGroupProviderInfo<c/> DbConnectionPool pool<c/> DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection<c/> DbConnectionPoolGroup poolGroup)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection<c/> DbConnectionFactory connectionFactory)     at System.Data.Odbc.OdbcConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName<c/> String connStr<c/> Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper<c/> Object transaction)  End Error  Error: 2013-06-22 14:25:20.59     Code: 0xC0047017     Source: c_d_billingchute SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131937.  End Error  Error: 2013-06-22 14:25:20.59     Code: 0xC004700C     Source: c_d_billingchute SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-06-22 14:25:20.59     Code: 0xC0024107     Source: c_d_billingchute      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:25:19 PM  Finished: 2:25:20 PM  Elapsed:  0.812 seconds.  The package execution failed.  The step failed.,00:00:01,0,0,,,,0
0
Comment
Question by:Josh2442
  • 10
  • 6
22 Comments
 

Author Comment

by:Josh2442
ID: 39268439
This package runs in SSIS but not under sql agent.I have created system DSN for ODBC Driver and data is read in ssis under this dsn.But unable to solve this issue in sql job agents--error still showing--Data source name not found and no default driver specified     at System.Data.Odbc.OdbcConnection.HandleError.I tried using proxy and didn't work but i dont think proxy is an issue as I have a priviledge of sysadmin.

Expert please help me.Thanks in advance.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39268461
>Data source name not found and no default driver specified  
One of your connections was not successful.... perhaps c_d_billingchute ADO NET Source [1]
0
 

Author Comment

by:Josh2442
ID: 39268527
I checked the connection,it was good.Ran the whole package,it ran successfully.But it still says data source not found and no default driver specified at system...
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39268698
A couple of things:
1.  When you run SSIS it is running under your security context and on you specific workstation.
2.  When you run SSIS under SQL Server agent it is running under the security context of the startup account of the SQL Server Agent and it is running on the same box as SQL Server.

So you need to tell us:
1.  What is the account used by the SQL Server Agent service?
2,  Does you SSIS package use mapped drives?
3.  Is the ODBC connection to MS Access?
4.  What processor is running on the server?  32 or 64 bit?
0
 

Author Comment

by:Josh2442
ID: 39270092
@acperkins,

1)I am running the package as SQL Server agent service account as I have a priviledge of sysadmin.
2)SSIS Package does not use mapped drive but I deploy it using manifest file and also package is available when browsing through the job steps.
3)ODBC Connection to the sybase
4)32 bit as I used the execution option as 32 bit.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39270199
1)I am running the package as SQL Server agent service account as I have a priviledge of sysadmin.
No, you are not running it.  SQL Server Agent is running SSIS or more precisely the startup account for the SQL Server Agent service is executing the SSIS package.
3)ODBC Connection to the sybase
Does the account used by the SQL Server Agent have access to the Sybase database.
0
 

Author Comment

by:Josh2442
ID: 39270252
Ok.I created another job just to see if it fails or not,tried for oledb database,it works,,tried for different server which is connected via odbc connection and this odbc connection does not need password while setting up in the ssis,this works,and for sybase I have to connected through the odbc driver via username and password.So I think this is the issue,and there is no option where I could save the password,I tried setting up the job just for simple task for sybase database and the job failed,saying Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).Experts help me in setting up the connection from this sql server agent.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39273503
Experts help me in setting up the connection from this sql server agent.
I tried to explain it to you, sorry you could not figure it out.

Good luck.
0
 

Author Comment

by:Josh2442
ID: 39274649
@Acperkins,

Sorry.I  don't mean that.
What is the account used by the SQL Server Agent service?
I tried using SQL SERVER Agent account as well as proxy.The account used by sql serevr agent account is localsystem
2)ODBC Connection to the sybase
the account used by the SQL Server Agent has no access to the Sybase database and it is set up using the id sa and password.
How do I make sql server agent agent access to sybase.

I really need a help on this piece,please.
0
 

Author Comment

by:Josh2442
ID: 39275636
I've requested that this question be closed as follows:

Accepted answer: 250 points for acperkins's comment #a39273503
Assisted answer: 0 points for Josh2442's comment #a39268439
Assisted answer: 0 points for Josh2442's comment #a39268527
Assisted answer: 250 points for acperkins's comment #a39268698

for the following reason:

This is due to system dsn.
0
 

Author Comment

by:Josh2442
ID: 39275603
Thank you
0
 

Author Comment

by:Josh2442
ID: 39275614
This is fixed and am closing now.
0
 

Author Comment

by:Josh2442
ID: 39275617
Closing
0
 

Author Comment

by:Josh2442
ID: 39275637
Thanks guys
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39276730
Psst.  This is the way you close a question:
How do I accept multiple comments as my solution?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39322544
I am not sure if you have noticed, but the author has already closed this question some days ago.  Do you still want our recommendation?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39335135
Yes, I'm still looking for Expert recommendations.
I am OK with the Accepted Solution selected by the author.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now