Using SSIS ETL Methodology for CAATs and Data Mining

I’d like to mention that Microsoft SQL Server has extract-transform-load (ETL) capabilities built into it called SQL Server Integration Services (SSIS).  SSIS uses a Graphical User Interface very similar to what you might see when building applications with Visual Basic or VB .NET.

With SSIS, you can extract data from almost every kind of data source: text files, CSV files, MS Excel spreadsheets, database tables using ODBC or OLEDB connectors, etc.
SSIS has a number of tools for transforming that data into a usable format. Do you need to filter certain data out, or concatenate two fields together, or create a calculated field based on several other fields? It can do that.
Finally, it can load data into almost every kind of format: text files, CSV files, MS Excel spreadsheets, database table using ODBC or OLEDB connectors, etc.
Since SSIS is a part of SQL Server, you can use SQL Server’s Scheduling service to schedule SSIS jobs. I use SSIS to initiate SFTP transfers of data files to and from several of our vendor’s SFTP sites on a nightly basis. It’s a great tool that’s available for the price of a SQL Server installation. I really like working with SSIS.
Just for clarity, this service used to be called Data Transformation Services (DTS) from SQL Server 7.0 through SQL Server 2000.
Anyway, my point about SSIS is that it is a powerfull, moderately inexpensive solution and is fairly easy to use. It may allow you to take your company’s data and transform it into a format that could be used by a CAATT.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>