The learning curve of SQL Server 2005 is like going from SQL Server 2000. Maybe even worse for DTS. Any knowledge of DTS you have now can be thrown out in SQL Server 2005. When I started to create my first DTS package, it was quite a humbling experience as I clumsily navigated around the interface. This tutorial will walk you through the creation of your first simple SSIS package and guide you through some of the features.
Download the Sample File
The goal of this tutorial is to transform data inline from a flat file to another flat file without ever having to stage the data like you would typically have to do in SQL Server 2000. We’re going to take a sample file with just under 90,000 records and aggregate it, group it and sort it before inserting it into a new text file. You can also insert into a SQL Server just as easy. Traditionally in SQL Server 2000 or 7.0, you would have to insert data into a staging area, use a query to aggregate, sort and group it and then write the flat file out. This query won’t require any knowledge of how to query a SQL Server system.
Before you begin, download the sample text file attached to this article.
Right-click on Sample File data flow item and select Edit. Under the Connection option in the left pane ensure that Sample File is selected for your connection. Next, go to the Columns section and uncheck most of the columns, leaving only ProductID, TransactionDate, Quantity and ActualCost.
Next, click and drag the Derived Column data flow item over to the design pane under the Sample File item. Again, right-click on the Derived Column item and select Rename. Rename it to Total Cost. Left-click the Sample File data item and you’ll see a red and green arrow pointing down. Left-click the green arrow and drag it onto the Total Cost item. With the two items connected, go ahead and right-click on the Total Cost item and select Edit. In the Derived Column Editor, drill into the Columns tree in the right and drag the Quantity and ActualCost columns into the Expression column below as shown in the below screen shot. Make the Expression Column Quantity * ActualCost. If you receive a data type error, then you may not have followed step 7b. Change the name of the Derived column to TotalCost and the data type to Currency as shown below. After you’ve done this, click OK. What this does is create an extra column that can be seen by the destination transformation and will sum up those two columns. Next, we need to roll up the data. Drag and drop the Aggregate data item from the Toolbox into the design pane. Again, connect the Total Cost data item to the new data item by connecting the green line as you did before in step 15. Let’s rename this data item to Group by Product. Go to the Aggregate Editor by right-clicking on Group by Product and selecting Edit. Then check the ProductID, TransactionDate, Quantity and TotalCost columns. This will partially fill in the Input Column area below. You will then need to specify what type operation you’d like to do for each of the columns. We want to group on ProductID so you need to ensure that Group By is selected. For Quantity select Sum and for TotalCost select Sum. Finally select Maximum for the TransactionDate Input Column. I also changed the Output Aliases for two of the columns to give a unique name to the new data. Essentially, what I’m doing here is grouping all the data by ProductID, determining the maximum date from the TransactionDate column and summing up all the quantity and cost. Click OK to save your work in this screen. Notice: that the TotalCost column is available to you here. So as you add derived columns, they show up in later data items.We now want to sort the data so it makes our new flat file easy to read. To do this click and drag the Sort data item from the Toolbox and rename it to Order by ProductID. Make sure it’s connected to the Group by Product item by connecting the green arrow from the Group by Product item. Go to edit this data item as we did the other items. Check ProductID and ensure that’s being order ascending. Click OK to exit. Lastly, we need to output all this work to another flat file. Click and drag Flat File Destination (it’s not in alphabetical order) over from the Toolbox. Ensure it’s connected to the Order by ProductID item by dragging the green arrow from that item. Click edit to modify the properties of the Flat File Destination. Click New to create a new flat file connection Name this connection SummaryByProductID (see below for other info). Notice in the Preview section, you can see all the output columns from the earlier items. Click OK In the Mappings section, ensure that the transformation looks fine. Click OK.You’re now done. To execute the package hit the F5 button or click Start under the Debug menu. You’ll then see the record counts increasing. Here’s an example of my output. I hope this got you past your first SSIS package. In the next tutorial, I’ll show you how to add some basic error handling to the package so you can easily get past something like what I mentioned in the last paragraph.
In the Mappings section, ensure that the transformation looks fine. Click OK.You’re now done. To execute the package hit the F5 button or click Start under the Debug menu. You’ll then see the record counts increasing. Here’s an example of my output. I hope this got you past your first SSIS package. In the next tutorial, I’ll show you how to add some basic error handling to the package so you can easily get past something like what I mentioned in the last paragraph.
I hope this got you past your first SSIS package. In the next tutorial, I’ll show you how to add some basic error handling to the package so you can easily get past something like what I mentioned in the last paragraph.