DTExec: The package execution returned DTSER_FAILURE (1) in SSIS package execution

Question

I've got this question within a project in ssis. I had to divide the original project into different packages because this was to big and sometimes it could provoke some problems with the memory. So, in order to link the different packages I'm using the "Execute Package Task" to refer one to each other. If I execute the package directly from SSIS it works perfect, there's not problem. But if I use a scheduler to program the time of execution I'm getting this error message:

Error: 2015-09-22 14:54:37.98 Code: 0xC00220E6 Source: Execute Package Task Description: There is no project to reference. End Error Error: 2015-09-22 14:54:37.99 Code: 0xC0024107 Source: Execute Package Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1).

I wonder what can be happening with the project and its execution.

Regards

Solution

I'm not sure what scheduler you are using, or how it's configured, but you can debug your way through this by simulating the scheduler with DTExec. It sounds like you've crossed your Setup and Execution Method (see below for those definitions).

Here's the summary.

1. Option 1: For the Setup, use Project References and for the Execution Method, use Project/Package.
2. Option 2: For the Setup, for each child package, use External References and for Execution Method, use File.

(It sounds like you're using a combination of Project References and File, which in turn sends the There is no project to reference error off of the child packages.)

Option 1

Setup

Open your parent package in SSDT, and then double click a child package. It should look like this:

Execution Method

This setting means you need to execute the package via the Project/Package method via DTExec. So build your project - this generates an ispac file. And to execute via dtexec, it would look like:

dtexec /Proj Path\To\MyProject.ispac /Pack Path\To\The\ParentPackage.dtsx

Note: If you specify the dtsproj file instead of the ispac file in the /Proj parameter, you will receive a File contains corrupted data error!

Option 2

Setup

Open your parent package in SSDT, and then double click a child package. Change it to look like this:

This is done by
1. Changing the Reference Type to External Reference
2. Changing the Location to File system (SQL Server is another option)
3. Select <New connection...> to create a new file connection for the child package to run (or a SQL server connection)

Execution Method

This setting means you can now use the File method, which is likely the way you're attempting to execute the package.

dtexec /f Path\To\My\ParentPackage.dtsx