Microsoft SQL Server Integration Services (SSIS) is really a great tool in my opinion. Based on my experience to date using it (pre-SQL 2005 release, with the first CTP in the first quarter of 2005 me and an colleague Tom Davis were doing “Tech Nights” at Software Architects, now Sogeti…wow I’ve used it six years now I guess), it’s solid, and a substantial improvement over DTS. Is it what we want it to be yet? Probably not, but it will get there someday and hopefully be as feature-rich as the other popular ETL tools. One thing that it allows the practitioner to do is various type of customization, either with the script task, script component, custom component, custom assemblies, or programming the API directly. This is a good thing and a bad thing. Bad because many experienced ETL developers subscribe to the “hands off the keyboard” approach to ETL; this rules out coding of any kind. There are various reasons for this that I will neither argue nor hash out here, but suffice it to say that traditional data warehouse folks who used the tools for many years relied many on delivered tasks and components and did not look to code and customize as a general rule. Good, on the other hand, because if the particular tool is not available…just build it!
Imagine for a moment a process that requires you to create 500 data flows, either individual text file source to database destination, or database table to text file. How would you split it up to manage it? 50 packages with ten flows? 100 packages with five? Or maybe one package with 500? You get the picture, and it’s not pretty. Not only that, but after you build it, try managing it right? Sounds like a nightmare to me. I’m going to show you a way to create all of this functionality in a single package, and you never need to map a column or drag a connection one. All of the transformations are handled, all of the data types and string lengths taken care of, all built dynamically and parameter-driven by a set of database tables. Sweet!
This series - Dynamic SSIS Packages- is about programming the SSIS Application Programming Interface, or API. I thought about sending this off to SQLServerCentral, one of these online tech magazines, or some of the other outlet, but if you follow me at all, you know that I gave up on those sites and just post my stuff here. To my knowledge, there isn’t a tutorial on this out on the web, or a series of any kind yet, so that’s one reason that I wanted to put this together…maybe there is, but I thought that I would write this nevertheless with the hopes of helping someone out as well as learning more about it myself. Thanks much to Kenny Chung – a senior database developer from Dallas who created the particular concept and code in DTS and SSIS whom I worked with at Alcon – for letting me “take over” the code so to speak. Kenny is a great programmer and a good friend and I appreciate his insight and creativeness in building the processes that I will demonstrate in this series. These packages are truly “dynamic” in nature.
Here’s what I will blog about in this series in the coming weeks:
1. Introduction (this post)
2. Database design and setup
3. Database to a flat file
4. Flat file a database
5. A in-depth look at the code
I may have more or less so we’ll see how it goes. What I want to do right off (after design and setup), however, is get the code out for you to get it installed. After we have it up and running, then we’ll look at it closer to see what’s happening. I hate to do a step-by-step but leave out being able to actually see something happen, so I’ll give this method a try and you can let me know how it works for you.
My obligatory caveat: I do not recommend using custom API packages unless 1) there is a great need for customization, or 2) it substantially makes a series of tasks easier to manage. It is also nice to have someone in the department familiar with vb.net or c# code as well; you would not be advised to code something like this without someone available to debug or modify.
About ten years ago, I worked a SQL Server gig at Jobs.com. A co-worker there mentioned something one day about “Knowledge is Power”, but a few years ago I came up with my own saying, and that is “Sharing is Power”. Jobs.com actually went out of business (gee I wonder why) and the domain is now owned by Monster I believe. With that kind of thinking in the IT shop, go figure eh? Anyway, I look forward to sharing this concept, and while it’s not proprietary or new, I hope to present in such a way that you can gain a better understanding about this great gift of the SSIS API to create your own custom packages.
Thanks for reading,
Lee Everest
-------------------------------
4d1989c9-3e00-4a18-a003-c468be2c971f|0|.0