An SSIS instance obtains multiple files from FTP and incrementally updates the database.

The entire process

Step 1 Place an FTP Task to copy the remote file to a local

After setting up the FTP link Manager is localpatchvariable set to ture and create a variable to set the local path

Operation set to Receive files

Overwritefileatdest set to True

The same set of remote path variables

Step 2 puts a foreach loop that loops the contents of the file into the database

Collection Page Enumerator Select the foreach File enumerator

Folder in the diagram: instead of the directory in the expression, the variable value is directly applied to the Localstorefiles variable created by the FTP configuration.

Files settings unchanged, keep *. * Get all file under Directory

Retrieve file name, select fully qualified (get full name, including directory)

Variable Mappings page added a variable loadfile to get the return value of Fully qualified

Step 3 Place a data Flow Task into the Foreach Loop container and open

1) Create a flat file Connection Manager and select one to configure

Click Flat File Connection Manager, and then configure the expression in the properties to set the link string to the variable LoadFile you just set. Used to get each file

2) Place a Flat file Source and select the Flat file Connection Manager that you just configured

You can now test the effect first. Note that because LoadFile now has no value, when executing the SSIS check may error, we set StoreFile's DelayValidation property to True

3) Place a lookup to determine if the original data is duplicated

The Lookup configuration increment method can also refer to my previous articles

Translation Stairway to integration Services Level 3-Incremental Import data

Translation Stairway to integration Services level 4-Incremental Update data

Translation Stairway to integration Services Level 5-Incremental Delete data

4) Place an OLE DB Destination, change the name to Staging and connect the lookup match Output of Lookup to Staging to hold the matching record

5) Place an OLE DB Destination, rename Stores Table, and then connect the lookup no match Output to the Stores table to put an unmatched record.

Step 4 Drag an Execute SQL task to control Flow to update the matching record

EXECUTE statement

UpdateStoresSetTerritoryID=SS. Territoryid,storename=SS. Storename,address1=SS. Address1,address2=SS. Address2,city=SS. City,state=SS. State,zip=SS. Zip fromStores SJoinstorestaging SS onSs. BusinessEntityID=S.businessentityid

Then link to the foreach Store File Loop

Step 5 Drag an Execute SQL Task to empty the temporary table

STEP6 Moving an imported file

1) Create a File Connection Manager and select the ' Completed ' directory and rename it to complete folder

2) Create a file Connection Manager and choose a CSV file, renamed to Storefilephy

Similarly, set the connection string

3) Drag a file System Task into the Foreach Store file loop

To make the relevant settings

So the whole process is over.

An SSIS instance obtains multiple files from FTP and incrementally updates the database.