Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package is scheduledas SQL agent job but while connecting todata sources, it is usingsql agent...


SSIS package is scheduledas SQL agent job but while connecting todata sources, it is usingsql agent account to connect , not the connections defined in...

Author
Message
kittu123
kittu123
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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



pduplessis-723389
pduplessis-723389
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 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
kittu123
kittu123
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.



pduplessis-723389
pduplessis-723389
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 400
Then once more my question to you is what is telling SSIS where to go and find those configurations?
kittu123
kittu123
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 66
Enviroment variable , pointing to MSDB database and table [ssis configurations]. It is able to reads values from configuration table.



pduplessis-723389
pduplessis-723389
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 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
kittu123
kittu123
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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 .



Ed Zann
Ed Zann
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 1391
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.



kittu123
kittu123
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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 .



ALZDBA
ALZDBA
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10093 Visits: 8867
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search