SSIS/CSV Architecture question

Question

Hi All,

I'm building a series of SSIS packages which will be designed to do the following:

1.) import a few thousand rows from a CSV file into a series of SQL 2005 tables 2.) export a few thousand rows into a series of CSV files

I've a few concerns before I dive in:

a.) Before I import my csv files, I first want to check that the files I'm importing are not open or being written to by another application. Is there any way I can do this within SSIS?

b.) As I'm going to be transferring the data over a VPN, I'm concerned over the time it's going to take to, first transfer the data from the CSV file and second to transform the data in my sql tables. It's not just a straight import - I've got to transform some of the columns in the csv rows to valid values for my SQL table and also check whether they are inserts or updates based on the existing data in my DB. Should I first look at transferring the csv data into temp tables and then look at writing futher transformations into my main tables (i'm thinking this might be more robust than trying the 2 steps on the fly.)

Your advice is greatly appreciated!

Ed

Answer 1

a. You could check in the controlflow if your file is in use with an scriptblock.

b. First copy your files to a local area then import them from that location. Use a staging db for importing your files into rawtable then transform and decide to insert your rows into table4update or table4insert after those tables are inserted you could insert/update your destination table with single sql statement(controlflow). Don't forget to think in batches. Truncate your staging tables before reading.