SolvedPrivate

ssis job scheduling issue

Posted on 2013-06-22
22
36 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a …
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

16 Experts available now in Live!

Get 1:1 Help Now