Using SSIS to Export a Fixed Width Flat File

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

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

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

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

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

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

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

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.

Advertisements

23 Responses to “Using SSIS to Export a Fixed Width Flat File”


  1. 1 Marvelouz June 3, 2010 at 4:26 am

    I ran through this tutorial and it was great! I am a beginner so I could have used some more details but I figured it out.

    I do have a question.. for some reason the first row in the flat file is correct but the second row and beyond, the field starts at character 12.. any ideas?

  2. 3 Binh September 1, 2010 at 6:18 am

    Hi ron,

    Thanks for sharing your knowledge!

    I am testing this tutz and get an error in regards to the CRLF column. Could you please give the exact settings of that CRLF column at the flat file connection manager editor steps when at the “Advanced tab” defining all destination columns?

    Cheers,

    B

    • 4 Ron September 7, 2010 at 10:00 am

      Binh,

      The settings are outlined below.

      Name: CRLF
      InputColumnWidth: 1
      Data Type: Unicode string [DT_WSTR]
      OutputColumnWidth: 1
      TextQualified: False

      Please let me know if you have any other questions and I will post a sample package.

      • 5 Raj June 1, 2011 at 7:27 pm

        I am not able to get this work, I do exactly same way you have mentioned. I am having almost 15 columns but it always gives error stating “failed to write column name of the column “all the columns after 6;7,8,9,to 15″”

      • 6 Raj June 2, 2011 at 6:02 pm

        but this seems to be not working becuz how come in the input column width is 4 (CLRF=4) and you using 1, as input width column. Please help

      • 7 Ron June 3, 2011 at 12:41 am

        Raj,

        CRLF is the column name (representing carriage return/line feed). The actual value of this column in the example is CHAR(10), which is the ASCII code for line feed, and therefore a length of 1. Can you please post the SQL (obfuscated if necessary) you are using and a screen shot of the error and I will be happy to assist you.

        Regards,

        ~Ron

  3. 8 sunny November 8, 2010 at 9:40 pm

    Hi Ron

    It is a nice article. I am still not able to create a fixed width file (i mean every row in new line). Can you please upload sample package.

    Thanks
    Sunny

    • 9 Ron November 9, 2010 at 12:28 am

      Sunny,

      I have added a sample package link in the main article. The solution was created in Visual Studio 2008. Please let me know if this works for you.

      Happy coding,

      ~Ron

  4. 10 Eugene December 22, 2010 at 9:59 pm

    I don’t know why but looks like just CHAR(10) doesn’t work for me. I had to use CHAR(10) + CHAR(13) and it worked fine. Thanks for your tutorial!

  5. 14 Eugene December 22, 2010 at 10:10 pm

    I tested it on SQL2008 R2 and “Flat File Connection Manager” creates a file with Code page 1252 (ANSI – Latin I)

  6. 16 Bhavi February 5, 2012 at 10:51 pm

    Hi Ron.. I am having the same issue can you please help me out?

  7. 17 SSN March 10, 2012 at 4:05 pm

    With above info. from the discussion, all the data is exported into Sharepoint column, except the Document data which is in Binary format in SQL Server.!

    What must be the SharePoint 2010 Column data-type to receive the binary data from SQL Server via SSIS?

    Thanks.

  8. 18 Sudsie August 3, 2012 at 1:48 pm

    Great post. Works flawlessly.

  9. 19 Jackie April 29, 2013 at 4:54 pm

    Thanks! Great post. First time using SSIS. Got it working quickly.

  10. 20 Matt August 5, 2013 at 9:26 am

    I can’t get this to work having created it from new following your guide. Error: [Flat File Destination [24]] Error: The column name for column “CRLF” could not be written. The column name may be longer than the available column size.

    • 21 Ron August 5, 2013 at 11:02 am

      Are you writing column names to the destination file?

      • 22 K83 July 9, 2014 at 1:15 pm

        On the off chance you are still watching this thread, I am also getting the same error. I’m not sure what the question means though regarding “writing column names to the destination file”. My destination file does need to have column names and I *think* I’m writing them to the file, although I haven’t yet managed to successfully produce the file. I am also using the suggested CHAR(13)+CHAR(10) for the CRLF (10 alone did not work). This is my first SSIS project, so I definitely need as much detail as possible on the explanation. Really appreciate your help with this.

  11. 23 Markus May 15, 2014 at 5:24 am

    Works fine! Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: