Can't run SSIS package from a batch file

Question

I am trying to run an SSIS package from a batch file with this code:

echo
d:
cd \DTSPacks
dtsrun /MyServer /E /N Eval_Emp_Info.dtsx
pause
cd\
echo off

I get this error:


Error:  -2147467259 (80004005); Provider Error:  17 (11)
   Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exi
st or access denied.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context:  0

Any help is appreciated.  I'm a rookie at this.

Thanks,

Steve

---

Steve

Answer 1

Is the command line for dtsrun correct? I am not seeing /S parameter which specifies the server name. Are you able to connect to MyServer from osql (or) Enterprise Manager etc; ? Are you seeing the same error message?

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

after reading your error message, I assume that your problem is in connection to the db.

some link about this error message

http://support.microsoft.com/default.aspx/kb/328306
http://www.experts-exchange.com/Databases/Q_21670333.html
http://geekswithblogs.net/azamsharp/archive/2005/07/27/48416.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/12/19/505372.aspx

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 3

The "/MyServer" is a generic name I put in for the post.  The file contains the actual server name that is running SQL Server.  The batch file is run locally.

When the SSIS package is run by itself, there are no connection problems and the package completes successfully.  Enterprise Manager connects with no problems.  Do I have to put a connection string in the batch file?  If so, where can I find a connection string template to follow?  As stated before, I'm a rookie.

Thanks.

---

Steve

Answer 4

Shouldn't the server be specified as [dtsrun /S MyServer /E /N Eval_Emp_Info.dtsx] as per dtsrun utility help on msdn (http://msdn.microsoft.com/en-us/library/aa224467(SQL.80).aspx)? 

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 5

Isn't dtexec the command line tool for SSIS?

Answer 6

Shouldn't the server be specified as [dtsrun /S MyServer /E /N Eval_Emp_Info.dtsx] as per dtsrun utility help on msdn (http://msdn.microsoft.com/en-us/library/aa224467(SQL.80).aspx)? 

---

This posting is provided "AS IS" with no warranties, and confers no rights

Raj you seem to be referring to SQL 2000 and executing DTS packages rather than SSIS packages

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 7

Based on you batch script, you store your packages in the file system rather than is SQL server, is this correct? If so you don't need to specify any server information on the command line at all, just provide the name of the dtsx file of the package. I'm assuming this becuase you are navigating to the DTSPacks directory on your D drive. If you want your package to execute against a different server then you should do this through package configurations or using the SET command line option to set the connection manager properties.

The easiest way is to double click you dtsx file which will launch dtexecui.exe (the UI for executing packages), set your options using this UI and then look at the command line tab which generates the command line for you. You can copy paste this into a batch file and parametrize to your needs.

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 8

Adam,

I tried you suggestion.  Here is what I copied and pasted.

echo
d:
cd \DTSPacks
dtsrun  /FILE "D:\DTSPacks\Eval_Emp_Info.dtsx" /DECRYPT /CONNECTION DestinationConnectionOLEDB;"\"Data Source=MyServer;Initial Catalog=Info;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;\"" /CONNECTION SourceConnectionOLEDB;"\"Data Source=QMCLEAN;User ID=user;Provider=IBMDA400.DataSource.1;Persist Security Info=True;Default Collection=HRCUSTOM;\""  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI  
pause
cd\
echo off

I got a slew of errors, the first saying the parameter was incorrect.  My batch file coding knowledge is awful.  correct this one if you can.  If you or someone else has a working example of running SSIS from a batch file, it would help for me to see one.  I get lost on the parameters.

Thanks

---

Steve

Answer 9

It would be helpful if you provided the slew of error that you got to get an idea what is wrong.

It looks like you used the connection managers tab to define the connections. I would suggest creating a XML package configuration file. In Visual Studio package editor, right-click an empty region and select package configurations. Follow the dialogs to sleect an XML config file and in the configuration dialog navigate to the connection managetr properties you wish edit and check the box next to those properties. VS will generate the file for you. You can then create multiple copies of this file, edit the values and specify it as a parameter on the command line. If you don't want to do this, then I suggest creating the config file anyway as it will give you property path syntax that you need to use on the "Set Values" tab of dtexecui that are then generated as /SET options on the commnad line. 

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)