This is part 3 of a 4 part series that introduces SQL Server Integration Services (SSIS). This article describes how to use the Import and Export Wizard. The Import and Export Wizard provides the simplest method of copying data between data sources and constructing basic packages. A major limitation is that it does not allow transformations to occur during the copy process (however with SSIS 2008, a choice exists to include a Data Conversion Transformation if there is a mismatch in data type between source and destination). Data can be pulled from the source to the staging server, where transformation can occur then transfers the data from staging to production server; though this is demanding on resources. Launch the Import and Export Wizard
- On the Start menu, point to All Programs, point to Microsoft SQL Server 2005/2008 (Depending on the SQL Server version you have installed), and then click Import and Export Data, or
- In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data (if Import data is clicked, by default destination server details will display for the server performing this operation likewise if Export data is clicked, the same will occur) or
- In a command prompt window, run DTSWizard.exe.
After the welcome screen, the next screen asks to specify the source server name, credential to use and database name as shown below:
After clicking next, a prompt to enter destination server name, credential to use, database name (if creating a new database, do click the New command button) appears as seen below:
Next, will be a prompt to choose between data from tables or views, or whether to write a query to retrieve the data. Depending on the selection made, the next screen will vary.
After clicking next (this exampe chose the first option to get data from tables or views), a list of all the available tables and views at source is provided then select the tables or views to get the data from. The Edit Mappings button allows changes to be made to the mapping of columns between source and destination. The Preview button allows previews of the top 100 records from the selected table or view.
After clicking next, select from two options, the first asking to run the package immediately and the second allows the package to be saved for later use. If deciding to save a prompt will ask where do to save the package either on the file system or SQL Server; the next screen it will ask for location or server details to save the package.
Click Next then Finish buttons and the Import and Export wizard will start transferring the data; the status will be shown as below, upon completion click on Close:
SSIS Designer
SSIS Designer is a rich graphical tool that can be used to create and maintain Integration Services packages. Using this designer control flow and data flow can be constructed in a package; handlers may be added to the package and its objects. The execution progress during run-time is shown. It has four permanent TABs and in addition to that, one additional TAB pops up during execution to show the package progress, as given below:
Control Flow Tab – construct the control flow in a package on the design surface of the Control Flow tab. Drag items from Toolbox to the design surface and connect them into a control flow by clicking the icon for the item, and then dragging the arrow from one item to another.
Data Flow Tab – used if a package contains a Data flow task; can add data flows to the package constructed on the design surface. When the Data Flow Task in Control Flow tab is double clicked, details of that Data Flow Task is opened in the Data Flow designer surface, allowing definition of data flows.
Event Handlers Tab – Package and its components have different events in their execution life-cycle. It’s possible to create event handlers for these events in Event Handlers designer surface. (further discussion about event handlers is in the article “SQL Server Integration Services – Features and Properties”.)
Package Explorer Tab – Package Explorer tab displays the contents of the package. Packages can be complex, including many tasks, connection managers, variables, and other elements. The explorer view of the package shows a complete list of package elements.
Progress Tab – This tab appears when a package is executed in designer and shows the execution progress. This tab is changed to Execution Result once the has package stopped executing. It will contain the results of the last execution until the package is closed or re-run.
At the bottom there is a control tray of Connection Managers, which will display all the used and available connection managers for the package.
While executing a package in designer, every task will change color according to the conditions described below:
- No color/White Color – the execution of the task has not started yet.
- Yellow Color – the execution of task is in progress.
- Green Color – the execution of the task has completed successfully.
- Red Color – It indicates the execution of the task has completed but it has failed.
The following three scenarios describe some examples of creating an SSIS package:
Note: the examples below show one task in package for simplicity, in actuality a package may contain several tasks.
Scenario 1
This scenario will create a very simple package; to use Execute Process Task to execute Notepad.
Open a new package; drag Execute Process Task from Toolbox to Control Flow.
Right click on the task, click on Edit and set the relevant properties as shown below:
Now the package is all set to be run. Press F5 and the package will start executing, click on Progress tab to see the execution progress.
Scenario 2
This example will show how to create a very simple package; it will use Data Flow Task to pull data from source to destination.
Open a new package; drag Data Flow Task from Toolbox to Control Flow.
Double click on the data flow task, now the details of it will be open in Data Flow Designer tab. Drag a source and a destination from the toolbox (Toolbox in Data Flow tab changes its content, it will now only show source, transformation and destination tasks) to the designer and specify source and destination details.
While configuring the source, select one of the available connection managers, data access method and what columns to pass through the data path.
While configuring the destination, select one of the available connection managers, data access method and the mapping between source and destination columns. Though SSIS is smart enough to do this mapping on the basis of similarity of column names and types between source and destination, it can be changed if required.
Scenario 3
In this example the package created in scenario 2 is used to add a derived column (VendorDetails = AccountNumber + “:” + Name) and sort transformation (to sort the incoming record-set on Name column before uploading). One or multiple transformations in the way from source to destination can be done as needed.
The next article in this series will describe how to use the SSIS API Model.