SSIS: Step-by-step teaching you to use temporary tables in SSIS

Demand:

exist A and B two databases, they all exist a T1 table, a database of T1 table is the source, B database T1 table is the purpose, we want to keep two tables synchronized, that is: A.T1 exists, B.T1 table does not exist insert.

Solution:

1. Create a temporary table testtmp in the B database to store the data in the A.T1

2. Compare b.testtmp and B.T1 data

If a record exists in b.testtmp and b.t1 does not exist, the record is inserted into the B.T1 table.

Disclaimer: Replace the A database with the SSIS2015 database. Use the TSQL2012 database to replace the B database.

Preparatory work:

1. Create the T1 table in the SSIS2015 database and insert the data:

--A Database T1 tableif object_id('dbo.t1') is  not NULLDrop Tabledbo. T1;Go Create Tabledbo. T1 ([Key] int  not NULL, col1nvarchar(Ten)NULL);GoInsert  intoDbo. T1 ([Key], col1)Values(1,'1'),        (2,'2'),        (3,'3'),        (4,'4'),        (5,'5'),        (6,'6'),        (7,'7'),        (8,'8'),        (9,'9')

2. Create a T1 table in TSQL2012

if object_id('dbo.t1') is  not NULLDrop Tabledbo. T1;Go Create Tabledbo. T1 ([Key] int  not NULL, col1nvarchar(Ten)NULL);GoInsert  intoDbo. T1 ([Key], col1)Values(1,'1'),        (2,'2'),        (3,'3')

Steps:

1. Using an Execute SQL Task, rename the Create temp table and enter the following code in the SQL statement property bar to create the corresponding temporary table. Select the desired connection, where I use the local server,database to use TSQL2012 as the B database, which is the source database. Other properties remain the default settings. For more information, refer to figure 1-1.

Note that when using temporal tables, the properties of the corresponding connection retainsameconnection to be set to True (refer to Picture 1-2 for setting)

The SQL statement:

if object_id('tempdb: #TestTmp') is  not NULLDrop Tabledbo. #TestTmpCREATE TABLE#TestTmp ([Key] int  not NULL, col1varchar(Ten)NULL    ) 

Excute SQL Task:

Figure 1-1

Figure 1-2

2. Drag a DataFlow Task to cache the data in the SSIS2015 to a temporary table in the TSQL2012 database.

2.1 Gets the data in the SSIS2015.

Drag an OLE DB Source component in the dataflow Task. Gets the data in the Ssis2015.t1 table.

The property settings for the OLE DB Source component are as follows:

2.2 Insert the data obtained from the SSIS2015.T1 table into a temporary table in the TSQL2012 database.

Pull an OLE DB Destination component, insert the data into the staging table in the TSQL2012 database, and follow the configuration component properties. (Note that setting the component's Validateexternalmetadate property is false. Spokeswoman: Right-click the component, select Properties, locate the property in Commen properties, and set it to false.)

3. With a Excute SQL task, insert data that exists in the temporary table but does not exist in Ssis2015.t1 to Ssis2015.t1.

Reference set this component property

The SQL statement:

Insert  intoDbo. T1 ([Key], col1)Select [Key], col1 fromdbo. #testtmp astmpwhere  not exists(Select 1 fromdbo.t1whereT1.[Key]=Tmp.[Key])

4. Delete the temporary table with a Excete SQL task.

Reference Setting Properties:

The SQL statement:

if object_id ('tempdb: #TestTmp'is not anulldroptable dbo. #TestTmp

Now that the setup is complete, we run the package, press F5 or click Start on the toolbar and the package will run successfully.

Look at the data again, clang clang, the data inserted successfully!

If this solution helps you, please help to top it, thank you very much.

Copyright NOTICE: If there is a reference, please specify the source, keep all copyright of the blog.

SSIS: Step-by-step teaching you to use temporary tables in SSIS