If you are new to SSIS and would like to create your first SSIS package then below is a simple example with step by step instructions which will show you how to create an SSIS package in bids. I will use SQL Server Integration Services 2008 R2 and Visual Studio 2008 (instructions should be very similar for 2008 and 2005). This tutorial is for beginners and will not be following best practise which includes using variables and package configuration. At the end of tutorial I will post extra materials (once I create them) which will include best practise and package deployment.
If you need SSIS Overview before you start than please visit the following link SSIS Package
Note: We have video version of this tutorial. Please click the button below otherwise scroll down to read text based tutorial.
Create SSIS Package – Purpose
The purpose of this package is to load employee data from a CSV file into SQL Server database new employee table.
Create SSIS Package – Prerequisites
In order to complete this tutorial you will need:
-
SQL Server 2008 R2 with default localhost instance and BIDS (should work on 2008 and 2005)
-
Employee CSV file which you should save in location you will remember. Download Employee CSV file
How to create an SSIS Package
First create SSIS Project using BIDS (For more information visit Create SSIS Project)
Below is example of an empty package. I have highlighted the elements we will use and briefly discuss it below (you can ignore the rest):
Solution Explorer - on the right you see solution explorer with your SSIS project (first icon from top). If you don’t have it go to view//solution explorer. In majority of cases you will use SSIS Packages only. The rest is not used in practise (best practise).
Package tab - In middle we have our ‘package.dtsx’ opened which contains control flow, data flow that we will use.
Toolbox - This shows ‘tools’ (items/tasks) that we can use to build our ETL package. Toolbox is different for control flow and data flow tabs in the package.
Control Flow - Here you will be able to control your execution steps. For example you can log certain information before you start the data transfer, you can check if file exists, and you can send an email when a package fails or finishes. In here you will also add a task to move data from source to destination however you will use data flow tab to configure.
Data Flow - This is used to extra source data and define destination. During the "data flow" you can perform all sorts of transformation for instance create new calculation fields, perform aggregations and many more.
Let’s get to work.
Make sure you are in control flow tab in SSIS Package designer and in the toolbox find data flow and drag into empty space in control flow pane.
Right click ‘data flow task’ that you dragged and rename it to ‘Employee Load’ (and hit enter to get out from edit mode)
Double click it ‘Employee Load’ data flow (ensure the box is not selected; otherwise double click will work like rename). Notice that SSIS automatically goes to data flow task where you can configure the data flow.
See below screenshot. Which shows that we are not in Data Flow tab and notice the data flow task drop down box which says ‘Employee Load’. You can have multiple data flow items in control flow so this drop down box allows you to change it.
From the toolbox (while in data flow tab) drag ‘FlatFile Source’ into empty space.
Right click the source and select rename. Type ‘Employee CSV Source’.
Double click the ‘Employee CSV Source’. A dialog box will appear with header name 'Flat File Source Editor'.
Next we will create SSIS Package connection which will be stored in the package and will connect to the CSV file. In order to that click the New… button.
Type the connection manager name and description
Click browse button and find the employee.csv file (by default you will see on *.txt file change it *.csv files)
Once you back ‘tick’ Column names in the first data row
You should the warning that states that you columns are not defined. Simply click columns which will set it for you (default settings should be fine).
OK button should be enabled now so click it to complete the process.
On the first dialog box connection manager should say ‘EmployeeCSV’ click OK to close the dialog box.
Now from the toolbox let’s drag OLE DB destination into data flow empty space and rename it to ‘Employee Table’ (OLE DB Destination in toolbox is in Data Flow Destination tab in toolbox…. I thought I will clarify that as it is easy to pick OLE DB source which is not what we want.)
Now we are going to create ‘data path’ which means that we define source and its destination. We will do that by clicking source (once). You should see green arrow. Click it (once or press and hold) and move it over destination (click or release mouse). You created "data path" in SSIS Package (Data Flow).
Double click ‘Employee Table’ Destination.
Create new connection by clicking ‘new…’ button and new… button again on another dialog box pops up.
Put server name. If you are connecting to local server type localhost
Select database from drop down box and click OK on all dialog boxes to confirm your choices.
Now that new connection is selected. We will create destination table. Notice that I highlighted data access mode with value table or view – fast load this is an important value that makes the load very quick, make sure you remember this one.
To create new table click New… for the table/view drop down box (see below), change the table name to [Employee] and click ok.
To finish the process click mappings that will create mapping between source fields and destination fields and click OK
Let’s test our SSIS Package. Click run (play button on toolbar). And you should see that extract from source worked (green), arrows should show 2 rows from our CSV file and destination should also go green which means it successfully loaded 2 rows from the file.
I hope you found this tutorial useful. If you have any questions or comments feel free to post them below.
Take care
Emil
