What is SSIS?
SSIS is a component of Microsoft SQL Server software which is used to perform Data Migration tasks. It is a good platform for data integration and workflow applications, and it is a good warehousing tool for data extraction and transformation.
Features
SSIS is customizable and has a good wizard to handle the common tasks like importing data or exporting data from databases. Also SSIS uses a drag-and-drop user interface. The SSIS package allows you to move the data among any of these data sources:
- SQL Server Databases
- Flat Files
- Microsoft Access Databases
- Excel Worksheets
- OLE DB providers.
Creating and maintaining SSIS packages uses a tool on Microsoft Visual Studio called the SQL Server Business Intelligence Development Studio (BIDS).
Connections
A connection includes the information necessary to connect to a particular data source.
Tasks
Task is a unit that performs certain actions called data transformation tasks like copying data.
Event Handlers
A workflow can be designed for a number of events in the different scopes where they occur.
Variables
Variables are used to store results and make decisions.
We can save a package as a file or store it with a hierarchical namespace with a SQL Server Instance.
Now we are going to develop a package to transfer data from Ms Access Database to SQL Server Database.
Source Database Name: Legalese
Destination: TestDb (SQL Server 2005)
Make sure you have installed SSIS along with your SQL Server 2005.
Steps
- Open the SQL Server “Business Intelligence Development Studio” from Microsoft SQL server 2005 Folder.
- Then the Microsoft Visual Studio IDE Will be displayed.
- Choose File > New > Project
- Then choose the “Business Intelligence Projects Menu” from the left side “Project Types” section.
- Then select the Integration Service Project Item from the right side Templates.
- Set the Name and apply the location.
- Then click ok.
- Now the Integration Service Project will be opened.
- Now right click on the SSIS Package Menu from the Solution Explorer.
- Then the SQL Server Import and Export Wizard displayed.
- Click Next.
- Then choose the Data Source from the SQL Server Import and Export Wizard window.
- Our Source Database is MS Access. So select the “Microsoft Access” as your Data source and select your File Name.
- Then Click Next.
- From the window now you have to select the Destination.
- Our destination is SQL Server so select SQL Native Client.
- Then choose your server name and select the Authentication level as Use SQL Server Authentication.
- Also set the User Name and Password.
- Then select the Database Name.
- Click Next.
- Then select the first option “Copy data from one or more tables on views” from the Specify Table or Copy window.
- Then Click Next.
- Then the List Of Source Tables (From Ms Access) and a Destination Table will be displayed.
- Choose your destination table.
- Now Click Edit Mappings. Now the column mapping window will be displayed. In that window all the source and destination columns will be mapped. If there are any field name changes, then that column will not mapped. You have to map that manually.
- In the above window you may notice that CompanyName from the Source database is not mapped with the destination. This is because the field name is different in the destination.
- You have to select the correct destination to get the desired result.
- Here my destination is Company.
- Click Ok.
- Now Click Next.
- Click Finish.
- Now you will get the “execution was successful” message.
- Click Close.
Now you can see that Package1 will be created in the Solution Explorer.
Setup Dataflow Tasks in the SSIS Package >