Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Loading a Series of Flat Files Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2002 12:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 1,114, Visits: 335
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ajones/load.asp


Regards,
Andy Jones
Post #7812
Posted Thursday, October 31, 2002 2:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 2,693, Visits: 1,212
Good article. Always like articles with code that I can play with

One comment I would make though is that I'd eliminate the GOTO's by using positive logic. eg:

IF @rtn = 0
begin
do something
if @rtn = 0
begin
do something more
end
else
begin
flag error
end
end
else
begin
flag error
end


Also, is it my browser settings or your formatting that double spaces all the lines?


Edited by - phillcart on 10/31/2002 02:39:37 AM


Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #45130
Posted Thursday, October 31, 2002 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 13, 2005 5:21 AM
Points: 3, Visits: 1
Good article - I agree that it's good to see the code as well. Perhaps it ought to be downloadable, and have a version with your error checking as this is very important.

It would be interesting to write a VB app, using ADO to load the data, to see which would be fastest.

Personally I would use VB, but I'm a little biased !

thanks
John, UK



Post #45131
Posted Thursday, October 31, 2002 8:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 1,114, Visits: 335
Thanks for your comments - some answers: -

I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.
I think the double space formatting is my fault!
With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.


Regards,
Andy Jones



Regards,
Andy Jones
Post #45132
Posted Thursday, October 31, 2002 3:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 18, 2011 5:08 PM
Points: 410, Visits: 7
My biggest project of the last 2 years has been a process that automatically logs and imports files that are uploaded to our server from stores. This process handles both transaction and customer files from 4 different POS systems, each with their own file format. The files also come compressed/archived in .ZIP, .Tar, or .tar.Z formats. (I skipped a lot of details, it gets even more 'interesting'.)

For my solution, I used 3 DTS packages. The first one has an ActiveX script that uses the file system object to loop through the files in the directory, logs any new ones, and performs any extraction that is necessary. This happens every 5 minutes.

The second package, which is executed every 30 minutes, goes through the log (a table) searching for files that haven't imported, and calls the third package for each file that needs to be imported, passing the filename via a global variable.

That's the basics of the process. I personally like the DTS method better because of flexibility, error handling capability, and logging. Whenever an error occurs, I get an email telling me where it occurred, and if it happens in an ActiveX script, the email includes the line of code where the error occurred. I also prefer to keep my DTS packages in SQL Server, its simpler to access them, and they're always backed up.

One final advantage to the way I do it, everything I use is documented. May not be well-documented, but at least there's something there. You want to be careful about using undocumented stuff, especially since it may not always be there.

Anyway, there's my 2-cents worth. If anyone is interested in more information, I'll be happy to provide. My process works; that's gotta be worth something.

James C Loesch



James C Loesch
Post #45133
Posted Thursday, October 31, 2002 4:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 2,693, Visits: 1,212
quote:

Thanks for your comments - some answers: -

I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.
I think the double space formatting is my fault!
With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.


Regards,
Andy Jones



You can still keep the error logging in one place. In my example where I've said flag error this could set an error flag and/or build an error message. Then at the end of the proc you'd just check to see if an error occurred and then log it.

I had a bad experience with goto's as a child and have been mortally afraid of them ever since


Thanks
Phill Carter

Edited by - phillcart on 10/31/2002 4:11:12 PM


Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #45134
Posted Friday, November 8, 2002 5:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:23 AM
Points: 154, Visits: 12
Interesting solution. Thanks for bringing those undocumented xp's to light. :)

Personally, I'd do a couple things different. It's probably the programmer in me, but I'd replace sp_GetFiles, and sp_FolderFileExist with functions. Then instead of working with ##Temp tables, your curser would be based on

select * from dbo.GetFiles(@myfolder)

I'd also use BCP instead of DTS to load the files. Again, personal preference.

(Johnhind, I'd put money on dts bulk load or bcp over any vb solution.)

Thanks again,

John





Post #45135
Posted Monday, November 11, 2002 3:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 1,114, Visits: 335
Good point about using a UDF. With regard to using BCP, my original requirement used files which didn't have a consistent number of fields on each line e.g.: -
1,2
1,2,3
1
etc...

I couldn't get BCP / Bulk insert to work with these types of files, so that was the reason behind using DTS.

Thanks for the reply.

Regards,
Andy Jones



Regards,
Andy Jones
Post #45136
Posted Monday, November 11, 2002 12:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:23 AM
Points: 154, Visits: 12
Andy,

Must be fixed width fields, otherwise you could spedify the delimeter in the BCP command. In the case of fixed width files, you could bcp into a generic table:

create table Import
( id int identity primary key,
data varchar(4000)
)

Then parse the data field as needed in sql. Just an option.

John






Post #45137
Posted Monday, November 11, 2002 3:28 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:06 AM
Points: 593, Visits: 26
I have been doing this for years with stored procs, BCP, and a few control tables. Multiple Import sources, multiple import structures within the same file, going to multiple tables determined by structure of data line.
I was very interested in seeing it done using DTS, excellent article....





Post #45138
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse