SSIS Connection not found in package

问题:

I'm kind of new to SSIS programming, and I'm having some problems deploying an SSIS package.

This package runs correctly on my PC, does everything it needs to do ... but when I deploy it cannot find the connection strings.

Here is the error:

Code: 0xC001000E Source: Description: The connection "{DA7CD38D-F6AA-4B06-8014-58BEE5684364}" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error

Error: 2012-08-09 00:21:06.25 Code: 0xC001000E Source: Package Description: The connection "{DA7CD38D-F6AA-4B06-8014-58BEE5684364}" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error

Error: 2012-08-09 00:21:06.25 Code: 0xC001000E Source: Package Description: The connection "{DA7CD38D-F6AA-4B06-8014-58BEE5684364}" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error

Error: 2012-08-09 00:21:06.25 Code: 0xC00291EB Source: Execute SQL Task Execute SQL Task Description: Connection manager "{DA7CD38D-F6AA-4B06-8014-58BEE5684364}" does not exist. End Error

Error: 2012-08-09 00:21:06.25 Code: 0xC0024107 Source: Execute SQL Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 00:21:04 Finished: 00:21:06 Elapsed: 1.888 seconds. The package execution failed. The step failed.

回答1:

It seems that your ssis package is pointing to some other connection which might have been deleted or renamed .Try opening the SSIS compoenents and point to the correct connection which are there in your connection manager .

It happens when we copy the SSIS package components to create a new package or because of renaming the connections or there may be still components which are using the old connection defined in you xml config file( In your case try checking the Execute SQL Task which is throwing error ) .If you are using XML for configuration try deploying the new one.

回答2:

I'm a little late to the party, but I ran across this thread while experiencing the same errors and found a different resolution.

When creating an SSIS 2012 package, in the Solution Explorer window you will see the Connection Managers folder at the project level. This seems like the most logical place to create a connection, and should be used when creating a connection that can be used by any package in the project. It is included at the project level, and not the package level.

When running my dtsx package using dtexec, I received the same errors shown above. This is because the connection is not included in the package (just the project). My solution was to go into the package designer, and then in the Connection Manager window, right click the project level connection (which is shown using the "(project)" prefix) and choose "Convert to Package Connection". This will embed the connection in the actual dtsx package. This alleviated my problem.

回答3:

This seems to also happen when you use the new SSIS 2012 "Shared Connection Manager" concept where the connection managers are not defined within your package but the Visual Studio project and just get referenced in the package. Executing it via SQL Agent or DTEXEC yields the same error message.

I haven't found a solution for that yet but would love to get some feedback if anybody experienced it before.

回答4:

Thank you for posting this issue.

One resolution: open the package in XML form through windows explorer, locate the GUID for the connection manager that cant be found. In my case, it was a bonked EventHandler connection that was corrupted. This same connection manager was used in the control flow but somehow was not corrupted there, so it was not obvious to the user via the UI. Since the XML pointed to an event handler connection manager, I opened the event handler tab in the UI and it immediately displayed the wonderful RED X on the source and targets that were referencing the corrupted connection manager ID. I repointed it to the correct manager, rebuilt the pkg and saved. Good to go.

The key was opening the pkg in XML format and locating the GUID in the code to see where it was failing. If I was not able to find a valid reference to it in the UI, I was going to either rename the XML connection to another known GUID within the XML and then go into the UI and repoint it again, or delete it altogether.

Good luck.

回答5:

In my case i found out that the problem was a Log Provider previously configured point to an old connection not used anymore. To solve the problem click the Package Explorer Tab then click Log Providers and delete the outdated Log Provider. I hope this helps someone.

回答6:

The previous remarks about deleting or removing a connection are absolutely a possibility. But you can also get this error when you attempt to invoke a package that uses project level connections (instead of package level connections).

If you are using project level connections and still want to use dtexec, never fear there is a way. I would not recommend converting them to package level connections (assuming you created them as project level connections for a good reason).

