Maintaining a Type 1 Slowly Changing Dimension (SCD) using T-SQL
A few days ago, one of our SSIS packages that maintained a Type 1 Slowly Changing Dimension (SCD) of about 1 million rows crept up to 15 minutes of runtime. Now this doesn't sound too bad, but this is part of our hourly batches, so 15 minutes is 25% of our entire processing window. The package was using the Slowly Changing Dimension Wizard transformation - we were doing the standard OLEDB Source (which basically represented how the SCD "should" look) and then sending it to the SCD transform and letting it figure out what needed to be inserted and updated. One option was to switch to lookups instead of the SCD wizard to speed things up, maybe even some fancy checksum voodoo for the updates (see http://blog.stevienova.com/2008/11/22/ssis-slowly-changing-dimensions-with-checksum/ for an example). Then after thinking about it a little more - why are we sending a million rows down the pipeline every hour? We know only a small percentage of these are new - and another small percentage needs to be updated. Well, we can just write a quick SQL query to get us just those sets and the package would be much more efficient!
Wait a tick - why would we give the rows to SSIS if all it is going to do insert one set and update the other? Let's just do it all in T-SQL:
Adding Windows Event Log Logging To Existing SSIS Packages via C#
While working with some SSIS logging, it occured to me that there's really no good way to globally apply the same logging to all packages on your server. For our production SSIS server, it would make sense to log all OnError events to the Application Event log. Then, whatever event log monitoring app you use can notify you of the package error (with details!). Thus, this app was born - an app to remove pre-existing LogProviders and add in the intended LogProvider and options.
This is really just an example of the implementation that worked for my purposes - I'm sure some of you would prefer to log elsewhere. This should be enough to get you going. Please drop a line in the Comments if you post alternative approaches.
Usage
SSISForcedLogging.Console.exe "Z:\SSIS Packages" or SSISForcedLogging.Console.Exe "Z:\SSIS Packages\Package1.dtsx"
Exporting SSIS Packages Stored on the Server (MSDB) to .DTSX (XML)
We wanted to export an SSIS package that was stored on the server in the msdb.dbo.sysdtspackages90 table to a .dtsx file so we could poke at it.
Here's what we came up with:
DECLARE @SQLcommand VARCHAR(MAX) SET @SQLcommand = 'bcp "SELECT cast(cast(packagedata as varbinary(max)) as varchar(max)) FROM msdb.dbo.sysdtspackages90 WHERE name=''PackageName''" queryout "c:\output.dtsx" -T -c' EXEC xp_cmdshell @SQLcommand
Alternatively, you can just Export the package via SSMS 
Social Networks
What I'm Doing...
- @scaleovenstove @jbollinger let's do it. "BItter" ;-) in reply to scaleovenstove 3 weeks ago
- @joekieffer good luck! in reply to joekieffer 2010-07-10
- @ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
- @michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
- Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
- More updates...
Recent Posts
- Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
- Locating Rampant Database Growth
- Script sp_configure Output To Migrate Settings Between SQL Servers
- Script RESTORE DATABASE … WITH MOVE Stub
- VersionOne Nag: Reminding Us To Burn Our Points