August 19, 2003 at 6:30 am
I need to be able to export data from my SQL Server 2000 database into a set of flat files (we have selecetd CSV) for transfer to other databases on separate networks. Because different users of the database may want to export different data subsets I want to be able to choose the location of these files based on some string parameters supplied and thereby avoid one user conflicting with another. I can specify a fixed path and filename in the Connection Properties but not a variable path. I can also specify just a filename but don't seem to be able to control the default folder for the creation of the output files.
Any ideas?
August 19, 2003 at 6:52 am
Always Export the file to the same location and then based on parameters, use XP_CMDSHELL to copy or move it to another directory.
August 19, 2003 at 9:55 am
You can use the filesystem object in an activeX script to manipulate files and their paths. Capture the new path\file in a global string variable and assign it dynamically to the connection path with the dynamic properties task. It's easy!
Here is a sample activeX script where I use a pre-formatted template. On success, go to the dynamic properties task and set the Excel connection to gsSetPath. That's all there is to it!
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oFso
Set oFso = CreateObject("Scripting.FileSystemObject")
Dim sMonth, sDay, sDate, sSaveFile, sTemplate
If Len(Month(Date)) = 1 Then
sMonth = "0" & CStr(Month(Date))
Else
sMonth = CStr(Month(Date))
End If
If Len(Day(Date)) = 1 Then
sDay = "0" & CStr(Day(Date))
Else
sDay = CStr(Day(Date))
End If
sDate = CStr(Year(Date)) & sMonth & sDay
sTemplate = "\\MyServer\MyFile_TEMPLATE.xls"
sSaveFile = Replace(sTemplate, "TEMPLATE", sDate)
oFso.CopyFile sTemplate, sSaveFile, True
Set oFso = Nothing
DTSGlobalVariables("gsSetPath") = sSaveFile
Main = DTSTaskExecResult_Success
End Function
J. Moseley
[font="Courier New"]ZenDada[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply