BI Debugging Thoughts – Part I – SSIS

Color coding is an interesting thing. Through our lives, through traffic signals, and through the occasional story book we’ve learnt a few important things:

1. Green is good.

2. Red is bad.

3. Yellow we can live with…

 

This lesson is being enforced right now as I’m debugging some SSIS packages!

(See, the Red / Yellow / Green KPI indicator thinking runs deep in the Microsoft BI stack!)

 

This has got me thinking, is it possible to actually document an approach to debugging BI Solutions? Quite a broad subject area to cover, as BI in the Microsoft sense includes a number of inter-related components.

 

So, I thought I’d start with the ETL pieces, and jot down a few areas that I tend to look into when the SSIS components go Red…

1. Progress Tab.

 – Yip, the humble SSIS progress tab. This is an incredibly verbose logging area, and it normally gives a great indication as to the source of errors.

     – This is not always available, as sometimes we need to debug packages that ran through the SQL Agent… so it’s always advisable to implement good SSIS Logging. (which will give the same effect)

 

2. Metadata, Metadata, Metadata and the Intrepid Data Types

 – SSIS is incredibly sensitive to changes in schemas and data types. And in fact, may not execute if some of the underlyinh data structures have changed. (without properly updating the SSIS data flow components.

 – Simple enough to fix, simply open the relevant Data Flow Component and SSIS normally refreshes its metadata on open.

 – In this space, it’s worth noting that SSIS doesn’t like inserting Unicode into Varchar data type fields, so you may need some data type conversions in your flows.

(don’t feel bad, the performance overhead is minimal compared to the overhead of dealing with NVarchar at the database level, in my experience)

 

3. Script Component Debugging

 – This is actually what I’m doing right now. Quite a tricky thing to do on a x64 platform, as the script components are precompiled, and you can’t step through them

 – In this case we have resorted to an approach where all potentially dangerous commands are wrapped in a try {} catch {} block, and the catch {} logs the error through to a text file.

(of course this text file could be the progress tab, a message box or the event log itself; either way it just needs to be set up.)

 – My initial rule of thumb here would be… don’t use the Script Component unless you really need to. SSIS has a variety of components that can be used to do things like FTP, file handling and cube processing… so, use what exists where possible before re-inventing the wheel!

 

4. 32 / 64 bit

 – Talking about bits, it’s important to note there are a few things in SSIS that don’t upgrade very well from 32 bit to 64 bit platform.

 – A good example is the Excel Source. (

 

5. Event Log

 – Often overlooked, and yet incredibly useful area to hunt through. It is amazing how many useful exception messages are found in the Application Log.

 

6. Green isn’t always Good 🙂

 – Yip, even though a package may compile and execute with no visible structural errors, it is always important to balance the end results by hand or by script to make sure that things haven’t gone a little wierd in translation.

 

Oh look, my package is finally all green!! Wonderful.