SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I want to run someone else's SSIS package, which file do they need to send me?


I want to run someone else's SSIS package, which file do they need to send me?

Author
Message
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 1905
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?
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8977 Visits: 3281
Check what version of SSIS was used by your colleague to create the SSIS package. I suspect that a different version was used.



Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6181 Visits: 3537
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:

<DTSTongueroperty DTS:Name="PackageFormatVersion">n</DTSTongueroperty>


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: blog.wsmelton.info
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 1905
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?
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6181 Visits: 3537
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: blog.wsmelton.info
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 1905
very good ideas. I will try this.
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 1905
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?
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6181 Visits: 3537
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: blog.wsmelton.info
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 1905
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 xmlnsBigGrinTS="www.microsoft.com/SqlServer/Dts"><DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[DispositionDataValidation]" DTS:CreationName="ODBC" DTSBigGrinTSID="{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
------------------------------
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6181 Visits: 3537
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: blog.wsmelton.info
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search