DTS Transform Data Task to create text files dynamically

[kavita11]

I am trying to following in a DTS PAckage:

I have an access database with a table with 2 fields and I have the same data in SQL Server in another table.I need to pull the data from the access and link a particular number to an ID field in the SQL server table. The records in the Access database have individual numbers on them and I want to export the data row by row into different text files with the ID from the SQL server table as the name of the text file Example :4556.txt if 4556 is the ID for that row. Is there a way to do this export. Exporting the whole table to a text file as destination is no problem creating, individual files at run time is the issue.

Please advise

Thanks
Kavita

 

[kavita11]

This worked. I used one MS access connection and one SQL server connection. I did a look up from the SQL to match the IDs I needed to name my text files. In the Active X script I added the look up and created a file system object.Using FSO I was able to create the file and specify the file name as the ID.txt that I wanted and then also was able to write to the file the content from the access database that I wanted. Dont forget to close the file and discard the object in the end. I got all individual text files for each record in my Access database created on the server with a .txt extension. If you use the regular Write transformation there is no file extension being created by DTS, though you can still open and read from the file.

Thanks,
Kavita