Import File Methadology

Question

Post reply

Import File Methadology

Jeff Schultz

SSC Rookie

Points: 44
More actions
April 28, 2004 at 11:01 am

#85490

Hi,
I am in the process of reworking a project done in VB 6 / MS Access to a VB.Net / SQL Server 2000 setup.
Currently, as part of our daily process, when the MS Access db is launched it uses the AutoExec feature to go out to a specific directory and import a semicolon-delimited text file.
1) What method would you suggest to take place of this AutoExec feature I am currently using in SQL Server?
2) Any suggestions as to how this data could be combed through and scanned for errors prior to populating in some of my tables I plan to build.
I am a SQL newbie, but from what I have read a stored procedure might accomplish problem 1) and triggers might be good for 2).
I would be most appreciative to hear what methods more experienced db gurus have adopted to import and verify external data.
Thx

Viewing 4 posts - 1 through 3 (of 3 total)

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

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 1

Hi,

Without knowing the frequency of importing your options are varied.

A DTS package works very well to import the raw data into a 'staging table'. You can even 'clean' the data using a package.

You can use a scheduled job to import and clean the data.

I would definitely recommend putting the raw data into a separate table before manipulating it. This means you don't have to be too specific with the initial import.

Regards

Graeme

etm1109 Ten Centuries Points: 1057 More actions · Answer 2

Bulk Insert or DTS packages can achieve this.

Microsoft has articles on line at MSDN.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_6_040_101f.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_1cxl.asp

I'm more inclined to use DTS packages when I need to modify columns in the import process.

Jeff Schultz SSC Rookie Points: 44 More actions · Answer 3

Thank You Both,

I will look into DTS. I am going to schedule a job to bring in the table and then perform some checks. After the checks have been made and satisfied I will have a seperate procedure to load specific elements from the base table into a number of other tables I am going to build.