March 16, 2014 at 12:32 pm
Someone in my organization send me their .dtsx file and said 'here, reverse engineer it for your needs'.
I have Visual Studio 2012 and all BI add-ins, so, I created a new project and tried to add the .dtsx to it as a package. ie. File->New Project->Integration Services Project, right click on ssis packages folder and selected 'add existing package' and browsed to the .dtsx file, hit OK. Error resulted:
The connection type "" specified for 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. ..
However, since I can't open the package, I'm unable to edit the connection.
How can I open a .dtsx to edit the connection properties, if the cause of the failure to load is the connection type? Does my colleague need to send the .sln file?
March 16, 2014 at 8:54 pm
Check what version of SSIS was used by your colleague to create the SSIS package. I suspect that a different version was used.
March 16, 2014 at 10:56 pm
You can open the DTSX file in Notepad to determine what version of SQL Server it was created with and look at the "PackageFormatVersion" value. The exact line should look something like:
<DTS:Property DTS:Name="PackageFormatVersion">n</DTS:Property>
The "n" will be a number of one of the following, and I can't find anything concreate from Microsoft on this it just trial and error (and a bit of Googling):
2 = SQL Server 2005
3 = SQL Server 2008 R2 (probably SQL Server 2008 as well)
6 = SQL Server 2012
? = SQL Server 2014 (if someone has those tools loaded I am not sure what value it will be for this version).
Since the XML structure changed a good bit with SQL Server 2012 you are likely not going to be able to easily open it or modify it to open without errors.
If you have the whole project the DTSX file was created with you when you open that project in 2012 it will upgrade the package for you. This would be the best route if you cannot get your hands on the BIDS install for 2008 or 2005.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 16, 2014 at 11:07 pm
thanks for replying happycat and shawn. Glad to learn about opening up in notepad and tips for finding version.
PackageFormatVersion is 6 so 2012!
So, should I be able to open it even though it is just a dtsx file?
March 16, 2014 at 11:42 pm
KoldCoffee (3/16/2014)
thanks for replying happycat and shawn. Glad to learn about opening up in notepad and tips for finding version.PackageFormatVersion is 6 so 2012!
So, should I be able to open it even though it is just a dtsx file?
Yes, you should be able to add it to your current SSDT project. The person that sent you that package file may have deleted the connection manager from the package before sending you the file as it may have contained username/password information.
You might try creating a new package and adding a connection manager to it then compare that to the other package and see if any sections of XML are missing that deal with the connection manager. You might be able to put in some "dummy" information so SSDT will open the package up.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 16, 2014 at 11:58 pm
very good ideas. I will try this.
March 17, 2014 at 12:14 am
And what files would be a reasonable way to ask for from someone intending to share their ssis package with you? This ssis package exists on that person's computer in whole...so doesn't it seem reasonable to just ask they resend it with the files I need?
March 17, 2014 at 12:43 am
KoldCoffee (3/17/2014)
And what files would be a reasonable way to ask for from someone intending to share their ssis package with you? This ssis package exists on that person's computer in whole...so doesn't it seem reasonable to just ask they resend it with the files I need?
SSDT 2012 changed a large amount of things with SSIS packages, one being design structure. They included project level control of connections which are seperate files to the DTSX files.
I would generally not share the actual DTSX files for projects I have worked on simply because they contain client/company specific information. I don't know to many folks that actually share DTSX files between each other.
If you know the person well just ask them if and what they can send you. Just tell them you can't get the file to open to reverse engineer it. Otherwise open up Notepad and start reading through it, the XML is actually not that hard to read through and get the gist of what is going on.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 17, 2014 at 1:16 am
Shawn, In the XML file I find three connectionManagers.
It seems that VS is not liking the OData one. So, I removed it (left other two alone) and tried reload, but because of other dependencies am getting further errors.
If I remove the downstream data tasks that depend on this connectionManager then I am in effect deleting the package I need to reverse engineer..I think.
Can you see around this?
----error before I remove Odata connectionManager (leaving other two alone)
TITLE: Microsoft Visual Studio
------------------------------
Error at Asset_MultiCast []: The connection type "ODATA" specified for connection manager "OData Source" 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.
Error at Asset_MultiCast []: Error loading value "<DTS:ConnectionManagers xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[DispositionDataValidation]" DTS:CreationName="ODBC" DTS:DTSID="{E8EE4378-E62D-491F-B908-93FAB9E40D01}" DTS:ObjectName="Disposit" from node "DTS:ConnectionManagers".
------------------------------
ADDITIONAL INFORMATION:
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
(Asset_MultiCast)
------------------------------
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
(Asset_MultiCast)
------------------------------
BUTTONS:
OK
------------------------------
---------error after removing OData connectionManager
TITLE: Microsoft Visual Studio
------------------------------
Error at {4EC5F2F0-0A5D-488B-A67D-E81F733D2D45} []: The object "/DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component/connections/connection" references ID "Package.ConnectionManagers[OData Source]", but no object in the package has this ID. If the object "/DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component/connections/connection" was added to the package by a manual edit ensure that the referenced object with ID "Package.ConnectionManagers[OData Source]" is also added to the package.
Error at {4EC5F2F0-0A5D-488B-A67D-E81F733D2D45} []: The object "/DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component/connections/connection" references ID "Package.ConnectionManagers[OData Source]", but no object in the package has this ID. If the object "/DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component/connections/connection" was added to the package by a manual edit ensure that the referenced object with ID "Package.ConnectionManagers[OData Source]" is also added to the package.
------------------------------
ADDITIONAL INFORMATION:
The package failed to load due to error 0xC001001C "The object "%1" references ID "%2", but no object in the package has this ID. If the object "%1" was added to the package by a manual edit ensure that the referenced object with ID "%2" is also added to the package.". This occurs when CPackage::LoadFromXML fails.
({4EC5F2F0-0A5D-488B-A67D-E81F733D2D45})
------------------------------
The package failed to load due to error 0xC001001C "The object "%1" references ID "%2", but no object in the package has this ID. If the object "%1" was added to the package by a manual edit ensure that the referenced object with ID "%2" is also added to the package.". This occurs when CPackage::LoadFromXML fails.
({4EC5F2F0-0A5D-488B-A67D-E81F733D2D45})
------------------------------
BUTTONS:
OK
------------------------------
March 17, 2014 at 2:52 am
KoldCoffee (3/17/2014)
Shawn, In the XML file I find three connectionManagers.It seems that VS is not liking the OData one. So, I removed it (left other two alone) and tried reload, but because of other dependencies am getting further errors.
The type of connection is the first clue, OData.
If I am not mistaken from googling, that is the connection manager used for connecting to a SQL Azure or other cloud service. You will likely need to install something in order for your instance of SSDT to know anything about that type of connection manager. I am not sure if it is SQL Azure maybe the SDK.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 17, 2014 at 7:35 am
Shawn, thanks.
Icbidtg (I can't believe I didn't try google:w00t:). I didn't think it could be about the type of connection. Thanks for the next lead.
March 17, 2014 at 10:24 pm
Shawn, I've been googling but OData links are sparse.
I want to create an OData source in a new Project/package but there's no Source Connection Manager called that in my sources lists. What do I need to install to have one of those/ where can I find what I need to install?
Very much need some help. Thanks.
March 18, 2014 at 7:56 am
KoldCoffee (3/17/2014)
Shawn, I've been googling but OData links are sparse.I want to create an OData source in a new Project/package but there's no Source Connection Manager called that in my sources lists. What do I need to install to have one of those/ where can I find what I need to install?
Very much need some help. Thanks.
As I am not all that familiar with it myself I would probably ping the person that sent you the package and ask them how they created that type of connection. Things I came across seem like it is a custom setup to create it as SSIS currently does not support OData data sources, it may have been a third party add-on for Visual Studio or SSIS that was used.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 18, 2014 at 8:23 am
Thanks a lot for that advice. I will try today.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply