Changing Flat File Destinations

Question

Changing Flat File Destinations

cdun2

SSCertifiable

Points: 7893
More actions
February 23, 2007 at 3:32 pm

#68713

Hello,
I have a situation where SQL Server table data needs to be exported to flat files on a daily basis. The flat file name needs to contain the current date at the end of the file name. This would mean that the destination Flat File would have a different name every day.
I imagine that this needs to be done in a script task. If so, are there any examples out there where I can pull SQL Server table data, use FileSystemObject to create the daily file, and then export the data to the file?
Thank you for your help!
CSDunn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I don't have a detailed example, but you could just use the text document destination connection object and push your data to a working file. Then follow that up with a script task to rename the file, or stream the file contents into your new file. You could even use a .bat file for the file rename or copy.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thank you for your response. That is actually the process in place now. The problem is, the working file gets locked by some other process. I'm trying to take the working file out of he process, and output the data to a new time stamped text file each time the process runs.

I'm checking with the (singular) user of the output. It could be that I may be able to just output the data into another sql server table, and create a small MS Access ADP for the user, where they could use a date filter to get the data they need.

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 3

This is 3 step process as follows:

Create a Global Variable "FilePath".
Create an ActiveX Script Task as below:

Function Main()
' Initialise variables first
DTSGlobalVariables("FilePath").Value = "\\Server\Directory\FileName." & CStr(Year(Date)) & Right("0" & CStr(Month(Date)), 2) & Right("0" & CStr(Day(Date)), 2)
' Other script continues here
'
'
Main = DTSTaskExecResult_Success
End Function

3. Create a Dynamic Properties Task as follows:

Select Tasks>Dynamic Properties Task...
Click Add.
Expand Connections.
Click on the name of the connection for the text file. (Do not expand.)
You will see Property name nad Default vaue in the right pane.
Double click on the DataSource.
You will see the Add/Edit Assignment menu.
Select Global Variable for the Source.
Select FilePath for the Variable.
Click OK, click Close, then click OK.

Save the package and run.

If you run the package using the Job, then you need to know the SQL-Agent NT login. This login must have proper access permission to \\Server\Directory if your file path is shared directory. Otherwise your job will fail.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Also, before changing your existing process, it may be worth finding out who or what is locking the file. Check the virus scanning software on the server. Make sure it is set up to omit the directory where you are dropping the working file. If this is not it, have a server SA help you attempt to identify who/what is locking the file the next time you run into that situation.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

Thank you for your help! I implemented a similar solution with a Dynamic Properties Task, but with a SQL Statement using the string for the file path + GETDATE() (I had an existing connection to a SQL Server).

cdun2