December 19, 2003 at 8:01 am
Hi,
I am trying to generate text files from SQL query in a DTS Package. Can I set the destination data source (i.e., path for the text file) dynamically using Dynamic Properties Task? I could successfully create a .UDL for Source Connection but I couldn't figure out the Destination Connection.
Any help is greatly appreciated. Thanks in advance.
Thanks,
Sabitha
December 19, 2003 at 2:07 pm
Hi,
You can replace the name using workflow properties for the destination source file step with this example code.
Regards
JFB
'********************************************
' Visual Basic ActiveX Script
'********************************************
Option Explicit
Function Main()
Dim oConn, sFilename
Set oConn = DTSGlobalVariables.Parent
' Filename format - exyymmdd.xls
sFilename = "file"
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = sFilename & Right(Year(Now()), 4)
'DTSGlobalVariables("newFileName").Value = sFilename & ".csv"
sFilename = "\\serverName\folder\" & sFilename & ".csv"
'MsgBox DTSGlobalVariables("excelFile").Value
Set oConn = DTSGlobalVariables.Parent.Connections("Connection 2")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
December 24, 2003 at 1:45 pm
You can set the data source dynamically using the Dynamic Properties task. Typically you would assign the desired connection value to a global variable(s) and then use the Dynamic Properties Task to change the Data Source and/or (depending on the type of db being used) Catalog properties on the connection at run time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply