To run a SSIS package outside of SQL Server Data T

Question

I am testing SSIS package that I upgraded from VS2005 project to VS2013 (package deployment model) project. This is very simple package which just processes files one by one from specific location and updates database based on those files and once done it moves file to archive or non-parsing directory based on outcome. And I am getting odd error and I cant find solution for that.

Issue is my package runs fine and it does exactly what it suppose to do (extracting data from file and inserting into table). But part of this operation is to move file to archive (file system task). And I am getting this error "Description: To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher."

I have used VS2013 with SSDT BI for 2014 and SSIS Designer version matches with dtexec utility in my dev test VM (12.0.2000.8 x64 bit). Below is output from CLI.

Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.

Started: 10:45:58 AM Progress: 2016-02-11 10:45:59.20 Source: Truncate StagingTable Executing query "TRUNCATE TABLE StagingTable".: 100% complete End Progress Progress: 2016-02-11 10:45:59.25 Source: Insert into Staging Executing query "exec dbo.staging @xml_fileName,NULL,'C...".: 100 % complete End Progress Progress: 2016-02-11 10:45:59.25 Source: Move to MasterXML Executing query "exec dbo.insertXML".: 100% complete End Progress Error: 2016-02-11 10:45:59.25 Code: 0xC000F427 Source: Move File to Archive Description: To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher. End Error Progress: 2016-02-11 10:45:59.30 Source: Execute SQL get_next_file Executing query "exec get_next_file".: 100% complete End Progress Progress: 2016-02-11 10:45:59.51 Source: Execute SQL notify_users Executing query "exec notify_users".: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:45:58 AM Finished: 10:45:59 AM Elapsed: 1.172 seconds

In latest effort, I installed VS2013 and SSDT BI for SQL Server 2014 in same machine where I am trying to run this package. And if I use VS, package runs fine but as soon as I try to run this package over CLI with following command it still fails with same message,

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtexec.exe" /f "C:\SSIS\Load_Files.dtsx" /ConfigFile "C:\SSIS\loadFiles_SSIS_Configuration.dtsconfig"

I am sure it has something to do about how SSIS works in VS2005 to VS2013 but just don't know where to look. Any suggestions ?

Answer 1

Just to give future visitors something to look on, in my case issue was I had two different version of SSIS installed on same machine. One was for 2005 and another for 2014. And oddly enough even I was explicitly pointing to newer version (as shown in later part of post) in my command, it was always using old version.

Once I had clean system with just SQL Server 2014 (& SSIS) same package ran without any issue. So it appears a limitation of some sort which doesn't allow to run two different versions of SSIS on same machine.

Answer 2

You need to install SQL Server and make sure to select Integration Services. Then run DTExec.exe from the new SQL server installation folder.

In my case I installed SQL server 2016 Standard Edition, and DTExec.exe was in the following location:

C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe

So your new command file (for SQL Server 2016) would look like this:

"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtexec.exe" /f "C:\SSIS\Load_Files.dtsx" /ConfigFile "C:\SSIS\loadFiles_SSIS_Configuration.dtsconfig