Creating your first SSIS package with the help of Import Export Wizard

In my previous article Overview of SQL Server Integration Services (SSIS), we had a quick overview of SSIS and the different features it provides. In this post, I will show you how to create your first SSIS package with the help of Import Export Wizard

1. Start BIDS and create a solution named ‘DemoBeginning’

2. Add a SQL Server Integration Services Project with name ‘DemoImportExport’ to the solution.

3. Delete the default package given in the solution explorer > right click on packages and select SSIS Import Export Wizard

4. We will fetch tables from the sample database AdventureWorks and put them in a newly created database named ‘DemoDB’

5. After the initial screen of the wizard, select AdventureWorks database from the database server as shown below:

6. Click Next > Click on New tab for destination database and give name DemoDB. Keep all default settings and click OK.

7. From specify table copy or query screen selec,t copy data from one or more tables or views and click Next

8. Select tables Production.Product, Production.SubCategory, Production.ProductCategory, Sales.SalesOrderDetail and Sales.SalesOrderHeader. We will keep all the default settings, hence will not do any Edit Mappings > click Next

9. On complete the Wizard screen, you will see the following screen:

Click Finish and click on Close after the required actions.

10. In solution explorer you will a see a package created with 2 tasks in control flow and 5 data sources and 5 destinations in data flow task. We can see only 2 connection managers, one used for source and one for destination.

11. We have just created the package but the data is not present in database. You can verify that the database is created without any tables in it. The ‘Prepare SQL Task’ from control flow creates the tables.

12. Execute the package by right clicking on the package in solution explorer and execute package.

You can verify the creation of tables and the data after successful execution. The components will first be shown in yellow color which will subsequently turn to green on execution or red if any error encountered. You can verify that the newly created tables do not have any primary keys and also any relationship. If we want to persist this we need to select Edit Mappings and do the necessary changes.

In next article we will continue with the same database and create a cube based on it.