In my last post, we used the Import and Export wizard to create a simple Load process. We loaded names.csv into a simple table. Let’s open Visual Studio 2008 (Also known as Business Intelligence Development Studio when you’re talking about the version that comes with SQL Server 2008 R2. Once you’ve started it, open the example1.dtsx file we created last time.
Again, it doesn’t look like much, but the icon you see here, the Data Flow Task is the fundamental unit. It moves data from one point to another. If you want to see the details of the Data Flow, you can either double click on the icon, or click on the second tab called “Data Flow”, it’s right under the tab that says “Example1.dtsx”.
Now you can see a little more detail. You can see the source and the destination. You can see a flat file source (our csv), and you can see an OLE DB Destination.
You can see a green arrow pointing from the source to the destination. That line represents the logic “All rows that are successfully read in, will be copied into the OLE DB Destination.
Feel free to look at the Tool box tab on the left, check out the different Data Flow Sources. One day you’ll end up pulling data from each of these. I’ve already had to pull data from Excel files, XML files, and All sorts of OLE DB connections (from SQL to IBM Universe databases).
Skim through the Data Flow Transformations section. Notice a few in particular Pivot, Row Count, Unpivot, UNION, these will become useful when you have to make some structure changes to the data before storing the results in your destination. The Pivot and Unpivot have been the most used for me.
Finally you have your Data Flow Destinations, basically the mates for your Sources. You can stick the data anywhere you can make a OLE DB connection. Once you’ve explored a bit, click the Control Flow tab once more, and notice how the toolbar changes context.
The tools available to you now have to do with the work flow. The steps you’ll have to take as a part of the ETL process you’re modeling. FTP and File system tasks live here, as do Execute SQL, and script tasks. Script tasks will be covered in a later lesson, but this is where I do most of my advanced processing. You can write straight up code here. So just about anything you could do in an executable, you could do here.
Once you feel familiar with the environment, you should be ready to take a look at my basic SSIS Package structure. This is my basic recipe for a SSIS package. It’s nothing fancy, but almost every package I’ve ever had to build has fit within this structure with very little stretching. I’d like to share it with you, so you can get more comfortable with how to work in SSIS.
Any questions? If so, send them in! I’m here to help!