Introduction
According to Microsoft, DTSX is an XML-based file format that stores the instructions for the processing of a data flow from its points of origin to its points of destination, including transformations and optional processing steps between the origin and destination points. In a nutshell, when you are creating your SSIS package, the SQL Server Data Tools SSIS Designer creates and saves all the data about your package into an XML file. As you may know, XML files in general are not designed for human consumption, as anyone that opened and tried to read an XML file can attest to that. So, is there any good reason to use XML? This blog post is going to discuss the some bad and good of the DTSX file.
Since we are already know how the DTSX file is created, let's see how you can read it. You can accomplish that by right-clicking on your package and selecting view code (Fig #1).
Fig #1
As you will quickly realize by looking at the XML, the text inside the file is mostly unreadable and only the computer can convert text in A into a visual representation in B (Fig #2).
Fig #2
This is the downside of the DTSX XML file. Let's do the opposite. Let's find something useful that we can do with that XML file.
A Problem
One of the most basic problems with SSIS Designer UX is trying to consume text files (a.k.a. flat-files) and hitting the Microsoft data type defaults wall. Unfortunately, Microsoft decided that for flat files, the column width or the max number of characters would be 50 (see Fig #3). While in some cases 50 characters would be enough, in most cases 50 characters would is too few. To fit the extra text, you will need to increase the number of allowed characters to prevent the truncation error, an error that will occur when the SSIS run-time engine will attempt to pull the data from a text file into a output buffer that is too small.
Fig #3
Fixing a truncation error for all 3 columns will require you to go column-by-column and change the value, 50, to something bigger, which is a bit painful, but still manageable. What about 50 columns? What if you need to change the 50 character limit on 50 columns? Now that becomes much more painful and not really manageable.
A Solution
The solution is to leverage the DTSX file. While that file contains plenty of unreadable gibberish tags that help the computer, it does have some text that we humans can read and use. We can use the text editor to find and replace specific text. Specifically, we can target the string, DTS:MaximumWidth=50. The DTS:MaximumWidth tag defines the max number of characters for a column.
For example, suppose we need to increase the width to 250. All we have to do is to hit Ctrl+F for the popup Find and Replace window, type DTS:MaximumWidth=50 into the find textbox, type DTS:MaximumWidth=250 into a replace textbox (Fig #4), hit Replace All , and hit X to close XML editor while saving the changes. It's that simple.
Fig #4
Disclaimer
This blog post is partially based on the following resources: https://docs.microsoft.com/en-us/openspecs/sql_data_portability/ms-dtsx/235600e9-0c13-4b5b-a388-aa3c65aec1dd
