Click here to Skip to main content
Click here to Skip to main content

How to Create your First SQL Server Integration Services (SSIS) Package - Part 1

, 9 Jun 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
How to create your first SQL Server Integration Services (SSIS) package - part 1

Table of Contents

  • Introduction
    • What is SSIS package?
  • Features
    • SSIS Import/Export Wizard
    • Creating or maintaining SSIS packages
    • Features of the data flow task
  • How to create a simple SSIS package
  • References
  • Conclusion
  • History

Introduction

SQL Server Integration Services (SSIS) is an excellent component with some great features of Microsoft SQL Server 2005 & 2008 edition as well. Today’s businesses are completely dependant on the data which comes through user / customers. Business analyst analyzes the data to figure out the market. So not only in business sector but also with the other areas; data are very much important.

So, if we think for a small single domain business, it could be easy to collect the business transactional data by using any data migration tools. But if it’s in a large domain or even multiple domains then how do you centralize all the data; data centralization is very common and essential requirement for business people where business is getting globalized day by day.

In this article, we will discuss about Microsoft SSIS package, how to create SSIS package for smooth data migration, design SSIS package for single domain, design SSIS package for multiple domains and some other features i.e., SSIS Import/Export, etc.

What is SSIS Package?  

By definition, SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software which can be used to perform a broad range of data migration tasks.

SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

Features

SQL Server Integration Services (SSIS) has various useful features, for example SSIS import/export wizard, creating / maintaining SSIS package using Microsoft visual development IDE /tools, etc. Let’s try to get a better understanding about the above features.

SSIS Import/Export Wizard

This wizard helps you to create SSIS package very quickly. For example, we want to move data from a single data source to another destination, note that we can do this without transformations and the wizard almost seems like Microsoft SQL Server Import / Export wizard.

Creating or maintaining SSIS packages

SQL Server Business Intelligence Development Studio (BIDS) allows users to create / edit SSIS packages using a drag-and-drop user interface. BIDS is very user friendly and allows you to drag-and-drop functionalities. There are a variety of elements that define a workflow in a single package. Upon package execution, the tool provides color-coded, real-time monitoring.

Features of the data flow task

SSIS provides the following built-in transformations:

  • Conditional Split
  • Multicast
  • Union-All, Merge, and Merge Join
  • Sort
  • Fuzzy Grouping
  • Lookup and Fuzzy Lookup
  • Percentage Sampling and Row Sampling
  • Copy/Map, Data Conversion, and Derived Column
  • Aggregation
  • Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing
  • Pivot and
  • Slowly Changing Dimension
  • Script Component

How to Create a Simple SSIS Package

Now we are going to create:

  1. Simple SSIS package using SSIS Wizard
  2. After that, we will do the same without any using any wizard & the source / destination data server will be different.
(i) Creating a Simple SSIS package using SSIS Wizard

Well let’s implement the first one. To create a SSIS package using SSIS Wizard, we need to follow the steps listed below:

Required step(s):

  1. Go to start & open SQL Server Business Intelligence Development Studio.
  2. Create a new project and select Integration Services Project template from the templates dialog window.
  3. Click on the project menu & select SSIS import and export wizard.
  4. Now the wizard will pop up click next.
  5. In this step, you just need to select the data source and click next. (see the figure below at step number 5)

    Note: If the SQL server authentication mode is configured as mixed mode, it could be better to choose SQL authentication.

    The following figure (A) shows the execution of the above steps:

    img_1to5.jpg

  6. Well, now it’s time to select the destination; so select the destination database and tables as well, where you want to store the data and click next.
  7. In this step, the wizard will ask you for specific table copy or query. Select specific table copy and click next.

    The following figure (B) shows the execution of the above steps 6&7:

    img_6to7.jpg

  8. Now, the wizard will display the source table and the destination table, you can preview the data by using the preview button or you can also add / remove column for your data mapping. Click next to proceed.
  9. Now you are very close to the end of all steps, the wizard will display a summary click next.
  10. Well done!!! You have successfully configured all the previous steps, now click finish.

Note: The wizard will create a new package.dtsx file.

The following figure (C) shows the execution of the above steps:

img_8to13.jpg

The Wizard can quickly move data from a variety of source types to a variety of destination types, including text files and other SQL Server instances. I must say this is really cool.

(ii) Creating Simple SSIS package without using SSIS Wizard with different scenario:

Well, this isn’t so easy. Let’s try to implement the same things but the scenario will be different i.e., now we transfer data from one data server / domain to another data server / domain.

Before we start, we need to understand few basic things listed below:

  • Connections
  • Tasks
  • Precedence constraints
  • Event handlers
  • Variables

Connections

To connect to a particular data source, you must require a connection which includes all the required information to connect. Tasks can reference the connection by its name, allowing the details of the connection to be changed or configured at runtime.

Tasks

A task is an atomic work unit that performs some action. For more about the Task, visit the link below:

Precedence Constraints

I think the following reference is very good to understand the precedence constraints and work with precedence constraints.

Event Handlers

A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package — such as cleaning up after errors.

Variables

Tasks may reference variables to store results, make decisions, or affect their configuration.

Okay great, now we have the basic knowledge and based on that we will create the second one SSIS package. So let’s start…

To achieve this, we need to follow the step(s) below:

  1. Open SQL Server Business Intelligence Development Studio (BIDS) and create a new project type of (Integration Services Project). Once the project is created, you will be able to see the package designer window of the default package called as Package.dtsx.
  2. In solution explorer, right click on the data source and click New data source. Give a data source name (for source), add the proper connection string and click ok. Repeat the same step for creating a data source for the destination.

    Figure-A & B shows the execution of the above two steps:

    SSIS1to5.jpg

    Figure-A
  3. So now we have two data sources; one is for the origin and another one for remote location. (Look at the figure below, your solution explorer should be similar to this one.)

    se1.jpg

    Figure-B
  4. Let’s create two connections for the data sources. Just right click on the connection manager and click on New connection, a list of connection manager types select ADO.NET or also you can choose OLEDB. Select one of them and click next & configure the connection for both source and destination (make sure that your connection is correct).

    Figure- C shows the execution sequences of the above steps:

    SSIS-ret.jpg

  5. Drag and drop a data flow task from the tool box on to the control flow section for both source and destination. Double click on each data flow task and configure the connection manager.

    Figure- D shows the action above.

    SSIS-ret-svr.jpg

  6. Now select the column tab and select the required columns in the table and click ok. Drag the OLEDB / ADO.NET source output connector (indicated by green arrow) to the OLEDB / ADO.NET destination. Open OLEDB / ADO.NET destination, give the destination data source in connection manager, data access mode will be Table or View - fast load. Select the table into which the data has to be inserted. Select the mappings tab, and properly map the source columns to the destination columns...

Finally, run the package.
Our output will be like the figure below:

exp.jpg

Figure - Output

Conclusion

I hope this might be helpful to you! Enjoy.

References

History

  • 30th March, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Marufuzzaman
Architect
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.
 
Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.
 
An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.
 
Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.
 
Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).
 
Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:
 
1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionNice Artical Pinmemberkumar Umesh1-Sep-11 23:00 
AnswerRe: Nice Artical PinmvpMd. Marufuzzaman1-Sep-11 23:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 10 Jun 2011
Article Copyright 2011 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid