June 6, 2008 at 2:04 pm
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
June 8, 2008 at 8:27 am
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
June 9, 2008 at 3:09 pm
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.
June 10, 2008 at 12:15 am
Then once more my question to you is what is telling SSIS where to go and find those configurations?
June 10, 2008 at 8:36 am
Enviroment variable , pointing to MSDB database and table [ssis configurations]. It is able to reads values from configuration table.
June 10, 2008 at 9:04 am
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
June 10, 2008 at 10:31 am
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 .
June 11, 2008 at 8:34 am
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.
June 11, 2008 at 11:10 am
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 .
June 10, 2009 at 6:35 am
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 .
:unsure:
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply