Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

I want to run someone else's SSIS package, which file do they need to send me? Expand / Collapse
Author
Message
Posted Sunday, March 16, 2014 12:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 680, Visits: 1,595
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?
Post #1551587
Posted Sunday, March 16, 2014 8:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 3,024, Visits: 2,628
Check what version of SSIS was used by your colleague to create the SSIS package. I suspect that a different version was used.




Post #1551627
Posted Sunday, March 16, 2014 10:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 908, Visits: 2,580
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1551637
Posted Sunday, March 16, 2014 11:07 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 680, Visits: 1,595
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?
Post #1551640
Posted Sunday, March 16, 2014 11:42 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 908, Visits: 2,580
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1551643
Posted Sunday, March 16, 2014 11:58 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 680, Visits: 1,595
very good ideas. I will try this.
Post #1551644
Posted Monday, March 17, 2014 12:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 680, Visits: 1,595
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?
Post #1551646
Posted Monday, March 17, 2014 12:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 908, Visits: 2,580
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1551652
Posted Monday, March 17, 2014 1:16 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 680, Visits: 1,595
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
------------------------------
Post #1551655
Posted Monday, March 17, 2014 2:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 908, Visits: 2,580
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1551664
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse