SSIS Import Flat Files with Headers

Well today I did something new 😉

First a little background information.
The project is called ‘DLT Containment’, here’s a short quote from the PID:
DLT stands for Die Level Traceability – this system contains all genealogy for our LED’s from EPI Reactor run through the LED location in the end-product we ship to a customer (full traceability).

I made a SSIS package that processes the logging files…BUT…they contain headers (see below).
It’s actually based upon the T-SQL code from my previous post.

First I’ll show the Destination table design:

Nothing that difficult:
– FST_Lot_ID = (believe it or not) WO NO
– FST_SubLot_ID = LOT NO
– FST_Date – Current datetime (smalldatetime..Grrrr)

I was struggling for quite a time on how I should do it.
After minutes (maybe an hour), I got an idea and worked on it.
Here’s the result of my brainstorm session:

Suggestions are welcome ofcourse.

Microsoft Contoso BI Demo Dataset for Retail Industry

A nice addition to your database collection is Microsoft’s Contoso (BI):

The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data.

Download the Microsoft Contoso BI Demo Dataset for Retail Industry here.

Multi value parameter within SSRS dataset (Command type: text)

Today I wanted to use my Multi-value parameter function within a SSRS dataset (Command type: text).
Guess what…it didn’t work!

After literally an hour, I found the answer….it’s not possible at all.
Instead SSRS basically ‘chops’ the multi value string into something like: ‘aa’,’ab’,’…’
My function requires a single string instead of many small ones, for example: ‘aa,ab,…’

Now you’re thinking, what about the Join-function? As in:
=JOIN(@MultiValue,”,”)
Which returns a single string like aa,ab (no quotes!)

Or if you wish:
=”‘”+JOIN(@MultiValue,”,”)+”‘”
Which returns a single string like ‘aa,ab’ (with quotes using dynamic string-manipulation)

Too bad….the JOIN-function isn’t available within the Dataset (nor is EXEC() btw)

The answer is actually quite simple, don’t use statements like
‘X IN (SELECT * FROM dbo.fn_MultiValue(@MultiValue,’,’))’
Instead use the dataset Filter (or List if you use cascading parameters in your report):