SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Automating Import of CSV
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taracad
Starting Member

Canada
1 Posts

Posted - 03/04/2013 :  11:24:12  Show Profile  Reply with Quote
Hello,

This is my first post. Wanted to provide the problem scenario.

Our marketing department gets a ftp drop of 6 CSV comma delimited files every week. They want to import this file to a new SQL database.

The files are the same files from the previous week but with updated entries so it is like a running update.

Ideally what I would like is to have a new database created each week once the csv files are in and create tables in that 1 database for each file. So the database named for the Date of the csv files and tables name with the name of the csv file.

Is there any way to automate the import into the tables? I was able to do manual import to the tables, but I wanted to know if there was a automated way to do this. Understand that the csv files have over 30 header columns so creating a query to bulk import them would be difficult as I would have to create a schema with a lot of headers.

Kris

Kris Taracad

James K
Flowing Fount of Yak Knowledge

3639 Posts

Posted - 03/04/2013 :  13:55:54  Show Profile  Reply with Quote
I would recommend that you NOT create a new database each week. Create a single database, and perhaps even a single table. Whether you need one table or two or three or more tables depends on your processing requirements. But definitely do not create a table for each week. Assuming you create a single table, that table should have all the columns that the CSV file has. In addition, it should have another column to indicate the date on which you imported the data into the table.

Once you have done that, you can use SSIS or any of the other methods to import the data into that table. If you are able to successfully import the data into the table once manually, you then can automate it by scheduling the import process as a SQL Agent task (or using any scheduler including Windows scheduler or 3rd party schedulers).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/05/2013 :  00:08:08  Show Profile  Reply with Quote
quote:
Originally posted by taracad

Hello,

This is my first post. Wanted to provide the problem scenario.

Our marketing department gets a ftp drop of 6 CSV comma delimited files every week. They want to import this file to a new SQL database.

The files are the same files from the previous week but with updated entries so it is like a running update.

Ideally what I would like is to have a new database created each week once the csv files are in and create tables in that 1 database for each file. So the database named for the Date of the csv files and tables name with the name of the csv file.

Is there any way to automate the import into the tables? I was able to do manual import to the tables, but I wanted to know if there was a automated way to do this. Understand that the csv files have over 30 header columns so creating a query to bulk import them would be difficult as I would have to create a schema with a lot of headers.

Kris

Kris Taracad



so far as the csvs are all having no changes in structure(metadata) you can use logic like below for upload.

http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html



Add this package execution as a step inside sql server agent and it will execute it automatically based on predefined schedule



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000