Sql-server – ‘Connection not found’ error thrown at end of task, after the package has successfully used the connection it refers to as missing

sql serverssisssis-2012visual studio 2012

Sorry for the long title and this long question!

I'll set the scene. I'm fairly new to VS and SSIS.

I have created several SSIS packages, basically a package for retrieving data and creating a csv file, getCSV.dtsx, and another to Load said csv file into our Oracle tables, loadData.dtsx.

Now that they are working independently, I have started to put them together in one 'main' package getandLoad_AllTables.dtsx consisting of a sequence container for each table, 2 packages per container; getData/loadData using 'Execute Package Tasks' to call these packages from the local file system in the order I have laid out in my control flow.

The issue – both the tasks are called, run and completed within the first container however the getandLoad_AllTables package errors out, detailing that

Error: 0xC001000E at loadData: The connection "{long-ass string}" is not found"

I checked the ID of the connection in the XML of loadData.dtsx and found it is the main connection used within all the 'Execute SQL Tasks' in the loadData package, all of which ran successfully and loaded the data into the tables as expected.

Why is this error thrown in the getandLoad_AllTables package and not in the loadData package it says the connection is missing from? I am completely stumped.

Thank you in advance for any help/guidance/advice anyone can give.

Best Answer

I figured this out and documenting here in case anyone comes across this question with the same issue.

Within the Execute Package Task Editor I had chosen the Reference Type as External Reference and was linking to the .dtsx file within the file system. It seems that this option, when run directly from Visual Studio at least, wasn't cleaning up properly.

To fix, I changed the Reference Type to Project Reference and choose the required package from the list of package within the project. This ran 100%.

I have gone as far as setting this up on management studio, but to run from within Visual Studio, this fixed the issue.

Related Question