Microsoft SQL Server Integration Services

Recently I worked on a project where I needed to import test data from a chemical process contained in text files into a MS SQL Server database. I was about to start a C# .NET project to do this task, but I thought there has to be a quicker and easier way to do this. That is how I stumbled upon SQL Server Integration Services (SSIS). SSIS can do exactly what I needed. This tool is a component included in all versions of Microsoft SQL Server 2005 (except Express and Workgroup) that can handle data migration tasks. It's a data warehousing tool that can extract, transfer and load (ETL) data. SSIS creates "packages" that can move data from one source to a destination. SSIS packages are created using the SQL Server Business Intelligence Development Studio which is basically a visual tool based on Visual Studio. It allows developers to drag and drop and graphically program an ETL package.

My project consisted of reading text files from a folder every hour, inserting the text file's data into the SQL database, compressing the text files and deleting the original text files. If the folder was empty, an email was to be sent for notification. I quickly accomplished this task by using the built in tools in SSIS: File System task, Send Email task, ForEach Loop Container, OLE DB Command (to execute SQL commands). However, I did have to import a third party task to compress the text files as this is not included with SSIS out of the box. After I created, tested and deployed my package, I scheduled SQL Server Agent to execute it every hour.

Using SSIS I easily completed this project. I would recommend using SSIS for simple ETL tasks. For more complicated tasks, a Script Task which allows VB .NET scripting is also included.

For more information, check out http://msdn.microsoft.com/en-us/library/ms141026.aspx