包中未找到SSIS连接

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

我是SSIS编程的新手，我在部署SSIS包时遇到了一些问题。

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

代码：0xC001000E来源：说明：找不到连接“{DA7CD38D-F6AA-4B06-8014-58BEE5684364}”。如果找不到特定的连接元素，Connections集合将抛出此错误。结束错误

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

错误：2012-08-09 00：21：06.25代码：0xC001000E源：包描述：找不到连接“{DA7CD38D-F6AA-4B06-8014-58BEE5684364}”。如果找不到特定的连接元素，Connections集合将抛出此错误。结束错误

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

错误：2012-08-09 00：21：06.25代码：0xC001000E源：包描述：找不到连接“{DA7CD38D-F6AA-4B06-8014-58BEE5684364}”。如果找不到特定的连接元素，Connections集合将抛出此错误。结束错误

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

错误：2012-08-09 00：21：06.25代码：0xC00291EB源：执行SQL任务执行SQL任务描述：连接管理器“{DA7CD38D-F6AA-4B06-8014-58BEE5684364}”不存在。结束错误

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.

错误：2012-08-09 00：21：06.25代码：0xC0024107源：执行SQL任务描述：任务验证期间出错。结束错误DTExec：包执行返回DTSER_FAILURE（1）。开始时间：00：21：04完成时间：00：21：06经过：1.888秒包执行失败。步骤失败了。

19 个解决方案

#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 .

您的ssis包似乎指向可能已被删除或重命名的其他连接。尝试打开SSIS组件并指向连接管理器中的正确连接。

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.

当我们复制SSIS包组件以创建新包或者因为重命名连接或者仍然存在使用xml配置文件中定义的旧连接的组件时（在您的情况下尝试检查执行SQL任务，这是抛出错误）。如果您使用XML进行配置，请尝试部署新的。

#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.

创建SSIS 2012包时，在“解决方案资源管理器”窗口中，您将看到项目级别的“连接管理器”文件夹。这似乎是创建连接的最合理的位置，应该在创建可以由项目中的任何包使用的连接时使用。它包含在项目级别，而不是包级别。

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.

当使用dtexec运行我的dtsx包时，我收到了上面显示的相同错误。这是因为包中没有包含连接（只是项目）。我的解决方案是进入包设计器，然后在Connection Manager窗口中，右键单击项目级连接（使用“（project）”前缀显示）并选择“Convert to Package Connection”。这将在实际的dtsx包中嵌入连接。这缓解了我的问题。

#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.

当您使用新的SSIS 2012“共享连接管理器”概念时，似乎也会发生这种情况，其中连接管理器未在您的包中定义，而是在Visual Studio项目中定义，只是在包中引用。通过SQL Agent或DTEXEC执行它会产生相同的错误消息。

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.

一个解决方案：通过Windows资源管理器以XML格式打开包，找到无法找到的连接管理器的GUID。就我而言，这是一个被破坏的EventHandler连接。在控制流程中使用了相同的连接管理器，但不知何故在那里没有损坏，因此用户通过UI并不明显。由于XML指向事件处理程序连接管理器，我在UI中打开了事件处理程序选项卡，它立即在源和目标上显示了引用已损坏的连接管理器ID的精彩RED X.我把它重新命名为正确的经理，重建了pkg并保存了。很高兴去。

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.

关键是以XML格式打开pkg并在代码中找到GUID以查看它失败的位置。如果我无法在UI中找到对它的有效引用，我将要将XML连接重命名为XML中的另一个已知GUID，然后进入UI并再次重新指定它，或者完全删除它。

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).

如果您正在使用项目级连接但仍想使用dtexec，请不要担心有办法。我不建议将它们转换为包级连接（假设您将它们创建为项目级连接有充分理由）。

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

您需要部署SSIS项目。您的SSIS服务器需要创建目录（https://msdn.microsoft.com/en-us/library/gg471509.aspx）。获得目录后，在SSIS项目中选择Project-> Deploy并按照向导进行操作。结果将是在SSIS解决方案文件夹/ bin / Development中生成的* .ispac文件

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

现在为money命令，而不是用一个简单的：dtexec.exe / f“package.dtsx”调用你的包

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

而是这样称呼它：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!

ispac文件具有执行包所需的项目级连接信息，您应该进行设置！

#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

我为解决这个问题所做的很简单。我不得不重命名我的SQL Server，以便它响应（localhos）标记。之后，我改变了SSIS上的所有连接，并重建了解决方案......它起作用了。希望它能帮助你

#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".

套餐周五工作正常，入住TFS并回家。星期一打开它，到处都是错误。 “在变量集合中找不到连接管理器变量$ project._connectionstring”。

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.

我rtclick-编辑连接和测试连接，没有任何问题; em。 ConnMnger位于解决方案的“连接管理器”列表中。打开连接到此连接管理器的TARGET对象，然后单击MAPPINGS时，会弹出上面的错误。映射中的任何位置都没有引用连接管理器变量。

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 -

