x

Import Data into an Existing Table

Okay, I have a table into which I import a comma delimited text file each day. Right now this whole process is performed manually by end users in an Access front end. I want to automate the process using a Job to import the data and disperse it to the work flow tables without the end users having to do anything.

Although I still have to work out how to teach SQL to find the exact file, I have someone working with me on that one.

But I am stuck on the import/inserting of the data. I have spent hours looking through Bulk Insert and Tasks and other options. I cannot find anyway to import this data directly into the table, every thing I find has a CREATE TABLE line to start the whole process. The import table itself is emptied each day after the import, but since I was working from the front end initially, and (based on experience, not so much knowledge) Access does not seem to like temporary table. The queries to manipulate the data and disperse the data to other tables, so the table exists.

So far, I am kind of stuck. I would rather not have to rebuild every one of the steps in my Job to accommodate a temp table

Should my work to automate this process change the process in its thinking to using a temp table, would that save me a lot of frustration?

Also, I set up a task using the Task Editor, but I have no idea where it went. I can't find the Task anywhere. It is a one-time kind of thing? If not, where can I find the task and can the file to import name contain wild cards? The report drops every day with a static first part of the name, underscore, date in MMDDYY, and time in HHMMSS. Since the time is unnecessary, could the file name be something like: \networkLocation\Folder\StaticFileName_(GetDate())%.txt? There would probably be some format statements in there I am guessing to make sure SQL knew it should look for today's date in the MMDDYY format.

Thanks everyone!

more ▼

asked Oct 12, 2009 at 07:03 PM in Default

Darren gravatar image

Darren
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Right-click database in SSMS, Tasks..., Import Data. Use the wizard to step through which fields in your CSV file you want to import. At the end of the wizard you'll be asked to save this SSIS package (the wizard is creating an SSIS package for you behind the scenes). Save it. Once you do you'll be able to schedule this package to run on a set schedule and not have to manually intervene again.

more ▼

answered Oct 12, 2009 at 08:34 PM

Jorge Segarra gravatar image

Jorge Segarra
419 2

The only caveat would be if the file name changes. Then you would need to "search" for the file.
Oct 12, 2009 at 10:59 PM Jack Corbett
Ahh good call Jack, thanks for comment!
Oct 13, 2009 at 12:43 PM Jorge Segarra
(comments are locked)
10|1200 characters needed characters left

Hi - Jorge is absolutely right that the Import/Export Wizard is the best place to create a starting point SSIS package. You will generally find however that you need to make some edits on that package (in BIDS - Business Intelligence Development Studio) to make it work repeatedly.

For example, if you don't want the task to repeatedly try and CREATE TABLE each day (if I understood your question correctly) then you may well need to make changes to the package to reflect this. Also, if you want the package to pick up different source flat-files each day with different filenames, this would also need to be done by changing the package.

Additionally, if you want to disperse the data to different locations (rather than just a single table), this can also be done by editing the SSIS package. Finally you can add in features such as "Email me when something goes wrong!" and so on which can be very helpful..

more ▼

answered Oct 13, 2009 at 03:03 AM

Ben Rees gravatar image

Ben Rees
61 2 2 8

(comments are locked)
10|1200 characters needed characters left

My answer to this is to start with SSIS and export the resulting code to VB.NET. (Yes there is some massaging going on) This allows me to use a configuration file so that the VB.NET can modify the SSIS parameters dynamically. I then configure the program on the user's desktop which allows each user to do the import from their desktop by using the VB.NET program.

more ▼

answered Oct 16, 2009 at 02:31 PM

dvroman gravatar image

dvroman
1.1k 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66
x7
x1

asked: Oct 12, 2009 at 07:03 PM

Seen: 4328 times

Last Updated: Oct 15, 2009 at 06:07 AM