CODEWITHSUNDEEP
ssis
TrevorGoodchild
TrevorGoodchild

Reputation: 1060

SSIS - Reading from a multi part text file

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.

Upvotes: 0

Views: 410

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

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.

Upvotes: 2

Related Questions