事实证明，要纠正此问题，您必须在Connection Manager窗口中右键单击连接管理器，然后选择PARAMETERIZE。根据需要填写选项 -

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

PROPERTY：ConnectionString使用Exisgint参数：$ Project :: ConnMgrName_ConnectionString或创建新参数：按照选项

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.

一旦这个连接管理器参数化，一切正常。即使Conn Manger存在于Conn Manger选项卡中，Conn Mgr已经在解决方案资源管理器中列出，并且在2天前没有遇到任何问题。

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

奇。随你。微软是微软。 SQL Server是SQL Server。选择你的毒药。

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

我有同样的问题，上面的其他内容重新给了它。事实证明，我的ssis右下角有一个旧的sql任务被禁用，我真的不得不寻找。一旦我删除了这一切都很好

#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!

在我的情况下，其中一个事件处理程序任务指向已删除的旧连接，删除未使用的事件处理程序任务修复了问题。我最终以XML格式打开包，以了解问题在于事件处理程序任务！

#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.

在我的情况下，我可以更容易地解决这个问题。我打开了x.dtsConfig存档，由于未知原因，该存档不是标准格式，因此ssis无法识别配置。幸运的是，我之前已经备份了存档，因此我只需将其复制到原始文件夹，一切都恢复正常。

#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.

尝试使用SSDT 2013在VS中打开SSDT 2010 / SSIS 2012项目时收到此错误。当它打开项目时，它要求迁移所有软件包。当我允许它继续时，每个包都会因此错误而失败。我发现绕过转换并且只是单独打开每个包，包在打开时升级，并且转换得很好并成功运行。

#13

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

在我的情况下我遇到了同样的问题，原因是连接没有嵌入并且Oracle Client不兼容。

SOLUTION:

解：

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

我的环境：SQL SERVER 2014 64位Oracle客户端32位

1. For include/embed Connection

   对于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

   对于SQL SSIS目录/作业计划设置配置，请按照步骤进行操作

   • Right on 'SQL JOB->Step" or "SSIS Catalog-->Package-->Execute" and select "Properties"
   • 在“SQL JOB-> Step”或“SSIS目录 - >包 - >执行”上右键并选择“属性”
   • Select Configuration-->Advanced tab
   • 选择配置 - >高级选项卡
   • Checked 32-bit runtime
   • 检查32位运行时

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.

我试图逐步发布详细信息，但由于声誉，Stack Overflow不允许这样做。希望我稍后会更新这篇文章。

#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

转到SQL Server Management Studio，右键单击失败的步骤并选择Properties - > Logging - > Remove the Log Provider，然后重新添加它

#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. :-)

在2008R2中导致问题的另一种排列是包配置。我已经设置了一个要从dtsconfig文件保存/配置的属性，然后删除了它所引用的连接。解决方案很简单，编辑配置并取消选择不需要的对象，然后为重命名的连接管理器选择适当的属性。保存，关闭和重新打开项目后，错误不再发生。 :-)

#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.

我通过识别失败的特定连接确定此问题是一个损坏的连接管理器。我在SQL Server 2016工作，我已经创建了SSISDB目录，我正在那里部署我的项目。

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.

为了识别腐败的连接，我做了以下事情。在SSMS中，我打开了Integration Services Catalogs文件夹，然后是SSISDB文件夹，然后是我的解决方案的文件夹，然后打开，直到找到该项目的包列表。

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.

随后，我进入我的包，打开数据流，发现我的一些目的地已经点亮了红色的X.我打开了目的地，重新选择了正确的连接名称，重新选择了目标表，并检查了映射仍然是正确的。我有六个目的地，只有三个有红色X但我点击了所有这些目的地，并确保它们仍然正确配置。

#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.

我通常发现，当SSIS似乎非理性地抱怨一个明显好的连接时，这是因为我试图直接使用包变量而不是通过连接管理器来定义Connection。示例：今天我有一个Web服务任务，我犯了一个错误，就是根据包含Web服务URL的包变量直接创建一个定义其“Connection”属性的Expression。但请注意，Connection与ConnectionString不同！因此，当我查看任务时，它会查找所有世界，因为它显示了一个完全有效的URL作为“连接”。问题是Connection不能是一个字符串;它必须是一个连接管理器。

#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.

对于在Visual Studio 2015中开发的软件包，我发现我必须为参数提供一个值（在部署或运行​​在不同服务器上时就是这种情况），它设置连接管理器的连接字符串而不是使用设计时间值。这将抑制错误消息。我认为这可能是一个错误。

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.

我在没有或没有参数化连接字符串的情况下测试了这个，这是在项目级别。结果是一样的：即我必须设置参数的值，即使它没有被使用。