Where Can I Find DTDs for SSIS Artifacts

Question

Does Microsoft make the DTDs for SSIS artifact files available for viewing/download, and if so, where? Specifically I am seeking DTDs for:

• @Shayna Webb .manifest
• *.conmgr
• *.dtsx
• *.params

Context: I am working on a .NET Core application that pulls specific information from these files for further analysis (for example, extracting SQL for review). Having complete DTDs for these file types would allow me to generate XML serialization classes so I can pull information more easily from the files. Alternatively, if a serialization library already exists for these XML schemas, I would gladly use that instead.

What I have tried: I have attempted the following and found the results problematic.

Using LINQ to query the XML: My first attempts at this utilized LINQ to query the XML for the data elements I need to pull. I found this error-prone due to a lack of a complete specification for where I can find the elements I need. For example, attempting to gather the SQL used in a Data Flow task requires searching in the task for a command, a table name value, or a variable name. Each Task type can handle this information slightly differently which requires probing the XML until the required information is found. Deserializing the XML would make this process easier to code and easier to read/maintain.

Using the Microsoft.SqlServer.Dts libraries: My next attempts used the SSIS libraries (like Microsoft.SqlServer.ManagedDTS). This presented several problems. First, it restricted the product I am building to .NET Framework since these libraries are not available in .NET Core form and do not necessarily adhere to the latest .NET Standard implementation. Second, there is a great deal of overhead that I do not require. A specific example is an Execute SQL task where a parameter did not have a default value and could not be bound which prevented loading the package at all.

Creating XSDs for each type: I turned next to using xsd.exe to generate XSDs for each file type, then used xsd.exe to generate C# classes from those XSDs. Unfortunately, I do not have examples of every widget. For example, a given DTSX will not contain an example of every possible task/data flow that can be included in an SSIS package. This results in incomplete XSDs which then result in incomplete class definitions.

Using Paste Special > Paste XML as Classes in Visual Studio: This suffers from a similar problem as the previous. Depending on which exemplar I choose, I get an incomplete set of classes. Further, the process regularly results in code that is correct for that specific file but but not for others. For example, a DTSX with one connection manager results in a field for a connection manager instead of a field that is an array of connection manager instances.

Google: I have googled various ways attempting to find these DTDs. The results invariably lead me to how to use DTDs within an SSIS package to assist in reading, writing, or loading data from an XML file.

I provide this list to illustrate what I have tried without or with little success. I am seeking either a set of libraries that can be used to deserialize the SSIS XML or the complete DTDs so I can generate the necessary classes for this deserialization. Thanks for any assistance you can provide!

Answer 1

After extensive googling and a large number of false leads, I located the XSDs for SSIS projects and packages here:

SQL Server File Formats Technical Documents

Answer 2

Hi @Christopher Hardage ,

You are asking a great question.

Overall, in XML technology stack, DTDs were replaces by XML Schema Definitions (XSDs) in 2001.
Microsoft never published officail XSDs for .dtsx files.

I personally query SSIS .dtsx files directly from the file system in SSMS by using XQuery in T-SQL. If you will search the old MSDN forums, you might find me answering questions how to query .dtsx files. And it is always try-and-see exercise.

Answer 3

Hi @Christopher Hardage ,

We can set SSIS logging and choose Provider type as SSIS log provider for XML files in ssis package.

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.