Automation of SSIS package developed in Visual Studio 2015

0
votes

Can someone please explain the best way to automate a SSIS package that was built in Visual Studio 2015 without using the SQL Agent? Preferably in Task Scheduler. I have tried using the older technique of dtexec but I kept receiving a version error message. I then tried running dtexec as a batch file and it could not locate the dtexec file. Any help would be appreciated.

Here is the script I tried using:

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec /f "C:\Users\amoberg\Documents\Scripts\DailyCancel\DailyCancel\Package.dtsx"

Thank you

UPDATE: Here is the CMD error:

C:>dtexec /F "C:\Scripts\DailyCancel\DailyCancel\Package.dtsx" Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved.

Started: 12:42:33 PM Error: 2017-01-05 12:42:33.92 Code: 0xC001700A Source: Package Description: The version number in the package is not valid. The version number cannot be greater than current version number. End Error Error: 2017-01-05 12:42:33.93
Code: 0xC0016020 Source: Package Description: Package migration from version 8 to version 6 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.". End Error Error: 2017-01-05 12:42:33.93 Code: 0xC0010018 Source: Package Description: Error loading value "8" from node "DTS:Property". End Error Could not load package "C:\Scripts\DailyCancel\DailyCancel\Package.dtsx" because of error 0xC0010014. Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. Source: Package Started: 12:42:33 PM Finished: 12:42:33 PM Elapsed: 0.094 seconds

1
sql-serverbatch-filevisual-studio-2015ssis
You have spaces in the path... try using quotes like this "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec" – RGuggisberg
Is SSIS installed and active? If so, what SQL Server version is installed? – Laughing Vergil
Yes, SSIS is installed and active. Using SQL Server version 2016. It runs fine within Visual Studio 2015, but when I try automating it I run into issues. I also tried what RGuggisberg suggested but it still did not work... – AndrewMo
I would try powershell to execute a t-sql statement, that is if you are at least publishing to the ssis catalog. – Joe C

1 Answers

0
votes

You are trying to execute a package from SSIS 2014 or higher with a dtexec from SSIS 2012. The error posted is saying that package version is newer (higher) that SSIS server version. Check yourself with exact path and version number; presumably, you have both SSIS 2012 and 2014+ on your server.
To fix this issue, select path of C:\Program Files\Microsoft SQL Server\ <your version of SQL and SSIS> \DTS\Binn, run dtexec with your parameters. This should execute your package.