Is it possible to Decompile and read an SSIS package code?

Question

I am working on a project which was owned by a different person. He created a job to update bunch of fields in handful of tables in SQL server. That job was based on an SSIS package. Now I have the DTSX file and the disabled job. When I tried to enable the job after changing the old credentials in the DTSX file, I am getting weird errors. I want to see the code behind the SSIS package and want to see how the update process is coded. Is there a way to do this?

If I am not mistaken, DTSX files are XML files. Just rename to xml, and open with an editor. — James A Mohler, May 29 '19 at 21:01
Yeah that I can see using a wordpad. But is there a way to see how the 'Update' is coded? I don't see anything related to that requirement in the DTSX file. — Rick, May 29 '19 at 21:02
Thanks. You idea was actually helpful. Found the requird statements. Can you make your comment as answer? — Rick, May 29 '19 at 21:06

score 5 · Answer 1 · answered May 29 '19 at 23:09

I want to see the code behind the SSIS package and want to see how the update process is coded

As other answers mentioned, dtsx files are XML files, you can simply open these files using a text editor to check the code behind, or you can simply create a new Integration Services project using visual studio and add the package to check it's control flow.

Integration Services (SSIS) Projects and Solutions

I added my own answer to list some of what i posted related to this issue, since it may give you some insights:

score 3 · Accepted Answer · answered May 29 '19 at 21:46

3

DTSX files are just XML files. Just rename to xml, and open with an editor.

The query is a string that is built up via string concatenation.

answered May 29 '19 at 21:46

James A Mohler

11,060
15
46
72

3

You do not need to rename it to *.XML because the X in DTSX stands for XML. Just open the *.DTSX in Notepadd++. – J Weezy May 30 '19 at 14:48
2

And if you want nice XML syntax highlighting, just add `dtsx` to the list of extensions that are associated with the XML language in Notepad++ under Settings -> Style Configurator. For example, I have added the following extensions: `build dstx ds dsv cube dim dwproj xmla config nunit nbits`. – Kendall Lister Apr 08 '20 at 03:00

score 2 · Answer 3 · answered May 29 '19 at 21:51

2

Create a new solution and import the DTSX file into that. Visual Studio just serves as a glorified XML editor for SSIS packages.

answered May 29 '19 at 21:51

J Weezy

3,507
3
32
88

score 2 · Answer 4 · answered May 30 '19 at 00:58

Depending on which version of SSIS you're working with, you'd want to look at the XML of the package (F7 if you have the package open) and find a block like the following

<DTS:Executable
  DTS:refId="Package\Execute SQL Task"
  DTS:CreationName="Microsoft.ExecuteSQLTask"
  DTS:Description="Execute SQL Task"
  DTS:DTSID="{93D895E6-A316-4718-9C97-4A5652ABD28C}"
  DTS:ExecutableType="Microsoft.ExecuteSQLTask"
  DTS:LocaleID="-1"
  DTS:ObjectName="Execute SQL Task"
  DTS:TaskContact="Execute SQL Task; Microsoft Corporation; SQL Server 2017; © 2017 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"
  DTS:ThreadHint="0">
  <DTS:Variables />
  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStatementSource="UPDATE T SET Count = Count+1 FROM dbo.Table AS T;" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>
</DTS:Executable>

This task uses a "hard coded" statement which is associated with the SQLTask:SqlStatementSource entity.

However, if the Execute SQL Task is driven by a Variable, then you'd see a block of XML as follows. There's no actual query there so you'd have to find the <Variables> collection that define User::MyQueryVariable and then you'd only have the design time value and not the run-time value

  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStmtSourceType="Variable"
      SQLTask:SqlStatementSource="User::MyQueryVariable" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>

Finally, for completeness, if the developer had specified File Source, you'd see something like this

  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{CEE71D69-1F9F-46FA-A22F-C9EB60670EA0}"
      SQLTask:SqlStmtSourceType="FileConnection"
      SQLTask:SqlStatementSource="FileConnectionSource" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" />
  </DTS:ObjectData>

You would then need to find the definition for FileConnectionSource within the XML as well.

Is it possible to Decompile and read an SSIS package code?

4 Answers4