How to read a flatfile with lowercase thorn as the delimiter

by

Being a dumb ‘merican, I think the lower case thorn character is 0xFE while upper case is 0xDE. This will become important soon.

I created an SSIS package with a Flat File Connection Manager. I pointed it at a comma delimited file that looked like

col 1,col 2,col 3

This allowed me to get the metadata set for the file. Once I have all the columns defined and my package is otherwise good. Save it. Commit it to your version control system. If you’re not using version control, shame on you, but then make a copy of your .dtsx file and put it somewhere handy.

Replace the comma delimited file with the a thorn delimited one.

What we’re doing

What we’re going to do is edit the XML that is our SSIS package by hand to exchange the delimter of a , with a รพ. It’s a straight forward operation but since you are going off the reservation, it’s easy to foul up and then your package won’t open up properly in the editor.

How to fix it

If you have the package open, close the package but leave Visual Studio open. Right click on the file and select “View Code”.

In an SSIS 2012 package, you’ll be looking for 

DTS:ColumnDelimiter="_x002C_"

In a 2008 package,

<DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property>

What we’re going to do is substitute _x00FE_ (thorn) for _x002C_ (comma). Save the file and then double click to open it back up.

Your connection manager should now show the thorn symbol on the Columns tab.

enter image description here

Interestingly enough, after you open the package, if you go back into the Code, the editor will have swapped the thorn character into the file in place of the hexagonal character code. Weird.

More Related Contents:

  1. Import most recent csv file to sql server in ssis
  2. How to create a temporary table in SSIS control flow task and then use it in data flow task?
  3. SSIS – How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename
  4. Parameterized OLEDB source query
  5. Microsoft.ACE.OLEDB.12.0 is not registered
  6. SSIS how to set connection string dynamically from a config file
  7. How to load a flat file with header and detail data into a database using SSIS package?
  8. Insert a single row and return its primary key
  9. Export Varbinary(max) column with ssis
  10. How to send the records from a table in an e-mail body using SSIS package?
  11. SSIS Package Not Running as 32bit in SQL Server 2012
  12. 2 Data Flow Tasks Linking to one Execute SQL Task
  13. The value violated the integrity constraints for the column
  14. How to convert string in format yyyyMMdd to date using SSIS expression?
  15. How to import a fixed width flat file into database using SSIS?
  16. How to replace double quotes in derived column transformation?
  17. SSIS 2012 – Insert new rows, ignore existing rows
  18. SSIS: script task (vs15) not work when deploy on sql server 2014
  19. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  20. convert Excel Date Serial Number to Regular Date
  21. How can I load a large flat file into a database table using SSIS?
  22. How to access ssis package variables inside script component
  23. How do I pass value to a stored procedure parameter in OLE DB Source component?
  24. Automate Version number Retrieval from .Dtsx files
  25. Dynamically assign filename to excel connection string
  26. Reverse engineering SSIS package using C#
  27. Temporarily disable all foreign key constraints
  28. Convert from DateTime to INT
  29. Skipping rows when importing Excel into SQL using SSIS 2008
  30. How to avoid SSIS FTP task from failing when there are no files to download?

Leave a Comment