OLEDB SOURCE TO OLEDB DESTINATION

Requirement:

I have a SQL server table as source table, with the help of the source table I want to create a new table (Table name is Target) with same structure (column names and data types should be same) and data, in the same data base.

Server details:

Server name: Test server

Data base name: Test

Source table name: Test

Authentication: Windows authentication

Package creation:

• In control flow items select  “Data flow task” then drag and drop into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• In the Data flow, tool box contains “Data flow source, Data flow transformation and data flow destination.
• In Data flow sources items select “OLEDB source” then drag and drop it into designer surface shown below.

• Right click on “OLEDB Source” and select edit option, then “OLEDB Source Editor” window will open.

• We need to create connection manager to access the source information (data base objects).
• In OLEDB source Editor window press “New” option to create new connection manager. If we press “New” button, then “Configure OLEDB Connection manager” window will open.
• In that window select “New” option to create connection manager, shown below.

• If we press New button then “Connection manager” window will open.
• In the “Connection manager” window enter the server information (provider, server name, log on to the server and data base name) shown below example screen shot.
• After entering the source server information press “Test Connection” option, to make sure the connection is fine.

• If connection is success then press “OK” button. The connection manager window will get disappears.
• Press “OK button in the “Configure OLEDB Connection manager” window also.Window will get disappears.
• Connection manager creation is finished, choose the Data access made and Table name in “OLEDB Source Editor” window, shown below.

• After that press “OK” option (We can find the “OK” option, bottom of the “OLEDB Source Editor” window).
• Connection manager creation is completed. We can see/edit the connection under connection manager list. Shown below.

• From tool box select “OLEDB destination” then drag and drop into designer surface.
• Select the OLEDB Source green arrow and connect to OLEDB Destination, shown below.

• Right click on “OLEDB Destination” and select “Edit” option.
• OLEDB Destination Editor Window will open.
• Now we can use same connection manager (Used for OLEDB source) for OLEDB Destination also. Choose data access mode and table name (if target table is present in data base)
• If target is a new table, which we needed to create, then press “New” button to create table. Then “Create table” window will open, it contains table creation code (Code is generated based on source field names and data types).

• In the table creation code modify the table name to required name (Target), and press ok button. Target table creation is completed.
• Click on the “Mappings” option in the “OLEDB Destination Editor” window and make sure all mappings are as expected.

• Press ok. All mappings are finished. Execute the package.