Not able to execute SSIS package from batch file

Question

Hi There,

I am trying to automate SSIS package execution using Windows Task scheduler and batch file(Since I don't have permissions to use SQL server agent).

In the SSIS package I am using two connections, one for Source server connection and other for target server.

In the batch file I am using below code:

@ECHO OFF
CLS
ECHO You are about to execute the TestPackage SSIS package
PAUSE
"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /F "C:\Users\msmanju99\OD\Migrated_Home_Drive\Visual Studio 2015\Projects\Test_project\Package2.dtsx"
PAUSE

When I run the batch file, I am getting the error "the runtime connection manager with the id cannot be found" for both the connections. Please let me know what I am missing here.

Thanks

Manju

Answer 1

Hi Manju,

1.Please check if the connection managers exist in your ssis package.

2.May I know if you create config file to change the connection string or other properties?

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 2

Is that the entire error message?

Are you running this batch file on different system from your development machine?

---

If my reply solves your issue, please mark it as the answer

Answer 3

Hi Mona,

1. There are two connection managers one for source and another for destination. Both connections are for two different SQL servers.

2. I have not created any Config files or XML file for connection string. Both connections are project level connections and they are parameterized.

I have attached screenshot of the error, I get this when I run the batch file 

Thanks

Manju

Answer 4

Hi Jim,

I am running the batch file on the same machine where Visual Studio(SSIS) is installed.

The SSIS package executes without any errors on Visual Studio, but when I'm trying to call the same package from batch file I am getting this error.

In the SSIS package, it is simple data flow task with OLEDB source and OLEDB destination, it is straight load from table to table, where source and destination are different SQL servers

Thanks

Manju

Answer 5

Hi,

I just found something, my connection are project level connections.

If I change the project level to package level by "Convert to package connection" then the batch file is executing without errors.

Now the problem is I have 22 package, and each package consists of at least 5 to 6 control flows. In that case should I change my connection to package level, that is for each control flow I have to change the connection??

Thanks

Manju

Answer 6

Hi Manju,

Please create the batch file like the following codes shown if you want to execute one package

with project connection manager.

@ECHO OFF
CLS
ECHO You are about to execute the TestPackage SSIS package
PAUSE
"C:\Program Files\Microsoft SQL Server\140\DTS\Binn\DTEXEC.exe" /Package Package.dtsx /Project "C:\Mona\SSIS\2020\pro\Integration Services Project8\bin\Development\Integration Services Project8.ispac"
PAUSE

We can test the command in CMD firstly.

dtexec /Package Package.dtsx /Project "C:\Mona\SSIS\2020\pro\Integration Services Project8\bin\Development\Integration Services Project8.ispac"

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 7

Hi Mona,

The code you provided for the batch file WORKS with project level connections. Thumbs up!!

Thank You!!

Regards

Manju