Experimenting with SSIS
I’ve been looking into tons of ETL products over the past few days. ETL simply means Extract, Transform, and Load. Of course, it doesn’t mean much unless you know what I’m talking about in the first place. ETL products are used to copy (Extract) data from one place and save (Load) it to another. Along the way, you might need to change (Transform) a few things around. Why didn’t they just call it CCSD (Copy Change Save Data). It makes more sense to me.
This is mainly done with databases. It has been done for a long time as well. Microsofts SQL database had something called Data Transformation Services. With the next version, they are changing the name to SQL Server Integration Services (SSIS).
You now develop your packages in Visual Studio.Net 2005. Everything is basically drag and drop. I just spent a while just trying to get my RSS file and saving it to a database. There is just so much to learn. I feel like a snail.
I was confused with what I was doing because I hadn’t done anything like this before. Fortunately, I found a brief tutorial to help me setup SSIS with RSS.
- Start a new SSIS project
- Add a Data Flow Task
- Switch to the Data Flow tab by double clicking the Data Flow task
- Add a XML Source to the data flow
- Double click the XML Source to edit it
- In the XML Source Editor, in the “Connection Manager” portion, make sure that the “XML File Location” is selected in the Data access mode. In the XML location text box, type in the url for a RSS feed (e.g., http://news.com.com/2547-1_3-0-5.xml)
- Click the “Generate XSD…” button, give the XSD file a name and save it. Make sure that it is correctly reflected in the XSD location text box.
- Click “Columns” in the list on the left hand side. If all goes well you should see the available columns from various outputs from the RSS feed.
- Select “item” from the Output Name dropdown box.
- Click “OK” to close the XML Source Editor.
Now you are ready to consume the RSS data.
There were more steps on the blog to start moving the data to a database, but this really helped me get my feet wet.
Tags: SQL, Database, Migration, DBA, SSIS, Yukon, SQL Server 2005, Visual Studio.Net, Visual Studio.Net 2005, Whidbey, RSS, Data, GUI, Programming