January 4, 2002 at 2:21 am
Hi all,
I am trying to pump some data from SQL2K database to Text files. Few requirements making my choice of using which tool and how to do it is making it quite difficult. Probably someone can throw some light on this...
Requirement 1: It has to be a scheduled job and is scheduled to run every hour/2hrs so the filenames should ideally be tablename_datetime. Is there a way to do it in DTS ?
Requirement 2: The data is pumped out from an oltp database so while the data is being pumped out there will be records inserted in to the table as well. After pumping out the rows, the rows which has been pumped out needs to be flagged as transferred so that it does not get picked up again. so i think bcp is not really a good choice.
Any ideas?
TIA.
Kaushik.
January 4, 2002 at 2:39 am
This should be ok:
1: In the DTS package have an initial ActixeX script step which assigns the filename, then schedule the DTS job.
2: You could add a column to the oltp table with a datetime datatype. In an execute sql step, update the new column with the current datetime previously stored in a variable - this applies to all records which have a null in the new column - the new entries. After that, you are just selecting those rows with the latest time - bcp should still be ok for this.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 4, 2002 at 3:43 am
Ok. Here's how I understand this to be done when using DTS.
Create two connections one sqlserver and one Text File(destination) and connect with a tansformation task. While creating the Text File(Destination) connection I need to specify a file name. Now if I have an activex script task (being executed before these tasks) how do I change the file name specified in the Text File (Destination) Connection?
Or is it the way to be done at all?
TIA again.
Kaushik.
January 4, 2002 at 4:04 am
Something like this should work ok:
Dim oPKG
Dim oConnection
Dim sNewFilename
Dim sDatetime
Set oPKG = DTSGlobalVariables.Parent
Set oConnection = oPKG.Connections("myconnection")
sDatetime = cstr(Date) + "_" + cstr(time)
oConnection.DataSource = yourfilename + sDatetime + ".txt"
Set oConnection = Nothing
Set oPKG = Nothing
Main = DTSTaskExecResult_Success
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 4, 2002 at 6:00 am
The way I would use, since I know almost nothing about ActiveX (although I plan to try Paul's way), is to set up the two connections to send to a specified text file, say filename.txt. After sending the data to the file you can use xp_cmdshell to rename the file (to add the date).
When its important to not loose a single text file I place a xp_cmdshell command to rename the file before and after the data pump section so that if the DTS package previously failed prior to renaming the file I still don't loose the file (assuming I haven't had time to find out why the DTS package failed and manually rename the file).
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 4, 2002 at 10:30 am
I'd use the dyanmic properties task to generate a file name from a SQL statemetn and assign that to the text connection.
Steve Jones
January 4, 2002 at 7:11 pm
Hi
We have dome something similar using stored procs and BCP via xp_cmdshell. its very easy to write with no major dramas. Only problem is the xp_cmdshell usage as many DBA's heavily restict its use.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 6, 2002 at 10:42 pm
Thanks Guys,
Was lot of help from your suggestions.
Regards,
Kaushik
January 7, 2002 at 10:22 am
January 8, 2002 at 2:41 am
Chose the DTS way writing an ActiveX task and Paul's script worked right away.
Anybody got ideas on how not to produce a file when there are no records at all ?
Thanks anyway.
Regards,
Kaushik
January 8, 2002 at 10:56 am
I'd produce the file, then check for records using an Active X script and then delete the file if there are no records.
Steve Jones
January 10, 2002 at 5:46 am
Use Dynamic properties, OLE_DB , Datasource
select 'QUERY' and type this select
select '\\Server\Directory\Subdirectory\FILENAME'+(convert (char(6),(SELECT GETDATE()),12))+'.txt'
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply