Import data from Oracle to SQL server using SSIS

Question

We want import data from Oracle to SQL server using SSIS I was able to transfer data from Oracle to one table (Staging)in SQL. then I need to transform data and I found that I need to run stored procedure to transform the data from Staging to Actual production data. But I wonder How we can do it.

EDIT #1

Source table has four Columns with one field containing date but its datatype is string
Destination table has also four Columns but two column will not be stored as it is there is mapping between source column and destination Column
This mapping is stored in two table for both two column Like Table one stores SourceFeatureID, DestincationFeatureID similarly second table stores SourcePID, DestincationPID
Data is updated periodically so we need from destination data when it was updated last and get remaining where SourceDate > LastUpdated_destination_date

**It is better to be more specific.** what have you tried? what is the source structure? destination structure? Any transformation needed? — Hadi, Jan 21 '18 at 10:21
So... the question has nothing to do with oracle and everything to do with how to write a stored procedure? What actual transformation do you want to do? That's the code you put the stored proc. A stored proc is just a bunch of insert/update/delete statements that using variables and control flow. First you need to know what transformation you need to do. Is it an insert? an update? what do you need to do with the data in the staging table — Nick.Mc, Jan 21 '18 at 12:17
1. Source table has four Columns with one field containing date but its datatype is string 2. Destination table has also four Columns but two column will not be stored as it is there is mapping between source column and destination Column 4. This mapping is stored in two table for both two column Like Table one stores SourceFeatureID, DestincationFeatureID similarly second table stores SourcePID,DestincationPID 5. Data is updated periodically so we need from destination data when it was updated last and get remaining where SourceDate > LastUpdated_destination_date — user641812, Jan 21 '18 at 12:56

Hadi · Answer 1 · 2018-01-21T18:10:47.137

Update 1: Components that you can use to achieve your goal within a Data Flow Task

Source and Destination

OLEDB Source: Read from staging table, you can use an SQL command to return only data with SourceDate > Destination Date
```
SELECT * FROM StaggingTable T1 WHERE CAST(SourceDate as Datetime) > (SELECT MAX(DestDate) FROM DestinationTable)
```
OLEDB Destination: Insert data to production database

Join with other table

Lookup transformation: The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.
Merge Join: The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join

Convert columns data types

Data Conversion transformation: The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column
Derived Column transformation: The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.

References

Initial answer

I found that I need to run stored procedure to transform the data from Staging to Actual production data

This is not true, you can perform data transfer using DataFlow Task.

There are many links that you can find detailed solutions:

SSIS. How to copy data of one table into different tables?
Create a Project and Basic Package with SSIS
Fill SQL database from a CSV File (even if the source is CSV but it is very helpful)

Executing stored procedure using SSIS

Anyway, to execute a stored procedure from SSIS you can use an Execute SQL Task

Additional informations:

Thanks but OLEDB Source how i can access StaggingTable and destination table they are from different database and server — user641812, Jan 22 '18 at 05:49

score 0 · Answer 2 · edited Jan 21 '18 at 18:12

I'm not going to go through your comments. I'm just going to post an example of loading StagingTable into TargetTable, with an example of a date conversion, and an example of using a mapping table.

This code creates the stored proc

CREATE PROC MyProc
AS
BEGIN

    -- First delete any data that exists 
    -- in the target table that is already there
    DELETE TargetTable 
    WHERE EXISTS (
       SELECT * FROM StagingTable 
       WHERE StagingTable.SomeKeyColumn = TargetTable.SomeKeyColumn
    )


    -- Insert some data into the target table
    INSERT INTO TargetTable(Col1,Col2,Col3)

    -- This is the data we are inserting
    SELECT 
    ST.SoureCol1,    -- This goes into Col1
    -- This column is converted to a date then loaded into Col2
    TRY_CONVERT(DATE, ST.SourceCol2,112), 
    -- This is a column that has been mapped from another table
    -- That will be loaded into Col3
    MT.MappedColumn
    FROM StagingTable ST
    -- This might need to be an outer join. Who knows
    INNER JOIN MappingTable MT
    -- we are mapping using a column called MapCol
    ON ST.MapCol = MT.MapCol
END

This code runs the stored proc that you just created. You put this into an execute SQL task after your data flow in the SSIS package:

EXEC MyProc

With regards to date conversion, see here for the numbered styles:

CAST and CONVERT (Transact-SQL)

This is why I always comment and never answer. You didn't mention that staging and target were on different servers, meaning this answer is unsuitable for your question — Nick.Mc, Jan 30 '18 at 00:51