Hi,
I need to have a dts package that creates a text file dynamically to
archive data from table.The file name needs to be in the format of
currentdate_table.txt and only one file needs to be created per year.So if
the DTS is on any day other than 1st jan then the data needs to be appended
to the already existing file which was created the 1st day of that year.Is
this doable and if so how?
Your help would be really appreciated.

One way to do this is to use an ActiveX script task at the beginning of your
package to check the date. If the month and day are each equal to 1, you
know you need to create a new file. Create the file using FSO and then set
your text file connection to the new file name using the dynamic property
task.

ActiveX Script Task:
Function Main()

dim MMonth, DDay, YYear

MMonth = DatePart("m", Date)
DDay = DatePart("d", Date)
YYear = DatePart("yyyy", Date)

If MMonth = 1 and DDay = 1 then
'create a new file
dim fso, ArchiveFile, FName
set fso = CreateObject("Scripting.FileSystemObject")

' build filename based on current date
FName = "D:\" + CStr(YYear) + Right("0" + CStr(MMonth), 2) + Right("0" +
CStr(DDay), 2) + "_table.txt"
DTSGlobalVariables("TextFileName") = FName

' the true argument causes an existing file to be overwritten
set ArchiveFile = fso.CreateTextFile ( FName, true)

' write headings with pipe delimters; you might not need this for your
specific package
ArchiveFile.WriteLine ("Heading1|Heading2|Heading3")

ArchiveFile.Close

set fso = nothing
set ArchiveFile = nothing
End If

Main = DTSTaskExecResult_Success
End Function

Dynamic Property task that sets the DATASOURCE property for the DESTINATION
TEXT FILE connection to the global variable, TextFileName, which was set in
the ActiveX script above.

DB Source connection - data pump - Destination Text File:
The data pump task will automatically append records to the file.

Hope this helps.

"Confused User" wrote:

Hello Confused,

Have a look here

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

As for appending. The thing you will need to do is check the date, that's
fine. the behaviour of the driver though is to steamroller over the data
every time you export to it. A lot of people use 2 files and the COPY cmd
to merge files and it seems this may work for you also.

Allan

Thanks for your help. I was able to go little bit further but here is what i
am facing now
1. When I first run the dts package the Connection2 which is my connection
for creating the DTS file saves the file name as destination and on
subsequent runs it recreates the file. which is overwriting my exisiting
file. I do have assigned the global variable as destination.
Questions:
how do I make sure that it always takes the value from the variable
how do i make sure that file is not recreated each time
how do i make sure that data is appended to the exisitng file.

My steps are
Dynamic Properties task ( which defines the global variable)
Activex script ( this assigns the file name to the global variable)
Data Transformation Task
(This extracts the data from the db to text file using connections 1
& 2)


"Stacy McDaniel" wrote:

Here is an example of setting filenames based on date -

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

The problem is that DTS does not support appending data to a file. Always
write to a new file, consider this a staging file, but the name can be
static. You would then bring the two files together, and the best way is to
use DOS copy.

copy "f1.txt"+"f2.txt" "final.txt"

You will need to dynamically change the copy command for your dated
filenames.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Confused User" <ConfusedUser (AT) discussions (DOT) microsoft.com> wrote