Link to home
Start Free TrialLog in
Avatar of Josh2442
Josh2442

asked on

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
Avatar of Josh2442
Josh2442

ASKER

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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
>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]
Avatar of Josh2442
Josh2442

ASKER

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...
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Josh2442
Josh2442

ASKER

@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.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
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.
Avatar of Josh2442
Josh2442

ASKER

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
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
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.
Avatar of Josh2442
Josh2442

ASKER

@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.
Avatar of Josh2442
Josh2442

ASKER

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.
Avatar of Josh2442
Josh2442

ASKER

Thank you
Avatar of Josh2442
Josh2442

ASKER

This is fixed and am closing now.
Avatar of Josh2442
Josh2442

ASKER

Closing
Avatar of Josh2442
Josh2442

ASKER

Thanks guys
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Psst.  This is the way you close a question:
How do I accept multiple comments as my solution?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
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?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Yes, I'm still looking for Expert recommendations.
I am OK with the Accepted Solution selected by the author.