0

I'm trying to build an SSIS package that reads from a text file and outputs into another text file. The catch is that the file I'm trying to read from has multiple sections and I can't find anything that shows how to do that.

The file looks like this:

[sectionA]
key1=value1
key2=value2
key3=value3

[sectionB]
key4=value4
key5=value5
key6=value6

I started with a couple of tutorials that read from a flat file source but the data gets pulled into an equally ugly table. Hoping someone has some input on this.

TrevorGoodchild
  • 978
  • 2
  • 23
  • 49

1 Answers1

2

The SSIS Flat File Connection is built for speed so it doesnt allow for niceties like that.

I would still use the Flat File Connection but just load all the data into a single, wide NVARCHAR column in a SQL table. I would add an IDENTITY column to that table to get a relative Row Number.

Then I would add downstream tasks using SQL to select by Sections e.g. for Section A rows:

WHERE File_Row_Number > ( SELECT MIN ( File_Row_Number ) FROM Staging_Table WHERE nvarchar_column = '[sectionA]' ) AND File_Row_Number < ( SELECT MIN ( File_Row_Number ) FROM Staging_Table WHERE nvarchar_column = '[sectionB]' )

If the split requirements are as simple as those shown I might attempt them in SQL e.g.

How do I split a string so I can access item x?

But I would probably lean towards using Strings.Split in a Script Task where the code will be simpler and safer.

Community
  • 1
  • 1
Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Hi Mike, That's where I am right now. I've loaded it all into a single column but I'm using a script task to navigate through the text. – TrevorGoodchild May 13 '14 at 18:41
  • Took some time, and frustrating the heck out of myself trying to do it in SSIS, but this solution worked perfectly once I understood it. Thanks Mike! – TrevorGoodchild May 19 '14 at 16:50