Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 DTEXEC /FILE on clustered server

Author  Topic 

DaveChapman
Starting Member

7 Posts

Posted - 2009-05-14 : 05:59:35
I have an SSIS package created in BIDS.

It needs to be executed from a SQL Server Agent Job using "Operating System (CmdExec)" because it's got a 32bit Excel import on the 64bit OS and therefore needs to run teh 32bit DTEXEC.

In the command line I pass in the path to the dtsx and dtsConfig files:
<path to (x86) folder>DTEXEC.EXE /FILE "g:\rbb\SSIS\RBBImport.dtsx" /DECRYPT 
rbbpassword /CONFIGFILE "g:\rbb\SSIS\RBBConf.dtsConfig"
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF


The job appears to be correctly picking up the config file as it starts validating the Excel spreadsheet, but then fails with a warning about not picking up a configuration file or value. I've tested by putting an incorrect config file name in and get a different error with no validation.

I can run the same job on a single SQL server and there is no problem, it runs fine.
Is there anything I need to do differently on a clustered server?

Thanks for any help
Dave

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-05-14 : 16:46:59
No, nothing different required on a cluster.

What is the error - not the warning ?
What is the g: drive? Does the SQL Agent service account have access to this area?

Is the above command in the SQL Agent job step? Or in a batch file?
Go to Top of Page

DaveChapman
Starting Member

7 Posts

Posted - 2009-05-15 : 04:12:38
I finally tracked it down...it appears that using a bulk import with SQl Server Destination doesn't work correctly on a clustered server farm - the error, apparently, is due to the fact the job HAS to run on the same SQL instance that the bulk import takes place on.

Solution is simple:
Change the destination to OLE DB Destination and everything ran first time.

Nice one to be aware of though.
Go to Top of Page
   

- Advertisement -