Tuesday, January 4, 2011

Recommended SSIS blog

Been following a blog series on SSIS, with recent topic:
31 Days of SSIS - Folder Structures

My response:
Can tell you use an organized layout, and some of the concepts under workfiles I hadn’t
considered before.

What do I do differently?
Well the concept of WorkFiles is outside of the ‘SSIS’ hierachy, in fact by the time it gets to production on an entirely different drive. One reason is that the WorkFiles lives on whether another developer was to follow me and structure packages differently… or for that matter a different ETL tool was used, or even reverted to importing ‘manually’. Second reason is the source control and permissions alluded to. Non-developers in my environment need access to the WorkFiles, on a regular basis to drop off files, and in rarer situations, to see what files have been imported. Confuses me to much to have this mixed in with folders that need source code control.

Under my equivalant to WorkFiles/Project, there is a ‘Samples’ folder which contains samples (i.e. templates) of what the files are ‘supposed’ to look like. Especially with Excel files, i’ve found a groove where if the package uses a file under ‘WorkFiles/Project/Samples’ at design, then have it loop through ‘WorkFiles/Project’ at execute time. This can be challenging though if the ‘Samples’ get out of sync with the package.