CSV File Data Loading in SSIS

Question

Hi All,

Every 10 mins We are getting a Multiple CSV files into one folder. We are loading multiple CSV files into Target(SQL Server database).We are using Foreach loop container to achieve the same.

Scenario:

Let us assume one of the CSV File have 3000 records, loading into Target(SQL Server database), after loading of 2000 records into Target, Source system is down. In the next run the same CSV file Loading , package should pick up from record 2001 to 3000.

How can we achive the above Scenario. We are using 2012.

All helps would be appreciated. Thanks in advance....

Answer 1

Do not load live files, it will not work.

Once a file is done writing to it needs to be moved to a folder from which SSIS will pick it up.

So it loads all or nothing.

---

Arthur

MyBlog

Twitter

Answer 2

2 scenarios:

1) wrap your data loads in transaction statements and don't commit until you've read the entire file.  If it errors off, then rollback the load.  That way, when the file is available again you can load the entire file.

2) write your database load with a lookup... if the record already exists, branch off to an update statement (or a trash destination)... if it doesn't exist, then send it to an insert.

Either of these solutions keep you from having to track what record you were on when a source file suddenly became unavailable.