ssis job scheduling issue

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
Josh2442Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Josh2442Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Josh2442Author Commented:
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
 
Josh2442Author Commented:
@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
 
Anthony PerkinsCommented:
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
 
Josh2442Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Josh2442Author Commented:
@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
 
Josh2442Author Commented:
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
 
Josh2442Author Commented:
Thank you
0
 
Josh2442Author Commented:
This is fixed and am closing now.
0
 
Josh2442Author Commented:
Closing
0
 
Josh2442Author Commented:
Thanks guys
0
 
Anthony PerkinsCommented:
Psst.  This is the way you close a question:
How do I accept multiple comments as my solution?
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Yes, I'm still looking for Expert recommendations.
I am OK with the Accepted Solution selected by the author.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.