Agile BI Community

Thanks to the Columbia Enterprise Developer’s Guild for letting me present last night. The audience was great, and I got a lot of good questions. Several people asked if the samples could be made available, and I also had a request to post the slides for some people who weren’t able to make it. So, here they are. I’ve posted them to my SkyDrive ...

A recent post on the SSIS forums was asking about creating multiple rows in a text output file from a single row in the data flow. Given a set of rows like this: John Smith 1/1/1900 Value A Jane Smith 12/1/1900 Value B the poster wanted this output in the text file: ...

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 ...