SSIS Architecture

SSIS stands for SQL Server Integration Services. It is a platform for Data integration and Work flow applications. It can perform operations like Data Migration and ETL (Extract,  Transform and Load).

• E – Merging of  data from heterogeneous data stores (i.e. it may be a text file, spreadsheets, mainframes, Oracle, etc.).This process is known as EXTRACTION.
• T – Refreshing data in the data warehouses and data marts. Also used to cleanse data before loading to remove errors. This process is known as TRANSFORMATION.
• L – High-speed load of data into Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) databases. This process is known as LOADING.

Tools used for the development of SSIS projects  are –

1. BIDS (Business Intelligence Development Studio).
2. SSMS (SQL Server Management Studio).

Note: – Prior to SSIS, the same task was performed with DTS (Data Transformation Services) in SQL Server 2000 but with fewer features.

Difference between DTS and SSIS is as follows:-

• DTS :-

1. Limited error Handling.
2. Message Boxes in ActiveX Scripts.
3. No deployment wizard and BI functionality.

• SSIS :-

1. Complex and powerful error handling.
2. Message Boxes in .NET Scripting.
3. Interactive deployment wizard and Complete BI functionality.

To develop your SSIS package, you need to install Business Intelligence Development Studio(BIDS) which will be available as client tool after  installing SQL Server Management Studio (SSMS).

• BIDS: – It is a tool which is used to develop the SSIS packages. It is available with SQL Server as an interface which provides the developers to work on the control flow of the package step by step.
• SSMS: – It provides different options to make a SSIS package such as Import Export wizard. With this wizard, we can create a structure on how the data flow should happen. Created package can be deployed further as per the requirement.

Now, you must  be hitting your head to know about Data flow and Control flow. So, Data flow means extracting data into the server’s memory, transform it and write it out to an alternative destination whereas Control flow means a set of instructions which specify the Program Executor on how to execute tasks and containers within the SSIS Packages. All these concepts are explained in SSIS Architecture.

SSIS Architecture :-

1. Packages – A package is a collection of tasks framed together with precedence constraints to manage and execute tasks in an order. It is compiled in a XML structured file with .dtsx extension.
2. Control Flow – It acts as the brain of a package. It consists of one or more tasks and containers that executes when package runs. Control flow orchestrates the order of execution for all its components.
3. Tasks – A task can best be explained as an individual unit of work.
4. Precedence Constraints – These are the arrows in a Control flow of a package that connect the tasks together and manage the order in which the tasks will execute. In Data flow, these arrows are known as Service paths.
5. Containers – Core units in the SSIS architecture for grouping tasks together logically into units of work are known as Containers.
6. Connection Managers – Connection managers are used to centralize connection strings to data sources and to abstract them from the SSIS packages. Multiple tasks can share the same Connection manager.
7. Data Flow – The core strength of SSIS is its capability to extract data into the server’s memory (Extraction), transform it (Transformation) and write it out to an alternative destination (Loading).
8. Sources – A source is a component that you add to the Data Flow design surface to specify the location of the source data.
9. Transformations – Transformations are key components within the Data Flow that allow changes to the data within the data pipeline.
10. Destinations – Inside the Data Flow, destinations consume the data after the data pipe leaves the last transformation components.
11. Variables – Variables can be set to evaluate to an expression at runtime.
12. Parameters – Parameters behave much like variables but with a few main exceptions.
13. Event Handlers – The event handlers that run in response to the run-time events that packages, tasks, and containers raise.
14. Log Providers – Logging of package run-time information such as the start time and the stop time of the package and its tasks and containers.
15. Package Configurations – After development your package and before deploying the package in production environment from UAT you need to perform certain package configurations as per production Server.

This completes the basics of SSIS and its architecture. Now, we will learn the components of SSIS architecture in depth and will implement its features like import/export wizard, data viewers, etc. in our future tutorials.

SQL Server Import and Export Wizard in SSIS

Let’s get started with SSIS import and export wizard which is one of the simplest ways to implement your first SQL Server Integration Services (SSIS) package. It is very useful as it provides the easiest method to move data from sources like Oracle, DB2, SQL Server, Excel, and text files to any destination.

Implementing Import and Export Wizard can be done by any of the following ways you wish to:-

1. Open SQL Server Management Studio (SSMS). After connecting to the database engine, Right-click the database whose data you need to import from or export data to SSMS and select Tasks and proceed next with selecting Import Data (or Export Data based on what task you’re performing).
2. In Business Intelligence Development Studio, right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
3. Another common way to open it is from the Start menu under SQL Server 2012, where the option is  called as Import and Export Data.
4. The last way to open the wizard is by typing dtswizard.exe at the command line or Run prompt.

Let’s start implementing this import and export wizard with the 1st way. All the methods will show you the same wizard so you could try any of these.

Below are the values of a Flat file (text file) which we need to load on SQL Server Table.

Name,Age,Location

Sanjeev,23,Chicago

Vijay,23,Florida

Garima,25,Scotland

Idrisha,21,Paris

Bhawan,23,Canada

STEPS :-

STEP 1. Open SQL Server Management Studio (SSMS) and connect to the database engine. Go toDatabases, right-click your database and move your cursor to Tasks option and click Import Data.

STEP 2. This will open welcome screen with brief description about the import and export wizard. If you do not wish to see this welcome screen in future, just check the checkbox placed at bottom and clickNext to specify Source connection.

STEP 3. In this step, we need to select Data source from which to copy data. We are given with many options to choose data source varying from Excel source, Access source, Flat file source, OLEDB, etc. You need to choose appropriate source as per your need as in this case our data source will be Flat file source. There will be 4 tabs named General, Columns, Advanced, Preview respectively having fields which we need to specify according to our requirement.

In General tab, you will encounter the below fields –

• Data Source – Choose Flat file source as our file is simple Text file.
• File name – Click on Browse button and select the file whose data you want to import to the SQL Table.
• Locale and Code page – If you are working in a different locale then set it else remain it as default.
• Format – Depending on the format of your input data, you could select Delimited or Fixed Width File. I am using a comma delimited file hence selected Delimited here.
• Text Qualifier, Header row delimiter, Header rows to skip – This wizard will automatically specify all these fields by default after reading your file, so mostly you do not need to change them. But if you wish you can make changes as per your need.

Note:- Tick the checkbox at bottom if you need to have column names in the first data row. I am checking it here because we have Name,Age,Location as our 1st field. If we will neglect to check it then this will be considered as data records.

Now, go to next Columns tab, fields you will have are –

• Data Source – Do not make changes with this as you have specified it previously.
• Row Delimiter – It is set to {CR}{LF} by default. CR is for Carriage return while LF is line feed terms derived from olden day’s typewriter. UNIX uses just LF where as Windows use both.
• Column Delimiter – In our case, Wizard will automatically select comma {,} as column delimiter. In other cases, you can change it to colon {:}, tab {t}, etc. as per your need from the Advanced tab.

Then comes the Advanced Tab, having list of columns the file contains. We can configure the properties of each column like data type( By default it  is –  string [DT_STR] ),Text qualified, Column delimiter, etc.

Note:– Had we not checked the box stating “Column name in the first data row” we would not get the column as you see now names.

After specifying the required fields, you can view the file from the Preview tab.

 STEP 4. Once we are confirmed that all the specified values for the columns are well mapped, clickNext.

STEP 5.  Now, the only thing left is to provide the destination so as to copy our data from the Flat file  to the database.

• Let the Destination be set as default “SQL Server Native Client 10.0”.
• Specify Server name as localhost (you can also use period {.} which represents localhost). In case you have a SQL server instance like i am having then  you would need to put as <SERVERNAME\INSTANCENAME>.
• Select the Authentication option from Windows Authentication Or SQL Server Authentication (usually we choose Windows Authentication).
• Go to New and select the database where you need to import data of your Flat file. You can also map columns using Edit Mapping button as shown below in the snapshot.
• Click Next and select source tables and views and proceed.

Congrats !!! Primary setting for our 1st SSIS package is completed. On Save and Run Package Screen, either we can run the package immediately or what we can save it for later execution.

 

 STEP 6. Saving a package is always good to schedule it for later execution.

• Lets save SSIS package on File system and choose “Do not save Sensitive data” from Package Protection Level Drop down.
• Click Next and specify the basic fields like Name, Description and Path where we need to Save SSIS Package.
• On clicking the Next button, you see that the package being validated for all the setting and finally it is saved. It would have run if we had chosen the option of Run immediately.

Our package is stored with .dtsx extension at the specified path. You can go the path and check the file. That’s how we export data from a Flat file source to SQL Server Table.

We will implement the Export data part in our next part. I will really appreciate your valuable commentsif this post helped you.

Earlier we discussed how to Import data from a Flat File Data source and to load it into MS SQL Server Table. That was very easy to implement and same is the case in Export Data Wizard. We can go ahead by selecting any of the ways I mentioned earlier in my Import Wizard post (If you want to again see them, Click Here -> SQL Server Import Wizard ). I am going by the 1st way i.e Using SQL Server Management Studio.

You can Export billions of data with 1 click using this Export Wizard feature provided in SSIS. Let’s take some data to get started. We created a database named chander (like to get popular, that’s why used my name), containing some data in the form of rows and columns as shown in the image below. Now, lets see how can we export this table to a Simple Text file.

STEP 1. Open SQL Server Management Studio (SSMS) and Right click on the database whose data you need to Export (say in our case lets select database named chander).Go to Tasks option and select Export Data. This will launch Import Export Wizard screen.

STEP 2. When the Microsoft Wizard appears, Click Next to begin and specify the Source connection. These are the basic steps which we performed earlier also while importing data.

STEP 3. Since you had opened the wizard from SQL Server Management Studio by selecting Export Data option, this screen is pre-populated. Now, specify from where your data is coming in the Data source dropdown box. Once, you choose your Data source, other options can vary as per your requirement. According to this example, I will choose “SQL Server Native Client” as Data source. Make sure the Server name references your database server(In our case,it’s localhost), Authentication is set to Use Windows Authentication and select your database which you need to export from Database dropdown(as in this case I am choosing chander as my database). Click Next.

STEP 4. Now,you will proceed to Choose a Destination Screen to specify where you need to copy data. Go ahead with the following steps :-

• In the Destination dropdown list, choose Flat File Destination as we are exporting data of our database to a Flat file(Simple Text file).
• Click on Browse button (under File name option) to navigate to the path where you want to store the your Flat file.
• Other fields will be set by default like Locale is English (United States) , Code Page is 1252, Formatis delimited and Text qualifier is none. But you will have an option to set them as you need. Click Next  and select Copy data from one or more tables or views and proceed ahead.

Note:- Tick the checkbox at bottom if you need to have column names in the first data row. I am checking it here because we have Name,Age,Location as our 1st field.

STEP 5. Clicking Next will lead to Configure Flat File Destination Screen. You will see some fields which needs to be specified to configure the destination.

• Source table or view :- Choose your source from the Dropdown list box.
• Row and Column Delimiter :- Set Row delimiter as {CR}{LF} and Column delimiter as Tab {t}.

You can preview your data from the Preview button and also can edit columns mappings from Edit Mapping Button. Click Next.

Step 6. On the Save & Run Package screen, you can Run Immediately without saving or can Save SSIS Package by clicking the respective check boxes. If you wish to Save it for later execution then Select File System as the option to save SSIS Package to. Click Next.

STEP 7. On the Save SSIS Package, enter Package name, description and set File name. Save the package by Clicking Next.

STEP 8. Review the details on the Complete Wizard screen and click Finish. The package will be saved to the location we specified and will export the Customers data from SQL Server Table to Customer.txt Flat file.

STEP 9. Let’s verify whether we implemented it properly or just wasted our time specifying fields. Go the specified path where you saved it. Open the Customers.txt file and review the data.

Great !!! We implemented it. So, you can see how easy it is to export data using SSIS Import and Export Wizard.  Add the package to an SSIS Project and review the package created by Import Export Wizard.

So, this completes the implementation of Import and Export Wizard. I hope this helped you in understanding some of your concepts of SSIS. I appreciate the time you dedicated for reading this post and your comments shall encourage me to write more posts. We will reach to the other functionalities of SSIS in our next posts.

Export Data from SQL Server To Flat File Using Import And Export Wizard

2015-04-11 by suresh

We have SQL 2014 Import and Export Wizard, which can help to import or export data from various sources to different destination using Wizard. In this example, we are going to export DimGeography table data present in the Adventure Works DW 2014 SQL data base to Geography text file present in the local hard drive using SQL 2014 Import And Export Data Wizard.

Double click on SQL Server Import and Export Wizard tool (you can find in start -> All Programs -> Microsoft SQL Server 2014 -> SQL 2014 Import and Export Data Wizard). It will open SQL Server Import and Export Data Wizard. First page is welcome page

 If you don’t want see this page again then Please tick the check box “Do not show this starting page again”. For now click Next

Choose a Data Source Page: This page is used to configure the source information. Our source is SQL data base so we are selecting SQL Server Native Client as our source and I am using my localhost instance as sever name.

Select the source data base from the list, let us select the Adventure Works 2014 here.

Authentication: Here we are using windows authentication but in real time, your organization or company will provide the authentication credentials to access the database. In that case, you have to change the radio button to SQL authentication and provide the user name and password.

Choose a Destination: This page is used to configure the destination information. Our target is to save the data into text file so we are selecting Flat File Destination as our Destination

Then we have to select the text file from our local drive

From the above you can observe we are creating the Geography text file. Next, Select the Format we are going to use

If you want the text file to include column name in the first row then tick the checkbox “Column name in the first data row”. If not, uncheck it.

Click Next button

Specify Table Copy or Query: Here we have two options

1. Copy data from one or more tables or views: Use this option to select from existing tables or view (All the columns data)
2. Write a query to specify file data to transfer options: This is the option normally use because there will be unnecessary columns in every table so it is better to avoid them or selecting data with condition.

For the time being we are selecting every row so we selected the first option. Click Next button

This page is used to select the source table from SQL data base you want to use as source. Here we are selecting DimGeography table. Click on preview button to check the incoming data flow

Specify the Row delimiter and Column delimiter you wish to. Click on Edit mappings button to change the types and sizes.

Save and Run Package: This page gives us the option to save the package in either SQL server or File system. For the time being I am selecting file system

Save SSIS Package: Provide the address to save the package

Name: Change the package name as you wish

Description: Write your own description

File Name: Select the file name by clicking on the Browse button and navigate to the appropriate location.

Click Next

Click finish button to complete the Wizard

From the above you can observe the status: Success and Message.

Let’s navigate to file system address we provides and check for the file name Geography. Double click on text document to check the result

Thank you for Visiting Our Blog

Import Data from Flat File to SQL Server Using Import and Export Data Wizard

2015-04-27 by suresh

In SSIS, We have SQL Server Import and Export Data Wizard, which is used to import or export data from various sources to different destination using Wizard. In this example, we have DimGeo text document and we are going to import the data present in the text file to SQL data base table using Import and Export Data Wizard.

Below screenshot will show you the data present in the DimGeo text file.

If you are running on Windows 7 then, you can find in start -> All Programs -> Microsoft SQL Server 2014 -> Import and Export Data Wizard).

If you are running on Windows 8 then you can find in Apps

Double click on SQL Server Import and Export Data tool. It will open SQL Server Import and Export Wizard. First page is welcome page.

If you don’t want see this page again then Please tick the check box “Do not show this starting page again”. For now click Next

Choose a Data Source Page: This page is used to configure the source information. Our source is text file so we are selecting Flat File source as our Data Source

Then we have to select the text file from our local drive

From the above you can observe that we selected the DimGeo text file.

If you text file include column name in the first row then, tick the check box ‘Column name in the first data row’. If not, uncheck it.

Click on the columns tab to configure row and column delimiter and Row delimiter (Specify how you column data and row data is separated).  Most of the times delimiters are automatically selected but it is always good practice to verify it yourself. We can also see the data as well.

Click on the Advanced tab to check/Modify data types of the source columns

Choose a Destination Page: This page is used to configure the Destination information. Our target is SQL data base so we are selecting SQL Server Native Client as our Destination and we are using my localhost instance as sever name.

Select the destination data base from the list, let us select the SSIS Tutorials here.

Authentication: Here we are using windows authentication but in real time, your organization or company will provide the authentication credentials to access the database. In that case, you have to change the radio button to SQL authentication and provide the user name and password.

Select Source Tables and Views: This page is used to select the destination table from SQL data base if you have already created. If not click on Edit Mappings button will open the window like below to create table and also Edit SQL button to customize

Click on Edit Mappings button

Click ok

From the above screenshot you can observe that,

• Here we can create new table or we can customize already existing table by clicking Edit SQL button.
• We can drop the existing table and recreate it by selecting Drop and re-create destination table
• Enable identity insert option will help you to insert values into identity column

Preview button is used to see the column data.

Save and Run Package: This page gives us the option to save the package in either SQL server or File system. For the time being we are selecting file system

Click Next button

Name: Change the package name as you wish

Description: Write your own description

File Name: Select the file name and file location by clicking on the Browse button and navigate to the appropriate location. (This is the place, where the package is going to save).

Click Next

Click finish button to complete the Wizard

From the above you can observe the status: Success and Message of 1000000 rows transferred.

Let’s open the SSMS and check the output data

Thank you for Visiting Our Blog

Export Data from SQL Server To Excel Using SSIS Import And Export Wizard

2015-05-24 by suresh

In SQL Server 2014, We have SQL Server Import and Export Wizard, which can help to import or export data from various sources to different destination using Wizard. In this example, we are going to export [Sales 2] table data present in the [SSIS Tutorials] Database to Excel  file present in the local hard drive using SQL 2014 Import And Export Data Wizard.

Below screenshot shows you the data present in the [Sales 2] Table

Double click on SQL Server Import and Export Wizard tool (you can find in start -> All Programs -> Microsoft SQL Server 2014 -> SQL 2014 Import and Export Data Wizard). It will open SQL Server Import and Export Data Wizard. First page is the welcome page

 If you don’t want see this page again then Please tick the check box “Do not show this starting page again”. For now click Next

Choose a Data Source Page: This page is used to configure the source information. Our source is SQL data base so we are selecting SQL Server Native Client as our source and we are using our own instance as sever name.

Select the source data base from the list, let us select the [SSIS Tutorials] here.

Authentication: Here we are using windows authentication but in real-time, your organization or company will provide the authentication credentials to access the database. In that case, you have to change the radio button to SQL authentication and provide the user name and password.

Choose a Destination: This page is used to configure the destination information. Our target is to save the data into Excel file so we are selecting Microsoft Excel as our Destination

Then we have to select the existing Excel file from our local drive

From the above screenshot you can observe that, We are selecting the Export Data from  SQL Server to Excel  file. Click Open button to select it

If your Excel file includes the column names in the first row then check mark the check box “Column name in the first data row”. If not, uncheck it.

Click Next button

Specify Table Copy or Query: Here we have two options

• Copy data from one or more tables or views: Use this option to select from existing tables or view (All the columns data)
• Write a query to specify file data to transfer options: This is the option we normally use because there will be unnecessary columns in every table so it is better to avoid them or selecting data with condition.

For the time being we are selecting every row so we selected the first option. Click Next button

This page is used to select the source table from SQL data base you want to use as source. Here we are selecting [Sales 2] table. If your Excel file already contains the table to hold the output then select the appropriate Excel table from the destination list otherwise, SQL Server Import and Export Wizard will generate the table name as shown in below screenshot. Click on preview button to check the incoming data flow

Click on Edit mappings button to change the Data types and size of each column. You can also edit the table creation using Edit SQL button

From the above screenshot you can observe that, English Product Name, Color and Sales Order Number columns are of type LongText which is not compatible with the  source data in SQL Server. So, let us change the data types.

We changed the above mentioned columns to VarChar and assigned the required sizes as well. If you forgot to change the Size of a column then data will be truncated so please change the size of the each column to match with source columns.

Save and Run Package: This page gives us the option to save the package in either SQL server or File system. For the time being we are not saving the package

Click finish button to complete the Wizard

From the above you can observe the status: Success and Message.

Let’s navigate to file system address we provides and check for the file name Export Data from SQL Server to Excel. Double click on Excel file to check the result

Thank you for Visiting Our Blog

Import Data from Excel to SQL Server Using SSIS Import and Export Data Wizard

2015-05-24 by suresh

In SQL Server 2014, We have SQL Server Import and Export Data Wizard, which is used to import or export data from various sources to different destination using Wizard. In this example, we have Import Data from Excel to SQL source.xls Excel file and we are going to import the data present in this Excel file to SQL data base table using SQL Server Import and Export Data Wizard.

Below screenshot will show you the data present in the Import Data from Excel to SQL source.xls Excel file.

If you are running on Windows 7 then, you can find in start -> All Programs -> Microsoft SQL Server 2014 -> Import and Export Data Wizard).

Double click on SQL Server Import and Export Data tool. It will open SQL Server Import and Export Wizard. First page is the welcome page.

If you don’t want see this page again then Please tick the check box “Do not show this starting page again”. For now click Next

Choose a Data Source Page: This page is used to configure the source information. Our source is Excel file so we are selecting Microsoft Excel as our Data Source

Then we have to select the text file from our local drive

From the above you can observe that we selected the Excel file.

If your Excel file include column name in the first row then, tick the check box ‘Column name in the first data row’. If not, uncheck it.

Click on the Next button.

Choose a Destination Page: This page is used to configure the Destination information. Our target is SQL data base so we are selecting SQL Server Native Client as our Destination and we are using our own instance as sever name.

Select the destination database from the list, let us select the SSIS Tutorials here.

Authentication: Here we are using windows authentication but in real-time, your organization or company will provide the authentication credentials to access the database. In that case, you have to change the radio button to SQL authentication and provide the user name and password.

Click Next button

Specify Table Copy or Query: Here we have two options

• Copy data from one or more tables or views: Use this option to select from existing tables or view (All the columns data)
• Write a query to specify file data to transfer options: This is the option we normally use because there will be unnecessary columns in every table so it is better to avoid them or selecting data with condition.

Select Source Tables and Views: This page is used to select the destination table from SQL data base if you have already created. If not click on Edit Mappings button will open the window like below to create table and also Edit SQL button to customize

Click on Edit Mappings button

Click ok

From the above screenshot you can observe that,

• Here we can create new table or we can customize already existing table by clicking Edit SQL button.
• We can drop the existing table and recreate it by selecting Drop and re-create destination table
• Enable identity insert option will help you to insert values into identity column

Preview button is used to see the column data.

Save and Run Package: This page gives us the option to save the package in either SQL server or File system. For the time being we are selecting file system

Click Next button

Name: Change the package name as you wish

Description: Write your own description

File Name: Select the file name and file location by clicking on the Browse button and navigate to the appropriate location. (This is the place, where the package is going to save).

Click Next

Click finish button to complete the Wizard. Let’s open the SSMS and check the output data

Thank you for Visiting Our Blog