July 20, 2012 at 4:56 am
Not really sure why DTEXEC gives a syntax violation on /Connection.
Alternative: don't select the data sources in the SQL Agent jobstep and use package configurations instead.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 5:16 am
Koen Verbeeck (7/20/2012)
Not really sure why DTEXEC gives a syntax violation on /Connection.Alternative: don't select the data sources in the SQL Agent jobstep and use package configurations instead.
ok Koen, Let me try with Config files, and i will let you know the status...
Thanks,
Charmer
July 20, 2012 at 5:41 am
I had the same problem with packages that use an ADO.NET connection to an Ingres server. The connection login for Ingres is different to my local SQL server login. The Ingres login/password is persisted in the connection manager and the packages work fine if called by DTEXEC but fails with connection problems if run through JobAgent. The login / password is not retained and it passes the login the job is being run under so there is a login / password mismatch. Setting up the same login in Ingres is a non-starter so I had to find another way of running the packages. What I did was create a cmd file that calls DTEXEC and run this cmd file via Task Scheduler. The packages are running without any login issues.
July 20, 2012 at 6:27 am
jim.david (7/20/2012)
I had the same problem with packages that use an ADO.NET connection to an Ingres server. The connection login for Ingres is different to my local SQL server login. The Ingres login/password is persisted in the connection manager and the packages work fine if called by DTEXEC but fails with connection problems if run through JobAgent. The login / password is not retained and it passes the login the job is being run under so there is a login / password mismatch. Setting up the same login in Ingres is a non-starter so I had to find another way of running the packages. What I did was create a cmd file that calls DTEXEC and run this cmd file via Task Scheduler. The packages are running without any login issues.
Jim, Could you guide me to create a cmd file and some steps through this process?
Thanks,
Charmer
July 20, 2012 at 6:34 am
Charmer
The contents of the cmd file are fairly simple
dtexec /sql "Dataload" >i:\xx\xxx\dataload\logfiles\dataload.log
call e:\\dba\common\BATCHscripts\datestamp_file i:\xx\xxx\dataload\logfiles\dataload.log
The package Dataload is run from the server dataload.log is to capture the output from the run of the package. The last bit is just to datestamp the dataload.log file so we can keep a history of the runs.
Jim
July 20, 2012 at 7:24 am
jim.david (7/20/2012)
CharmerThe contents of the cmd file are fairly simple
dtexec /sql "Dataload" >i:\xx\xxx\dataload\logfiles\dataload.log
call e:\\dba\common\BATCHscripts\datestamp_file i:\xx\xxx\dataload\logfiles\dataload.log
The package Dataload is run from the server dataload.log is to capture the output from the run of the package. The last bit is just to datestamp the dataload.log file so we can keep a history of the runs.
Jim
Jim, I have a doubt... I am not able to run the Package myself on the production server..If i try that, it gives error...then how come this cmd file option will get worked? I am just curious...
Thanks,
Charmer
July 20, 2012 at 7:31 am
Jim, I created a cmd file calling the package....but where can i call this cmd file? i am not getting about the task scheduler..Could you please tell me?
Thanks,
Charmer
July 20, 2012 at 7:47 am
The packages mainly read data from SQL Server and copies it to Ingres. The login has permissions to select this data and run the cmd file so it uses windows authentication. My login is setup as an administrator and I use the Administrator Tools on the server to set up the task entry. We are using Windows 2008 R2 Standard systems
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply