Everybody talks about Big Data, like it’s either a monolithic thing like Ayres Rock in Australia that has been around since before there were people. You can almost imagine companies springing up around deposits of Big Data like gold miners along a stream. Could that be why so many people talk so much about data mining? I think not, because Big Data is almost an organic thing, like a little acorn that grows into a might oak, and like a seed it needs to be fed and nurtured.
While there are multiple methods of feeding a database, one of the more popular is SSIS (SQL Server Integration Services). If you’re unfamiliar with SSIS, it’s a data migration tool that is part of SQL Server Standard Edition or above, which, unfortunately, means that it isn’t part of the free Express Edition of SQL Server.
In this blog, I’ll walk you along the creation of a data project using SSIS. To play along at home, in addition to SSIS, you’ll need Microsoft’s Adventure Works Sample database and Microsoft Excel.
In the Windows start menu, SSIS can be found under Microsoft SQL Server 2012 as SQL Server Data Tools. Clicking it and selecting new project brings up the New Project dialog shown in Figure A.
Click on figures to enlarge.
When a new project is created, you’re presented with the IDE shown in Figure B. Notice various tabs along the top, the SSIS Toolbox with its tasks on the left and the Connection Managers along the bottom.
Looks kind of familiar, doesn’t it–like Visual Studio? That’s because it is Visual Studio. But don’t let that scare you, if you’re not really a .Net programmer. With the exception of Connection Managers, this IDE is basically drag and drop.
Now about those Connection Managers: they identify what kind of inputs and outputs are available for processing, as listed in Table A.
|ADO||Connection manager for ADO connections|
|ADO.NET||Connection manager for ADO.NET connections|
|CACHE||Connection manager for cache|
|DQS||Connection manager for DQS server|
|EXCEL||Connection manager for Excel files|
|FILE||Connection manager for files|
|FLATFILE||Connection manager for flat files|
|FTP||Connection manager for FTP connections|
|HTTP||Connection manager for HTTP connections|
|MSMQ||Connection manager for Message Queue task|
|MSOLAP100||Connection manager for Analysis Services connections|
|MULTIFILE||Connection manager for multiple files|
|MULITFLATFILE||Connection manager for multiple flat files|
|ODBC||Connection manager for ODBC connections|
|OLEDB||Connection manager for OLE DB connections|
|SMOServer||Connection manager for SQL Server transfer tasks|
|SMTP||Connection manager for SQL Server Compact connections|
|WMI||Connection manager for WMI connections|
Using the list above, let’s say, rather than feed the beast, we’d like to drag some information like addresses and put them in an Excel spreadsheet. So, we’ll need a Database Connection Manager, like the OLE Connection Manager shown in Figure C. For the Excel spreadsheet, use an Excel Connection Manager, an example of which is displayed in Figure D.
There is one caveat: If you’re using a 64-Bit edition of Windows, you’ll need to make sure that you do not use the 64-Bit runtime. The package will execute badly. Here’s how to make sure this doesn’t happen. Open up the project’s property pages (it’s the last item under the Project tab). Next, click Configuration Properties and Debugging. Under Debugging Options, set Run64BitRuntime to False as shown in Figure E, and you’re good to go.
There is one more item to remember. All of the record-level processing is done in Data Flow tasks, not in the Control Flow. The Control Flow is where you control the flow using the For Loop Container, the Foreach Loop Container, or the Sequence Container. But since our project is so simple, all we need to do is drag a Data Flow Task onto the Control Flow, giving us something that looks like Figure F.
Now it’s time to click on the Data Flow Task or the Data Flow Tab, and open up the Data Flow. While it’s similar to the Control Flow, the SSIS toolbox offers some different tools. The two that we need are under Other Sources and Other Destinations, namely the OLE DB Source and the Excel Destination. Figure G shows what this looks like.
Now drag an OLE DB Source onto the page and double-click on it. Up comes the OLE DB Source Editor. Here is where you use the OLE DB Connection Manager and select which table or stored procedure or SQL to use in order to retrieve data, like Figure H shows. This is also where you can pick the columns that you want to retrieve. Finally, here is where the error row handling can be defined.
Once the source is defined drag an Excel Destination onto the Data Flow, then click on the OLE DB Source and drag the arrow to the Excel Destination, getting something that looks like Figure I.
This last step is where the Excel Destination is defined using the Excel Connection Manager. It looks a lot like setting up the OLE DB Source as Figure J and Figure K show.
Now all that is left is to run the package by clicking the obligatory green triangle and, if you don’t see any red, looking at the result in Excel, Figure L.
Now, this really isn’t a fair test of everything that is possible with SSIS; that would be somewhat longer than an article. It does show something of what is possible. Consider, for a moment, what if the input was Excel and the output was a database table, or a flat file and a table, or an FTP and a table, or what if both were tables?
The Data Flow doesn’t have to be a straight move; there are a bunch of tools that we never even looked at. You can sum, split, sort, fuzzy group, lookup, pivot, unpivot and even add your own code in C# or Visual Basic .Net.