It is a common scenario, when we need a script, package or something else to perform insert and update in a table simultaneously. Sometimes the task seems easy and sometimes it takes so much time to perfectly synchronize the databases. In this blog post we will try to create a SSIS package that performs the synchronization task by performing upsert (Insert/Update) using SSIS lookup transformation.
1.1 Create Package
1.2 Add Transformations in Data Flow Task
To accomplish the synchronization between two databases, first we need to add a data source in the Data Flow task that must be point to the source database. To add a Data Source double click the Data Flow task added in the Control Flow tab. It will open the Data Flow tab, drag and drop an OLE DB Source from the SSIS toolbox. Double click the Data Source and provide the necessary configuration in Connection Manager and Columns tab, below image shows an overview of our OLE DB Source Editor window.
Figure: OLE DB Source Editor Window
Now we need to identify the record that exists in source database but not in destination database. If we identify those records we can just insert those records in the destination database. We will use SSIS lookup transformation to identify the new record. The logic is simple; we will apply lookup transformation with the primary key of our Person table for source and destination database. The lookup transformation gives us two pipelines one is for matching record and another is for no matching record. No matching record means the records are exists in source database but not in destination database. So we just insert those records in destination database. Lest add a SSIS lookup transformation in our package.
1.3 Add Lookup Transformation
Drag and drop a lookup transformation from SSIS Toolbox to Data Flow tab, connect it with our data source, rename the transformation by a meaningful name. See the below image for overall picture.
Figure: Adding Lookup Transformation. Now we need to configure the Lookup Transformation. Double clicking the transformation will open the Lookup Transformation Editor window. In general tab choose the configuration option like the below image.
Figure: Lookup Transformation General configuration.
In connection tab specify destination data source in the OLE DB connection manager, if you not create destination data source yet, you can create it by clicking the new button. Below the connection manager there are two radio buttons. From the first one, we can select a table name; in this case the whole table will be load. From second option, we can write SQL query and fetch only the required column which we want to use for lookup match. We will use second option since we perform the lookup using primary key (Id in this case). We can use any column that uniquely identifies the record for lookup column. Below image depicts the overall picture.
Figure: Lookup Transformation Connection Configuration.
In columns tab of the Lookup Transformation Editor window we need to map between the lookup columns. See the below image where we map the Id columns from Available Input Columns to Available Lookup Columns.
Figure: Lookup Transformation Columns configuration.
Finally close the Lookup Transformation Editor by clicking the ok button.
1.4 Insert New Record to Destination
To insert new record to the destination database, first we need to add data destination in the Data Flow tab. Drag an ADO NET Destination from SSIS Toolbox and drop it to Data Flow task tab and rename it.
Figure: Adding Data Destination.
Now we need to connect the output of the Lookup Transformation to the destination. When we drag and connect the pipeline it opens Input Output Selection window. In this window we must select the Lookup no match output, which will give us those records that are not in destination database.
Figure: Connect Lookup output to the data source. We use ADO NET not OLE DB Destination.
Double click the destination and configure the Connection Manager and Mappings so that it connects to the destination database and table with required columns. After this step if we execute the package it will insert the record, which exists in source database but not in destination database, into destination database. Below is the snapshot of ADO NET Destination Editor Window.
Figure: Destination Configuration.
1.5 Update Existing Record to Destination
In our Lookup Transformation we have another pipeline, Lookup Match Output. In this output we will get all the records that exists both source database and destination database. The records can have two states
They are identical both in source and destination database.
One or more columns of the record have been modified in the source database.
We don’t need to do anything with the identical record but we must update the destination database so that it will sync with source database with the updated records. For this reason we need another Lookup Transformation that will filter out the modified records.
We have added another Lookup Transformation to the Data Flow task and connect it with the previous Lookup Transformation. In this transformation we use those columns as lookup columns that are frequently updated. Let’s configure the Lookup transformation like below images.
Figure: Create the connection in Lookup Transformation.
Figure: Mapping between the columns in Lookup Transformation.
This Lookup Transformation has also two outputs. Lookup Match Output is identical so we do not need to do anything with that records.
For update the changed records we need to add another transformation name OLE DB Command. We add the transformation and connect it to the Lookup No Match Output of the newly added lookup transformation.
To configure the OLE DB Command double click on it and it will open the Advanced Editor window. In Connection Managers tab change the Connection manager property.
Figure: Configure Command Connection of OLE DB Command.
Now we need an update query for updating the necessary columns. We have used below query for our PARTY table and the column value will be supplied from OLE DB
Command parameters.
Update [CIOSCLI].[dbo].[tbl_Party]
Set [LAST_MOD_D]=?
,[LAST_MOD_T]=?
,[LAST_MOD_USER_ID]=?
,[PARTY_TY_ID]=?
,[ORG_PARTY_ID]=?
,[OBJECT_TYPE]=?
,[PARTY_NM_UPC]=?
,[PARTY_NM]=?
,[PARTY_D]=?
,[PARTY_ED]=?
,[PARTY_S]=?
,[PARTY_LV]=?
,[PARTY_NM_SDX]=?
,[TREE_SORT_CD]=?
,[PARTY_REF_NO]=?
,[UNLINKED_I]=?
,[TEAM_PARTY_ID]=?
,[CR_USER_NM]=?
where [PARTY_ID]=?In Component Properties tab click on the ellipse of the Sql Command properties and it will open the editor. See the below image and insert the query.
Figure: Configure Component Properties of OLE DB Command.
Now we need to map the parameters from Column Mappings tab. See the below image and map the columns with parameter.
The parameters appear here in the same order as the order in the update query.
NOTE : The parameters are in the same order as the Update SQL
1.5.1 Example of parameter assignments:
Update [CIOSCLI].[dbo].[tbl_Party]
Set
[LAST_MOD_D]=? =Param_0
,[LAST_MOD_T]=? =Param_1
,[LAST_MOD_USER_ID]=? =Param_2
,[PARTY_TY_ID]=? =Param_3
,[ORG_PARTY_ID]=? =Param_4
,[OBJECT_TYPE]=? =Param_5
,[PARTY_NM_UPC]=? =Param_6
,[PARTY_NM]=? =Param_7
,[PARTY_D]=? =Param_8
,[PARTY_ED]=? =Param_9
,[PARTY_S]=? =Param_10
,[PARTY_LV]=? =Param_11
,[PARTY_NM_SDX]=? =Param_12
,[TREE_SORT_CD]=? =Param_13
,[PARTY_REF_NO]=? =Param_14
,[UNLINKED_I]=? =Param_15
,[TEAM_PARTY_ID]=? =Param_16
,[CR_USER_NM]=? =Param_17
where
[PARTY_ID]=? =Param_18Figure: Configure Column Mappings of OLE DB Command.
Finally click OK and close the window. Our package is ready now. The complete package image looks like the below image.
_edited_edited.jpg)
תגובות