I have made a custom SSIS Connection Manager (.NET 4.6.2):
namespace HelloWorldCm
{
using Microsoft.SqlServer.Dts.Runtime;
[DtsConnection(
ConnectionType = "HELLOWORLD",
DisplayName = "Hello World Connection Manager",
Description = "Connection manager for Hello World")]
public class HelloWorldConnectionManager : ConnectionManagerBase
{
public override DTSExecResult Validate(IDTSInfoEvents infoEvents)
{
return DTSExecResult.Success;
}
}
}
I'm referencing Microsoft.SQLServer.ManagedDTS version 14.0.0.0 which in my understanding is SQL Server 2017. Locally, if I register it in the GAC and copy to my /Connections folder I can use SSIS from within Visual Studio, and add the Connection Manager into my DTSX.
I can save / build etc and it's working fine - so I then copy the Connection Manager and DTSX to my VM running SQL Server 2017. I once again register in the GAC and copy to the /Connections folder.
I then Import Packages... into my Integration Services Catalogs, select the DTSX and press Next. This then tells me that:
One or more selected packages are not ready.
And when I look at why I see:
The connection type "HELLOWORLD" specified for connection manager "Hello World Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
I've been trying to get this to work now for quite some time, so I'll list what I've tried:
I'm sure others. What I've also done is create a Custom Task, which simply printed 'Hello World' to the logs, this was compiled as 'Any CPU', deployed in the same way (to the same locations) and SSIS didn't seem to have any problems finding it and accepting the DTSX. So I'm reasonably confident 'Any CPU' should be good enough.
I'm not sure where to go now - as far as I can see I've got the right references, the right .NET version, and deployment is the same as for the task.
I've put the dtsx below in case it's something in there:
<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="6/11/2019 1:09:07 PM"
DTS:CreationName="Microsoft.Package"
DTS:CreatorComputerName="CHRIS"
DTS:CreatorName="CHRIS\cskar"
DTS:DTSID="{0F2D59A1-461B-46ED-8765-75484C06253C}"
DTS:ExecutableType="Microsoft.Package"
DTS:LastModifiedProductVersion="15.0.1100.123"
DTS:LocaleID="2057"
DTS:ObjectName="Package1"
DTS:PackageType="5"
DTS:VersionBuild="1"
DTS:VersionGUID="{E688A11B-234E-4F39-991E-BAA0A37642E6}">
<DTS:Property
DTS:Name="PackageFormatVersion">8</DTS:Property>
<DTS:ConnectionManagers>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[Hello World Connection Manager]"
DTS:CreationName="HELLOWORLD"
DTS:DTSID="{9E82E067-2A0A-4C9C-931D-FD59FD159B5C}"
DTS:ObjectName="Hello World Connection Manager">
<DTS:ObjectData>
<InnerObject>
<TargetServerVersion
Type="3"
Value="150" />
</InnerObject>
</DTS:ObjectData>
</DTS:ConnectionManager>
</DTS:ConnectionManagers>
<DTS:Variables />
<DTS:Executables />
<DTS:DesignTimeProperties><![CDATA[<?xml version="1.0"?>
<Objects Version="8">
</Objects>]]></DTS:DesignTimeProperties>
</DTS:Executable>
The SSIS catalog is meant to store projects, not standalone packages. This was a new feature in 2012 which helps to keep related packages together. You can parameterize the project and share connection managers between packages. If you're looking to deploy to the catalog, you'll need to import a ".ispac" file, or you can right-click in Visual Studio and press "Deploy".
When in project deployment model, an individual ".dtsx" package can not be treated as stand-alone. It only makes sense as part of the project because of the new features described above.
Deploying stand-along packages to MSDB is the legacy model and is maintained so we don't have to migrate all of our packages to the catalog immediately.