Automated Importing of a csv file into SQL 2005

Question

Automated Importing of a csv file into SQL 2005

cnatcc

SSC Rookie

Points: 28
More actions
August 8, 2008 at 6:21 am

#382203

Hi guys.
I am new to SQL 2005. My boss has just asked me to create an automated process that will load data from a csv file everyday at midnight.
Can this be done on SQL 2005 - as a SQL 2005 configuration perhaps?
Please help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

well, without much insight onto the nature of the csv file to load, what you are asking for can be really simple.

setup a flat file connection to point to the csv to connect to and an ole db connection for your sql server. create a data flow with a flat file source and ole db destination to point to your sql server and connect the to mapping the columns from the flat file to the sql server table

after you load the data, you will want to archive the file, this can be as simple as moving the file to an archive folder

as far as the timing, that can be done via a sql job to kick off at your designed time (midnight as you say)

this is a start, they are probably data validations you want to consider and so forth but the basics are above

hth

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 2

you can deploy the package as a job using Sql Agent and set the job to run at a specific time

i hope this link helps

http://www.mssqltips.com/tip.asp?tip=1180

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

cnatcc SSC Rookie Points: 28 More actions · Answer 3

Thanks guys - you're the best. Did as you said and sure enough my project is working!

Thanks again!

JBANKO SSC-Addicted Points: 453 More actions · Answer 4

But HOW do you change the NAME of the file dynamically in the connection? I know where the file is but not the name until it appears in the source file AND there may be more than one that needs to be processed. I have the archiving part working but have been beating my head against this one thing.

Thanks,

Joe B

ps, sorry if I'm hijacking this thread but I need to have this working by tomorrow and it looks like this is an active thread

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 5

Joe

On my corporate Blog, I have a blog detailing how to loop through a collection of files and load them into sql server database without knowing the names of the files (just the structure)

The blog can be found here: http://www.rdabiblog.com/

The article is under SSIS Dynamic File Name Load

hth

JBANKO SSC-Addicted Points: 453 More actions · Answer 6

JBANKO

SSC-Addicted

Points: 453

August 10, 2008 at 1:47 pm

#855795

Thanks Chuck. On my way there.

Joe B

JBANKO SSC-Addicted Points: 453 More actions · Answer 7

Wow. That's really weird!!! I saw that earlier today and tried it and it wouldn't work. In the meantime I had been trying other things and after your post back I went and tried it again. Evidently I screwed something up the first time because it worked like a charm this time.

Thanks again,

Joe B