How can I organize data transfer between CSV and SQL?
I am creating a series of SSIS packages that will be developed to do the following:
1.) import several thousand rows from a CSV file to a series of SQL 2005 tables 2.) export several thousand rows to a series of CSV files
I have a few problems before I dive in:
a.) Before importing the csv files, I first want to check that the files that I import are not opened or written by another application. Is there a way to do this in SSIS?
b.) Since I am going to transfer data over VPN, I am worried about the time it has to take, first transfer the data from the CSV file and then to convert the data to my sql tables. This is not just a direct import - I need to convert some columns in csv rows to valid values for my SQL table and also check if they are inserts or updates based on existing data in my DB. Should I first look at passing csv data into temporary tables and then look at writing further conversions to my main tables (I think this might be more reliable than trying 2 steps on the fly.)
Your advice is greatly appreciated!
source to share
and. You can check the control stream if your file is in use using a script block.
b. First copy the files to your local area, then import them from that location. using the executable db to import your files into rawtable, then transform and decide to insert your rows into table4update or table4insert after those tables are inserted, you can insert / update the target table with a single sql statement (controlflow). Remember to think in batches. Exaggerate your seniority charts before reading.
source to share