On a side note, I love DTS. It was taking CF alone minutes to process an import. I had 3 text files, totalling about 10 mb. I created a few DTS packages, run them via CF, and the whole thing now takes about 10 seconds, doing some 70,000+ records. Before it would take 30 minutes. Pretty sweet. : )
----- Original Message ----- From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Wednesday, November 20, 2002 11:10 AM Subject: Re: Importing a text file into SQL Server > OK,. it looks like DTS wins. Thanks > ----- Original Message ----- > From: "Robertson-Ravo, Neil (REC)" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Wednesday, November 20, 2002 10:03 AM > Subject: RE: Importing a text file into SQL Server > > > > yep, this is just like the one on cfcomet. What I would say is this : > never > > begin your SP with the name sp_XXXXX > > > > It wont break anything, but it can have a serious hit on performance as it > > will store that SP within the Master DB and not your DB. > > > > > > > > -----Original Message----- > > From: Joel Firestone [mailto:[EMAIL PROTECTED]] > > Sent: 20 November 2002 15:59 > > To: SQL > > Subject: Re: Importing a text file into SQL Server > > > > > > Bruce: > > > > I just did the same thing recently. You can create and save your DTS > > package > > on the server, and then you can call via a stored procedure in CF. Here's > > the > > SP code: > > > > CREATE PROCEDURE qryClientImport AS > > > > DECLARE @object int > > DECLARE @hr int > > > > --create a package object > > EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT > > if @hr <> 0 > > BEGIN > > print 'error create DTS.Package' > > RETURN > > END > > > > EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile', NULL, 'F:\Temp\FILE > > NAME HERE.dts', '' > > IF @hr <> 0 > > BEGIN > > print 'error LoadFromStorageFile' > > RETURN > > END > > > > EXEC @hr = sp_OAMethod @object, 'Execute' > > IF @hr <> 0 > > BEGIN > > print 'Execute failed' > > RETURN > > END > > > > Hope this helps. > > > > Joel > > > > ----- Original Message ----- > > From: "Bruce Sorge" <[EMAIL PROTECTED]> > > To: "SQL" <[EMAIL PROTECTED]> > > Sent: Wednesday, November 20, 2002 10:37 AM > > Subject: Importing a text file into SQL Server > > > > > > > Hello list. I have a text file that is comma and double-quote separated, > > with a line break that separates the lines. The first row contains the > > header information. What I am wanting to do is to import this into a SQL > > Server 2K table. I know that I can use DTS to do this, but I need to do > this > > in ColdFusion code instead as a scheduled task. I have always in the past > > just used DTS Wizard to do this, not straight SQL. So, my question is, how > > do you import a text file into SQL Server? What would my query look like? > > > > > > Thanks, > > > > > > Bruce > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
