SQL Integration Services Homework Help, Assignment Help, Online Tutor, Tutoring

Submit Assignment

* *

Payment Online Assignment Help

Asp dot net Project Homework

Homework Help

sfsf

SQL Integration Services

SSIS and DTS Overview

SSIS is an ETL tool (Extract, Transform and Load) which is very much needed for the Data warehousing applications. Also SSIS is used to perform operations like loading the data based on the need, performing different transformations on the data like doing calculations (Sum, Average, etc.) and to define a workflow of how the process should flow and perform some tasks on the day to day activity.

Prior to SSIS, Data Transformation Services (DTS) in SQL Server 2000 performs the tasks with fewer features. With the introduction of SSIS in SQL Server 2005, many new features can be used. To develop your SSIS package, you need to get installed with the SQL Server Business Intelligence Development Studio which will be available as client tool when installing SQL Server Management Studio (SSMS).

SSMS and BIDS

SSMS provides different options to develop your SSIS package starting with Import and Export wizard with which you can copy the data from one server to the other or from one data source to the other. With these wizards, we can create a structure on how the data flow should happen and make a package and deploy it based on our needs to execute in any environment.

Business Intelligence Development Studio (BIDS) is a tool which can be used to develop the SSIS packages. BIDS is available with SQL Server as an interface which provides the developers to work on the work flow of the process that can be made step by step. Once the BIDS is installed with the SQL Server installation, we can locate it and start our process as shown in the steps below.

Steps

Now let’s take an example of importing data from a text file to the SQL Server database using SSIS. Let’s have the step by step process of how to achieve this task using SSIS.

Step 1 – Go to Start ? Programs ? Microsoft SQL Server 2005 ? SQL Server Business Intelligence Development Studio as shown in the below figure:

It will open the BIDS as shown in the below figure. This will similar to the Visual Studio IDE where we normally do the startup projects based on our requirements.

Step 2 – Once the BID studio is open, now we need to create a solution based on our requirement. Since we are going to start with the integration services just move on to File -> New Project or Ctrl + Shift + N. It will open a pop up where we need to select Integration Services Project and give the project name as shown in the below screen:

After creating this new project, we can see that by default a new SSIS package is added (Package.dtsx) when you create an Integration Services Project; you can right click on it and rename it. Now the designer will show the empty workflow tabs as shown below:

Step 3 – Since our task is to load the text file into the database, we need to create a new connection or otherwise in simple words we need to create a data source. Right click on the connection manager pane and select "New Flat File Connection" as shown in the below image:

Step 4 – It will open a window as shown in the below image. We need to fill in the details like the path of the text file, and Format of the text file. If the text file first row contains the columns, then we need to select the "Column names in the first data row", check box as shown in the below image:

Step 5 – You can see the columns which we are trying to import from the text file by going to the columns tab or the preview tab in the below image. In the preview tab, we can see the data as per our requirement like say a huge amount of data is available in the source like 1 million records. In the preview tab, it shows only 100 records at a time. To see the records from 500 to 600, you need to specify "Data rows to skip" = 500 in the preview tab. So we can see the records from 501 to 600. Click on the ok button to complete this task.