Agile BI Community

A common question with SSIS is how to handle adding headers and footers to a flat file when the format is different that the data rows. This gets more difficult when the header or footer needs to contain data based on the actual data rows, such as a total or a record count. Basically, this can be accomplished by creating the header or footer in a ...

A recent post on the MSDN forums brought up an interesting problem. The poster was attempting to process a folder of files, all of which had the same format, but some were encoded as ASCII (ANSI, DT_STR, or varchar, if you like the SQL Server data types) and some were encoded as Unicode (DT_WSTR, nvarchar). The initial issue was determining ...

It's been tough finding time to write any content recently, but this was a scenario that came up recently both at work and on the forums, so I decided to write it up. It's another variation on processing flat files (they seem to be endless). This scenario deals with an input file that contains two types of records, headers and details. This ...

In my previous post, I discussed how to use a script destination to output multiple flat files from a single source. In it, I mentioned that I would post an alternate approach, that didn't require any scripting. So, here it is. This is using the same example data as the previous post, a source file that looks ...

Yesterday I posted about using a destination script component to write XML files from a data flow. Today, I'm continuing in the same vein with a post about writing multiple destination files from a destination script component. The inspiration for this was a post on the MSDN forums where a developer described a scenario where he had a single ...

I often hear comments along the lines of ''Why doesn't SSIS have an XML destination?''. Unfortunately, I don't know the answer to that, but I can show you how to implement your own XML destination fairly easily. Thanks to the magic of the script component, it doesn't take much work at all. This was also a good opportunity for me to use a ...

There's been a few questions on the forums recently about taking column values and pivoting them to rows. For example, I might have an input file with 5 columns: a key column and 4 category columns. Key; C1; C2; C3; C41;1;2;3;42;1;2;3;43;1;2;3;44;1;2;3;4 and I'd like an output that has 2 columns (the key and the category ...

In Handling Flat Files with Varying Numbers of Columns, I showed an example of parsing a flat file with an inconsistent number of columns. I used a script component, but Jamie commented that the same thing could be accomplished through a Conditional Split and Derived Column transform. So, here's part 2. I added a new data flow to the same ...

5/15 Update - I added Part 2 to show how to do the same thing with a Conditional Split and a Derived Column transform, per Jamie's feedback (see the comments on this post). A common question on the forums has been how to handle flat files that have a varying number of columns. For example, one row contains 3 columns, and another ...