You will need to deploy your SSIS project. Your SSIS server will need to have a catalog created (https://msdn.microsoft.com/en-us/library/gg471509.aspx). Once you have the catalog, in your SSIS project select Project->Deploy and follow the wizard. The result will be a *.ispac file generated in your SSIS solution folder/bin/Development

Now for the money command, instead of invoking your package with a simple: dtexec.exe /f "package.dtsx"

instead call it this way: dtexec.exe /project "<...>/project.ispac" /package "<...>/package.dtsx"

The ispac file has the project level connection info that is needed to execute your package and you should be set!

回答7:

What i did to solve this problem was simple. I had to rename my SQL Server so that it would respond to the (localhos) tag. After that i changed all the connections on the SSIS and i rebuild the solution...it worked. hope it helps you

回答8:

Package works fine on Friday, check in to TFS and go home. Open it on Monday, getting errors everywhere. "connection manager variable $project._connectionstring was not found in the variables collection".

I rtclick-edit the connection and test connectivity, works no prob;em. The ConnMnger is in the Connection Managers list in the solution. When open the TARGET object connected to this connection manager, and click on MAPPINGS, the error above pops up. There is no reference to connection manager variables anywhere in the mapping.

Turns out, to correct this you must right click on the connection manager in the Connection Manager window and choose PARAMETERIZE. Fill in the options as necessar -

PROPERTY: ConnectionString Use Exisgint Parameter: $Project::ConnMgrName_ConnectionString OR Create New PArameter: follow the options

Once this connection manager is parameterized, everything works. Even though the Conn Manger existed in the Conn Manger tab, the Conn Mgr is already listed in the Solution Explorer AND worked no problem 2 days prior.

Odd. Whatever. Microsoft being Microsoft. SQL Server being SQL Server. Choose your poison.

Hope this helps the next person save some time.

回答9:

i had the same issue and niether of the above resoved it. It turns out there was an old sql task that was disabled on the bottom right corner of my ssis that i really had to look for to find. Once i deleted this all was well

回答10:

In my case, one of the event handler task was pointing to old connection which was deleted, deleting the unused event handler task fixed the problem. I end up opening the package in XML format to understand that the problem is with event handler task!

回答11:

In my case, I could solve this in an easier way. I opened the x.dtsConfig archive, and for an unknown reason this archive was not in the standard format, so ssis could not recognize the configurations. Fortunately, I had backed up the archive previously, so I just had to copy it to the original folder, and everything was working again.

回答12:

I received this error while attempting to open an SSDT 2010/SSIS 2012 project in VS with SSDT 2013. When it opened the project, it asked to migrate all the packages. When I allowed it to proceed, every package failed with this error and others. I found that bypassing the conversion and just opening each package individually, the package is upgraded upon opening, and it converted fine and successfully ran.

回答13:

I had same issue in my case, the cause was connection was not embedded and incompatibility of Oracle Client.

SOLUTION:

My Environment:SQL SERVER 2014 64bit Oracle Client 32 bit

1. For include/embed Connection

   • open package
   • right click on connection
   • select "Convert to project" option

2. for SQL SSIS Catalog/Job schedule set the configuration follow steps in picture

   • Right on 'SQL JOB->Step" or "SSIS Catalog-->Package-->Execute" and select "Properties"
   • Select Configuration-->Advanced tab
   • Checked 32-bit runtime

I tried to post detail step by step pictures, but Stack Overflow does not allow that due to reputation. Hope I later will update this post.

回答14:

This solution worked for me:

Go to SQL Server Management Studio, Right click on the failing step and select Properties -> Logging -> Remove the Log Provider, and then re-add it

回答15:

Another permutation that causes a problem in 2008R2 is Package Configuration. I had set a property to be saved/configured from a dtsconfig file and then deleted the connection that it referred to. The resolution was simple, edit the configuration and de-select the unwanted object and then select the appropriate property for the renamed connection manager. The error did not recur after saving, closing and re-opening the project. :-)

回答16:

I determined that this problem was a corrupt connection manager by identifying the specific connection that was failing. I'm working in SQL Server 2016 and I have created the SSISDB catalog and I am deploying my projects there.

Here's the short answer. Delete the connection manager and then re-create it with the same name. Make sure the packages using that connection are still wired up correctly and you should be good to go. If you're not sure how to do that, I've included the detailed procedure below.

To identify the corrupt connection, I did the following. In SSMS, I opened the Integration Services Catalogs folder, then the SSISDB folder, then the folder for my solution, and on down until I found my list of packages for that project.

By right clicking the package that failed, going to reports>standard reports>all executions, selecting the last execution, and viewing the "All Messages" report I was able to isolate which connection was failing. In my case, the connection manager to my destination. I simply deleted the connection manager and then recreated a new connection manager with the same name.

Subsequently, I went into my package, opened the data flow, found that some of my destinations had lit up with the red X. I opened the destination, re-selected the correct connection name, re-selected the target table, and checked the mappings were still correct. I had six destinations and only three had the red X but I clicked all of them and made sure they were still configured correctly.

回答17:

I generally find that when SSIS seems to be irrationally complaining about an apparently good connection, it is because I am trying to define the Connection directly using a package variable rather than via a Connection Manager. Example: today I had a Web Service Task where I made the mistake of directly creating an Expression defining its "Connection" property in terms of a package variable that contained the URL of the web service. Note however that a Connection is not the same thing as a ConnectionString! So when I looked at the task, it looked for all the world like it had everything valid, because it displayed a perfectly valid URL as the "Connection". The problem is that the Connection cannot be a string; it must be a Connection Manager.

回答18:

The connection value in the job seems to be case sensitive.

回答19:

For package developed in Visual Studio 2015, I found i must supply a value for the parameter (which would be the case when you deploy or run on different server) which sets the connection manager's connection string instead of using the design time value. This will suppress the error message. I think this could be a bug.

dtexec /project c:\mypath\ETL.ispac /package mypackage.dtsx /SET \Package.Variables[$Project::myParameterName];"myValueForTheParameter"

I tested this without or without parameterize the connection string, which is at the project level. The result was the same: i.e. I have to set the value for the parameter even thought it was not used.

回答20:

I had the same problem.

I use project level connection managers and my packages run correctly in SSDT but when I deployed them and execute them through a job with sql server agent, I get "Connection not found" errors.

So I deploy the project and then the problem was solved, when you use project level connection managers but just deploy a single package from that project, and you call package through sql server agent, it could not recognize your connection managers so you should determine package level connection managers or you should first deploy your project.