Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package is scheduledas SQL agent job but while connecting todata sources, it is usingsql agent account to connect , not the connections defined in... Expand / Collapse
Author
Message
Posted Friday, June 6, 2008 2:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 2:15 PM
Points: 8, Visits: 66
Created a package with four connections strings ( two on local sql & two side of the network ) and stored all connections using environment variable and in sql server [SSIS configurations]

Deployed the package successfully & able to run manually using dtexecui

the scheduled job is failing while connecting to source/destination out my network because it is using sql server agent account credentials

This is suppose to use connection managers defined in the package



Post #513206
Posted Sunday, June 8, 2008 8:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
hey,

I am misunderstanding your posting slightly...

Assuming you have created an environment variable which tells SSIS where your package configurations reside, and assuming that within these package configurations (if I am correct they reside in SQL) you have specified your connection strings, your package should not be failing.

What authentication are you using when connecting to your OLEDB sources (windows or SQL?). If it is SQL, you will have to make sure that you specify the userid and password as part of your connectionstring, if it is windows authentication, there may be problems with your SQL server agent account (rights).

~PD
Post #513443
Posted Monday, June 9, 2008 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 2:15 PM
Points: 8, Visits: 66
Thanks for your response & sorry for the confusion.

Your are correct I am using sql server to store configuration info. All the connections are using sql user & password. I have four connections , two connections to sql server 2005 (64 bit) located in our network and one is out side of our network and another email - sntp connection. Deployed the package on production SSIS server & ran successfully from DTEXECUI where as failing when I ran as sql job or ran from the query window .
xp_cmdshell 'dtexec /DTS "\File System\SOD - XXXXXXX package " /SERVER "XXXX01" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V'


failing to connect to OLDDB source which is sql server 2000 , sp3 (32 bit) where as all other connections are sql server 2005 sp2 ( 64 bit)

My question is : it running fine if i ran from DTEXECUI.

Any help is really appreciated


Error is :

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source - SQL server2000" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

component "OLE DB Source" (570) failed validation and returned error code 0xC020801C.

One or more component failed validation.

There were errors during task validation.



Post #514045
Posted Tuesday, June 10, 2008 12:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Then once more my question to you is what is telling SSIS where to go and find those configurations?
Post #514169
Posted Tuesday, June 10, 2008 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 2:15 PM
Points: 8, Visits: 66
Enviroment variable , pointing to MSDB database and table [ssis configurations]. It is able to reads values from configuration table.


Post #514490
Posted Tuesday, June 10, 2008 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Strange....

I use an environment variable per environment (dev, preprod, prod), all telling SSIS where the package configurations are stored.
I elaborated slightly, and have 4 different tables(FTP, Database, File, SMTP). In the case of database, I have my connectionstrings, which SSIS reads and is able to use to connect.

Care to post one of the connectionstrings?
Mine looks like following:
Data Source=YourServerName;Initial Catalog=YourDBName;User ID=YourUserID;password=YourPassword;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False

Works perfect

~PD
Post #514522
Posted Tuesday, June 10, 2008 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 2:15 PM
Points: 8, Visits: 66
Yes it is Strange.

My connection string also in same format.

It works fine if I run from dtexecUI where as it fails if run from cmd or as sql agent job or query window.

if some thing wrong with connection string it should fail when I ran from DTEXECUI .




Post #514622
Posted Wednesday, June 11, 2008 8:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
Don't know if this is what's going on - but it's something to check....
There are special security considerations when using xp_cmdshell. You can review these in BOL.



Post #515222
Posted Wednesday, June 11, 2008 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 2:15 PM
Points: 8, Visits: 66
Thanks for all the suggestions. Finally I find the reason for failing. It is alias issue on X64 bit connecting to 32 bit.

Initially I created on only Alias under SQL Native Client Configuration(32 bit) . Because of this SSIS package is running fine with DTEXECUI .

Where as sql job or XP_cmdshell looks for alias under sql native client for 64 bit.
after adding alias under 64 bit, SSIS package is running fine on both DTEXECUI & dtexec and in query window also.

I fell main reason is name resolution issue: some components looks for alias under 32 bit & few looks under 64 bit. Adding alias under both 32 & 64 bit resolved this problem .



Post #515380
Posted Wednesday, June 10, 2009 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
kittu123 (6/11/2008)
Thanks for all the suggestions. Finally I find the reason for failing. It is alias issue on X64 bit connecting to 32 bit.

Initially I created on only Alias under SQL Native Client Configuration(32 bit) . Because of this SSIS package is running fine with DTEXECUI .

Where as sql job or XP_cmdshell looks for alias under sql native client for 64 bit.
after adding alias under 64 bit, SSIS package is running fine on both DTEXECUI & dtexec and in query window also.

I fell main reason is name resolution issue: some components looks for alias under 32 bit & few looks under 64 bit. Adding alias under both 32 & 64 bit resolved this problem .



What kind of alias are you refering to ?

Can you post the statement you executed to create this alias ?

I'm having issues using ssis to export data to a local msaccess.mdb on x64.
(only sqlserver is installed on this server)


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #732183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse