by Reza Rad and Pedro Perfeito
In this chapter, we will cover the following topics:
Import and Export Wizard: First experience with SQL Server Integration Services (SSIS)
Getting started with SSDT
Creating the first SSIS package
Getting familiar with Data Flow Task
SSIS 2012 versus previous versions in Developer Experience
As technology evolves, it is always necessary to integrate data between different systems. The integration component is increasingly gaining importance, especially the component responsible for data quality as well as the cleaning rules applied between source and destination databases. Different vendors have their own integration tools and components, and Microsoft with its SSIS tool is recognized as one of the leaders in this field.
SSIS can be used to perform a broad range of data integration tasks, and the most common scenarios are applied to Data Warehousing. The known term associated with Data Warehousing is the Extract Transform and Load (ETL) that is responsible for the extraction of data from several sources, their cleansing, customization, and loading into a central repository (for example, to a Data Warehouse, Data Mart, Hub, and so on). SSIS is also used in other scenarios, for example data migration and data consolidation. Data Migration is the one-time movement of data between databases and computer systems, and is needed when changes occur or when we upgrade our systems. Data Consolidation combines and integrates data from disparate systems and assumes high importance in a business environment with increasing acquisitions and mergers. The following diagram adapted from TDWI (www.tdwi.org) helps clarify the different scenarios where SSIS could be used:
New business challenges are driving organizations to adopt data integration projects. Some of these challenges are:
Increasing demand for real-time information reporting and analysis
Large volumes of data spread along the entire organization
The need to comply with regulations, which often require to continuously track all changes to data and not just the net result of those changes
Although SSIS is an amazing tool for data integration, the same work can be done manually in almost all cases. As you can imagine, performing data integration tasks manually could be hard to maintain in terms of code, hard to scale properly, and would require more time to implement. From our perspective, since we have SSIS, there is no real reason to do it manually. The cost of ownership is not a problem either, because SSIS is included with SQL Server licenses that most organizations have already acquired.
In this chapter you will learn how to work with SSIS, how to create packages for data transfer, and you'll perform some simple operations with SSIS Package. At the end, we will highlight several improvements which are included in this new version.
The Import and Export Wizard will be our first stop at SSIS. This wizard provides a simple ETL and is easy to use for basic data transfer operations. With this wizard you can choose a source, a destination, and map columns with few constraints on data transfer options. We will take a brief look at this wizard in our first experience with SSIS.
Install SQL Server 2012. SQL Server 2012 comes with three editions: Standard, Business Intelligence, and Enterprise. The Business Intelligence edition covers all requirements for this book that you'll need to install. With this edition you will have all SQL Server Integration Services features.
For many recipes in this book, you need to have the AdventureWorks2012 and AdventureWorksLT2012 sample databases installed. Information about installing these databases can be found in the book introduction.
To install sample databases, first download the database files from http://msftdbprodsamples.codeplex.com and then open SSMS to execute this statement (download AdventureWorks2012 Data File and AdventureWorksLT2012_Data):
Note that you should replace the path of the file here with the path of the file downloaded on your machine.
Create a new database in SQL Server. Open SQL Server Management Studio (SSMS) from Start Menu | Microsoft SQL Server 2012 | SQL Server Management Studio. In the SSMS, connect to local computer instance and create a new database. Name this database as PacktPub_SSISbook.
Note that you should run SSMS as administrator, to do this just right click on SQL Server Management Studio from path above and right click on it and choose Run as Administrator.
Open the Import and Export Wizard; there are three ways you could do it:
In the Run window, enter
DtsWizard.Open the wizard from the following address: Start Menu | All Programs | Microsoft SQL Server 2012 | Import and Export Data.
In SSMS, right-click on any database and then under Tasks, select Import Data or Export Data.
At the first step in the Import and Export Wizard, a welcome page will appear. Click on Next to enter the Choose a Data Source step, in this step you should choose where the Data Source comes from. The Data Source can be any source; from an Oracle database or SQL Server to any other database, flat files (such as
.txtand.csv) or even Excel files, the range of source and destinations is based on data providers installed on the machine. For this sample, leave the Data Source option as its default option which is SQL Server Native Client 11.0.We want to export two tables from the
AdventureWorks2012database to another database. Therefore, leave the Server name as (local) or a single dot ( . ) or if you have a named instance you could use.\<Instance-Name>, and in the Authentication section leave the Authentication Type as Windows Authentication. This option will use your Windows account for connecting to the database, so obviously the Windows account should have read access to the underlying database.In the Database drop-down box, select AdventureWorks2012 from the list. Then click on Next and go to the next step.
The next step is required to choose a Destination, therefore, provide the connection details of the data's destination (types of destinations can differ from databases to flat files). For this sample leave the destination as default value which is SQL Server Native Client 11.0.
Set the Server Name to (local) or dot ( . ) to connect default instance of current machine. Set the Authentication as Windows Authentication. Select R1.1 in the Database drop-down list . Then click on Next to follow the wizard's steps.
In the Specify Table Copy or Query step you can choose between selecting a table or view name for a source database or writing a query to fetch data from a source. For this example, choose the Copy data from one or more tables or views option .
Next, a list of tables and views from the source database will appear. For this example, select HumanResource.Department, Person.Address, and Production.Product. Then click on Next.
The Save and Run Package step is next; it provides the ability to save all the settings and configurations that you've set for an SSIS Package. We are going to save this package to see what the SSIS Package looks like. There are a lot of concepts and options associated with saving SSIS Packages which will be discussed in upcoming chapters, so don't worry about some terminologies here, all of them will be explored later. Check the Save SSIS Package option and click on the Next button .
In the next step which is the Save SSIS Package dialog , type the name as
R01_ImportExportWizard, and choose a location for the package file. Then click on Next.
Now a summary of all settings that you've done appears here; after reviewing the summary click on Finish.
After clicking on the Finish button , the Import and Export Wizard will show up and we can see all the messages generated during the package's execution. The number of rows copied are displayed, or any other information such as the number of rows transferred, validated, and any other actions.
Open and see the execution's report by clicking on the Report button .
Close the Wizard and open SSMS to check the destination database, you can see transferred tables there with data.
In this recipe, we created the first SSIS Package with the Import and Export Wizard, this simple scenario exports some tables from the AdventureWorks database to an empty database. In the last few steps, we saved the whole data transfer scenario to an SSIS Package on a file system that we'll be able to open with SQL Server Data Tools (SSDT) in later recipes.
With the Import and Export Wizard you can import or export data from a source to a destination, this is the most simplistic ETL scenario. In the Select a Data Source step you perform the Extract part of ETL and fetch data from SQL server database (data source). The second step, which was the Destination Select, was configured during the Load part of ETL. Load indicates where data should be exported; we export data to a SQL Server database. In this first example scenario we don't have any specific Transform stage. We will see this part of the ETL later, during the Data Flow chapters of this book.
When you choose table(s) from the AdventureWorks database in the Select Source Tables or Views section
, tables that don't already exist in the destination database will first be created.
After matching the columns and metadata, data will be transferred and a summary of all logs will show what happened during execution.
We saved this package to the file system; we can also save packages to an SQL Server. The difference between the different storage options for SSIS Packages with their pros and cons will be explored later in Deployment chapters.
As you've seen so far, the Import and Export Wizard is a simple way to transfer data that covers our most basic requirements. But in real-world scenarios, you need some additional features, which we'll now discuss.
In the Select Source Tables and Views step, when you select a table or view to transfer, an Edit Mappings button will be enabled. Note that you need to select a row in order to enable this button.
When you click on Edit Mappings, the Column Mappings window will appear. As you see, there are some options here for mapping Source and Destination columns.
When the destination table doesn't exist in the destination database, the Create Destination Table step will flag it. This means that the missing table will be created in the destination database; you can click on the Edit SQL button to see what the exact create table statement is; you can change the script as you want here.
When the destination table already exists in the destination database, the Delete Rows and Append Rows options in Destination Table will be selectable. You can select between deleting rows in a destination table before data transfer or appending new rows to existing records with these options.
The Drop and re-create destination table option
will be selectable when the destination table already exists. Another important option is Enable identity insert
, which should be checked if you load data into an IDENTITY column. The last part of the Column Mappings window is the Mappings section, which shows Source and Destination columns
, as well as some additional column information. By default, all columns with the same name in Source and Destination will be mapped automatically. However, if the column names are different you should select columns by selecting the correct column name in the drop-down box. If you want to remove a column from data transfer you can simply choose the <ignore> option
.
In real-world scenarios, you need to configure transfer settings for all tables at once. Select multiple rows in the Select Source Tables or Views Wizard step by holding the Ctrl key and clicking on every row that you need, and then click on the Edit Mappings button. The Transfer Settings dialog box will open; all configurations that you set here will be applied to all selected tables.
In the Destination schema name you can choose a schema name from the destination database and use this schema for all selected tables. You can also type a schema name there; if that schema doesn't exist in the destination database it will be created and all tables will be created under this new schema. The Drop and recreate new destination tables option will be applied to all new tables, and the Delete rows in existing destination tables option will be applied to all existing tables. Enable identity insert is also applicable to all tables that have Identity columns.
Data types which are automatically mapped through the Column Mappings window of the Import and Export Wizard are defined in XML files based on source and destination type. A list of all these XML files is available here:
There is a mapping file for each source or destination, and details of data type mappings can be found there. The next screenshot shows a portion of the MSSql8toOracle8 mapping file:
