Microsoft SSIS

Create OLE DB destination and load data

Create OLE DB destination and load data

 

  1. Create OLE DB destination and load data to dbo.Employee we created in SSIS Lessons Database.
    1. Now go back to the Package.dtsx package
    2. If you are on Control Flow then Double Click the DFT – Process 1

    3. This will take the control to the Data Flow and the screen looks similar to this.
    4. Now drag and drop OLE DB Destination from the SSIS Tools < Destination>
    5. Click on the SRC – EMP it should highlight arrows. Now drag and drop the grey arrow on to OLE DB Destination
    6. Once connection is created now it is time to configure the OLE DB Destination. Once configured the RED X will be vanished.
    7. Now Double click on the OLE DB destination and Click NEW to create a new connection manager to SSIS Lesson (our destination database)

    8. Bring the following screen and here Click NEW because we don’t see the SSIS Lesson database in the Data connections
    9. Now Enter Server name <LOCALHOST> and on Select or Enter Database name click the drop down and select SSIS Lessons database or SSIStestDB (in this example).

    10. Click < Test Connection > and make sure it is succeeds and display the following message

    11. Click OK which take the control back to the following screen

    12. Click OK again on this screen too
    13. Go to < Name of the table or the View:> and click the Drop down and select the table created dbo.Employee

    14. Now Click ON MAPPINGS on the left List
    15. In our case it automatically connect each column from Available Input to Destination because in this case the source and destination columns are named exactly same.
      1. Note :If not you may not see any connection and we need to drag and drop the connections from Source to Destination manually

    16. We will leave the Error Output as is and it will fail on ERROR. This will help us make sure that our process fails when there is some problem with the data
    17. Now we have SOURCE and DESTINATION set up to load the data

    18. Before we process or test this let us change the name of the Destination to SSIStestDBEmployee

 

 

 

 

  1. Processing or Testing the PACKAGE :
    1. Go back to the CONTROL FLOW tab
    2. From the Top level Menu select DEBUG

    3. From the Drop Down CLICK <Start Debugging>
    4. IF complete successfully you should see the following screens
    5. Control Flow

    6. Data Flow
    7. cx

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  1. Verify the Data in the Table:
    1. Go to the SSMS Management Studio and log on to the LocalHost
    2. Once it connects to the Database Expand the Databases (click + sigh beside the database)
    3. Here you can <right Click> the mouse on the table dbo.Employee and select <Select top 1000 rows> or you can run the following

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s