I only recently learned about SSIS packages, a feature that was introduced long ago with SQL Server 2005. They were presented to me as a solution to a customer problem that required millions of records from separate vendor databases to be archived and up-to-date in a single location. I wasn’t the brains behind that solution, but I had a similar, less-business-critical situation and I decided to give it a shot.
There’s a small learning curve, but after a few hours I was able to extract hundreds of thousands of records from a database on one server and insert them into a different table in a different database on a different server. One of the most exciting parts is that it was able to do all of that in about 10 seconds!
I was so impressed with what I’d accomplished that I wanted to document my journey in this mini-tutorial. Disclaimer: I’m by no stretch any sort of expert with SSIS nor do I claim to be any sort of SQL guru–I’m just a dude trying to move some data.
I started by creating my extraction query in SQL Server Management Studio. I actually started by using SSMS’s “script as INSERT” feature on my destination table and evolved that into my data extraction query. The nice thing about that approach is that the column mapping for the insert will be handled automagically by SSIS. I didn’t know that at the time, but it worked out well!
Once I had my SQL extraction query, it was time to start with the SSIS:
- Open SQL Server Business Intelligence Development Studio from Start > All Programs > SQL Server 2008
- Create new project
- Business Intelligence Projects > Integration Services Project
When the project opens, you have a canvas-like screen with several tabs at the top. One of the things that I did not realize right away is that toolbox items change depending on which tab you’re on. Since I wanted to move data between databases, I eventually found and decided to use the Data Flow Task. I dragged it from the toolbar onto the design surface of my Control Flow. Double-clicking the Data Flow Task took me to the Data Flow tab, where I added the meat.
Since I was extracting data from one database to be inserted into another, the logical step was to figure out how to extract that data. From the Data Flow tab, I found a OLE DB Source control in the toolbox. I double-clicked the control was it was on the design surface and was able to add my database connection and provide the SQL statement I wrote previously as the source. Pretty easy!
There was another control in my toolbox called OLE DB Destination–sounds perfect. Once again, I was able to double-click the control from the design surface to access properties like database connection and destination table. The final step was to drag the arrow from OLE DB Source to OLE DB Destination. I ran the package using the standard Visual Studio debugger controls, and it worked great!
The only other cool thing I found was that I could run the package from outside of Visual Studio, and I was given the ability to do things like update database connections. That seems great for package re-usability and sharing! I’ve definitely only scratched the surface of SSIS, but I will certainly keep it in mind for future problems!
Like this:
Like Loading...
adamprescott.net 