Table of Contents
- Introduction
- 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:
- Simple SSIS package using SSIS Wizard
- 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):
- Go to start & open SQL Server Business Intelligence Development Studio.
- Create a new project and select Integration Services Project template from the templates dialog window.
- Click on the project menu & select SSIS import and export wizard.
- Now the wizard will pop up click next.
- 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:
- 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.
- 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:
- 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.
- Now you are very close to the end of all steps, the wizard will display a summary click next.
- 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:
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:
- 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.
- 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:
Figure-A
- 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.)
Figure-B
- 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:
- 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.
- 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:
Figure - Output
Conclusion
I hope this might be helpful to you! Enjoy.
References
History
- 30th March, 2011: Initial post
