Tutorial 1: Creating Your First SSIS Package Using SQL Server 2005 Beta 2

Download code and sample file from our downloads section : (Tutorial 1 - Creating Your First SSIS Package)

The learning curve of SQL Server 2005 is like going from SQL Server 6.5 to 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 DTS package and guide you through some of the features.

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. This tutorial is a variant of a sample that’s provided in the Beta 2 installation.

Before you begin, download the sample text file here that we use in this sample.

1. Open the Business Intelligence Development Studio (Start | Programs | Microsoft SQL Server 2005). This is where you will typically create your DTS packages now. The environment as you can see is Visual Studio 2005 Beta 1.
2. Select File | New | Project
3. Under the Business Intelligence Projects Project Type, select Data Transformation Project. Call the project Tutorial1 under the Name text box. Click OK. This will create a new project and your first blank package called Package.dtsx.
4. Double-click on Package.dtsx in the right-pane (Solution Explorer) under SSIS Packages.
5. This will open the new Package Designer. You should start in the Control Flow tab. The Control Flow tab is where you start your workflow. Each time you transform data move data, you will automatically be redirected to the Data Flow tab. We’ll cover much more about these tabs shortly.
6. There are lots of ways of doing data connections. You can create a shared connection that works across multiple SSIS packages or you can create one that has the scope of the existing package. In our example here, let’s start simple and just do a local connection. To do this, right-click in the Connections tab below and select New Flat File Connection.
7. You are then taken to the New Flat File Connection dialog box. By now, you should have downloaded the sample file. If you haven’t downloaded the file, please download it here. Name this connection Sample File and no description is needed but it is useful metadata if you’d like. The file name should point to C:\DTS\SampleText.txt or wherever you placed the flat file. Another area that will be different than the defaults is to change the text qualifier to double-quotes (“). Here’s my properties screen.
   1. Before clicking OK, go through the other areas of the dialog box like the Columns screen.
   2. This part is very important and don’t skip this step as the rest of the tasks will rely on this. Go to the Column Properties screen. By selecting each column, you can see what data type SSIS is using for that column in the flat file. Select the column TransactionDate then under data type select date [DT_DATE]. A really cool feature here is you can click Suggest Type to have DTS sample the data and predict what you need. When it suggests the data type, it will predict for all the columns and not just the one you have selected. Click Suggest and OK to finish the rest of the columns. One thing I have noticed is you want to make sure the Quantity column is a 4-byte integer and not a 2-byte integer.
   3. Click Preview to confirm that the settings you choose are good.
   4. After you see data on the Preview section, then you’re ready to select OK.

8. You’ll now see the new connection at the bottom. We’ll create another one of these later to show you another method of doing this.
9. In the far left pane, you should be able to see the Toolbox tab. Hover over Toolbox and click and drag the Data Flow Task control onto the Control Flow tab. (You can optionally pin the toolbox to your design environment by clicking the push pin.)
10. Right click on the newly created task and select Rename. Type “Transform Flat File” as the new task name. (You can also left click on the task and modify the Name property in the Properties pane on the right). 
11. Right-click on the Data Flow task you just renamed and select Edit. This will take you to the Data Flow tab.
12. Once on the Data Flow tab, drag and drop the Flat File Source data flow item onto the design pane from your Toolbox in the left.
    1. You may want to optionally rename the data flow item by right-clicking the item and selecting Rename. I renamed mine to Sample File.

13. 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.