How do I use Data Transformation package

How do I use Data Transformation package

Step 1

To create a data transformation package go to Enterprise Manager, expand the Data Transformation Services and choose Local Package

Then DTS package will appear as show in the diagram below. We will try to construct a simple DTS package that will prompt user a message upon success.

Step 2

First of all we need to create a Microsoft Access Connection. You can set it to any database that you might have. The configuration might look something like this.



Step 3

Then we create another connection, this time a SQL OLEDB connection.

We will set to import data to be place on a table in Demo database. The configuration for our second connection look like this



Step 4

After that we need to define the Transform Task properties indicated by the black arrow line.

Double click on it and then a windows similar to the one shown below will appear. We are trying to import Department table from our access database to table also called Department SQL Server database

The source tab look like the one show below :



Step 5

Next click on Destination tab. Just click on Create in the table name.



Step 6

Then user can click on Transformation tab to check the fields that will be imported over.

Step 7

Click OK when done.

Step 8

We will now create an Active X object. Just drag from the toolbox that is located on your left hand side.

Step 9

Inside the textbox, key in the following Vbscript MSGBOX("HEY YOU DID IT")

Step 10

Click on Ok when done.

Step 11

Then hold down your CTRL key and click on our OLEDB provide and the Active X object.

Step 12

Then link them by selecting Workflow and the click on On Success. That means that if you process is successful it will trigger a messagebox saying that you did it.

The completed diagram look like the one show below:

Step 13

When you run it, you will see this message appearing.