To begin, open Business Intelligence Development Studio (or Visual Studio, if you have that installed) and under Business Intelligence projects, create a new Integration Services Package. You will also need a directory at c:\export to contain the output flat file.
Start by adding a new database connection on the Connection Manager tab (this is usually at the bottom of the SSIS Package Designed). For the purposes of this tutorial I will be using the AdventureWorks database my local machine. This connection will be the source of the data for the flat file.
Database Connection
After you create the database connection, go ahead and create the flat file destination connection. I used the c:\export directory and named my export file Export.txt, but anything will work. On the format drop down, select “Fixed width.”
Data Destination
Please ignore the “Column” sub-menu on the right for now. Select “Advanced” from the sub-menu on the right. Now we will create all of our columns. For this tutorial I created a mail merge type export using First Name, Last Name, Address, City, State and Zip. Add one column to handle the line feed/carriage return. Without this column the package output will concatenated into on one row.
Data Destination Columns
On the Control Flow tab of the SSIS Package, add a data flow task. After adding the task, select the Data Flow tab. Add an OLE DB Source. On the OLE DB Connection Manager, the database connection we created earlier should already be selected. If it is not, select the connection we created at the beginning of the tutorial. The T-SQL used to select the data is below. Please note that there is no data formatting in the T-SQL, only the inclusion the column to handle the line feed/carriage return.
ANSI decimal character 10 is line feed, CHAR(10) in T-SQL, and ANSI decimal character 13 is a carriage return, CHAR(13) in T-SQL. Sometimes these characters are used in conjunction, depending on the format, but for our purposes we must use only one or the other.
SELECT
pc.LastName,
pc.FirstName,
pa.AddressLine1,
pa.City,
psp.StateProvinceCode,
pa.PostalCode,
CHAR(10) CRLF
FROM
Person.Contact pc
INNER JOIN HumanResources.Employee hre
ON pc.ContactID = hre.ContactID
INNER JOIN HumanResources.EmployeeAddress hrea
ON hre.EmployeeID = hrea.EmployeeID
INNER JOIN Person.Address pa
ON hrea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp
ON pa.StateProvinceID = psp.StateProvinceID
WHERE
psp.CountryRegionCode = 'US' ;
Data Source
One of the issues that you will face with any fixed width export are columns whose width is greater than the fixed width destination. We are going to handle this with a Data Conversion task. Be sure to give the converted column a friendly name, other than “Copy of ” and the column and set the length of the converted field to the same length of the destination fixed width column.
Data Conversion
To make sure that the package succeeds if the width of the origin column is greater than the destination column, we will set the conversion to ignore all truncation errors. For these purposes it is important not to confuse width and length. A column with a data type of nchar(4) has a width of 4 but may have a length of 0 to 4. The data length may be less than or equal to destination, but if the width is greater it may prove problematic.
Error Handling
After adding the data conversions and error handling, let’s map the converted columns to our flat file destination columns.
Flat File Mapping
That’s it. Run the package and you will see the fruits of your labor.
~Ron
UPDATE: The zipped solution can be downloaded from here. WordPress does not allow files of this type, so it is available via Dropbox.
