Can I dynamically change output text file name from DTS?
-
I have a series of stored procedures which extract data from a data warehouse.
The output of these extracts should be .csv text files - which will be imported into a 3rd party application of some sort.
I have not been able to locate any sample code anywhere which demonstrates how to dynamically set an output text file's name and other properties.
Is it possible to do this?
Does anyone know of any sample code that shows this?
I'm using ActiveX vb-script to put together most of what I'm trying to do, but I'm stuck at trying to figure out how to dynamically/programmatically build these text files. (It seems like Microsoft thinks that people will only want to import data to SQL Server, not export it! :whistling
-
Use GlobalVariable. This is how you can do it.
- Create a GlobalVariable "gv_FileName" of type STRING.
- Create a TextFile(Destination). Give any name when you create it.
- Create a Dynamic Properties Task.
Inside the Dynamic Properties Task, do the following:
- Click Add.
- This will display Dynamic Properties Task: Package properties menu.
- Expand Connections.
- Click on the name of the TextFile(Destination) connection. (Do not expand)
- On the right side, you can see Data Source under properties.
- Double click on it.
- Change the Source to Global Variable.
- Select the variable name gv_FileName.
- Click OK, Click OK to exit from Dynamic Properties Task: Package properties menu.
You need to create an ActiveX Script Task before branch to the Dynamic Properties Task which initialise the file name using DTSGlobalVariables("gv_FileName").Value = "some text file.csv".
-
Terry,
Many thanks for the information. I'm working on it right now, but it looks like "just what I needed" to figure this out.
I will post back when I have it working.
Is it typical to post up the source code when I have it done?
(This is going to contain multiple ActiveX script Tasks in addition to the Dynamic Configuration objects, etc...)
-
I tried to implement the suggestions made by Terry, but I can't get it to work like I need it.
here's the set-up for it:
I have an existing package which contains about 20 transformations in it. All 20 transformations run SQL procedures to extract data from a warehouse. Each is configured to produce a flat-file in .csv format.
I can open up this package, and loop through all of the connections in the package's connections collection. I can examine each connection looking for ProviderID="DTSFlatFile" to isolate just the destination file objects. The code looks like this:
Set oNewPkg = CreateObject("DTS.Package")
oNewPkg.LoadFromSQLServer "SXADISCOVERY", "", "", "256",,,,"LKruseTestPkg2"
sTmp = ""
For Each oConn In oNewPkg.Connections
If oConn.ProviderID = "DTSFlatFile" Then
For Each oProp In oConn.Properties Then
sTmp = sTmp & oProp & vbCRLF
Next
MsgBox "Conn props are: "&vbCRLF&sTmp
Next
So I can see that Properties(9) contains the output path and filename. But for the life of me, I can't figure out how to be able to modify that value. (I need to insert a subfolder designation in the path.)
Does anyone have any ideas? Thanks.
Larry
-
I am not 100% sure that I understand what you are looking for. According to your code, you are doing through VB?? or in .NET???
My quiestion is are you looking fo a solution in DTS or outside of the DTS? It seems like you are looking for a solution outside of DTS.
Then try this:
Set oNewPkg = CreateObject("DTS.Package")
oNewPkg.LoadFromSQLServer "SXADISCOVERY", "", "", "256",,,,"LKruseTestPkg2"
sTmp = ""
sFilePath = oNewPkg.GlobalVariables("gv_FileName")
For Each oConn In oNewPkg.Connections
If oConn.ProviderID = "DTSFlatFile" Then
oConn.Properties("DataSource") = sFilePath
For Each oProp In oConn.Properties Then
sTmp = sTmp & oProp & vbCRLF
Next
MsgBox "Conn props are: "&vbCRLF&sTmp
Next
I am assuming that you followed my suggestion to generate the GlobalVariable("gv_FileName") and the Dynamic Properties Task. Otherwise the above code would not work.
-
Hey Terry! Thanks again for the info.
I've sent you a PM to clarify what I'm trying to do.
-
Hey Terry!
Aside from a small syntax change, I think what you wrote might work!
Set oNewPkg = CreateObject("DTS.Package")
oNewPkg.LoadFromSQLServer "SXADISCOVERY", "", "", "256",,,,"LKruseTestPkg2"
sTmp = ""
sFilePath = oNewPkg.GlobalVariables("gv_FileName")
For Each oConn In oNewPkg.Connections
If oConn.ProviderID = "DTSFlatFile" Then
oConn.Properties("DataSource").Value = sFilePath
For Each oProp In oConn.Properties Then
sTmp = sTmp & oProp & vbCRLF
Next
MsgBox "Conn props are: "&vbCRLF&sTmp
Next
I have a couple of other logic issues to fix up, this seemed to do the trick and the process is now creating the files in subfolders. One last hurdle to overcome is the syntax for saving the (now) modified package.
Tried this: oNewPkg.SaveToSQLServer _ "SXADISCOVERY","","",DTSSQLStgFlag_UseTrustedConnection,"","","","", ""
But I keep getting a syntax error! I tried putting the parms in parens, but it didn't like that either.
-
Terry, I am following your original reply as all I need to do is output a filename wiht the current year and month in front of some text. i.e. 200703-aa-bb-ccccc.csv. I created the Dynamic Properties Task and the ActiveX Script Task and now I am unsure how to execute this in the package. I am fairly new to this so I need some help!! Also, what is the syntax in the ActiveX Script to add in yyyy and mm to the filename?
-
Following my original suggestion means that you are using one DTS. Therefore you are not using the second DTS which had "oConn.Properties("DataSource").Value = sFilePath" in the ActiveX script.
Your question: what is the syntax in the ActiveX Script to add in yyyy and mm to the filename?
Answer: DTSGlobalVariables("gv_FileName1").Value = sFilePath1 & CStr(Year(Date)) & Right("0" & CStr(Month(Date)), 2) & "-" & sFileName1
My new suggetion: Inside the first DTS,
1. Create 20 GlobalVariables "gv_FileName1" to "gv_FileName20" of type STRING.
2. Confirm that you have 20 TextFile(Destination) connections. I believe you already have these in the first DTS.
3. Create ActiveX script task.
4. Create a Dynamic Properties Task.
To create Global variables,
1. Inside DTS package, right-click on any while space.
2. Select Package Properties.
3. Click Global Variables tab.
4. Click New.
5. Enter name "gv_FileName1", type string, value "any".
6. Repeat #4 and #5 for all the gv_FileNames up to gv_FileName20.
7. Click OK.
Inside the ActiveX script task, do something similar to the below.
Function Main()
Dim sFileName1, FilePath1
Dim sFileName2, FilePath2
...
Dim sFileName20, FilePath20
' Initialise variables first
sFileName1 = "Some File Name"
sFilePath1 = "Some Location"
sFileName2 = "Some File Name"
sFilePath2 = "Some Location"
...
sFileName20 = "Some File Name"
sFilePath20 = "Some Location"
' Initialise global variable
DTSGlobalVariables("gv_FileName1").Value = sFilePath1 & CStr(Year(Date)) & Right("0" & CStr(Month(Date)), 2) & "-" & sFileName1
DTSGlobalVariables("gv_FileName1").Value = sFilePath2 & CStr(Year(Date)) & Right("0" & CStr(Month(Date)), 2) & "-" & sFileName2
...
DTSGlobalVariables("gv_FileName1").Value = sFilePath20 & CStr(Year(Date)) & Right("0" & CStr(Month(Date)), 2) & "-" & sFileName20
Main = DTSTaskExecResult_Success
End Function
Inside the Dynamic Properties Task, do the following:
Click Add.
This will display Dynamic Properties Task: Package properties menu.
Expand Connections.
Click on the name of the TextFile(Destination) connection1. (Do not expand)
On the right side, you can see Data Source under properties.
Double click on it.
Change the Source to Global Variable.
Select the variable name gv_FileName1.
Repeat for 20 connections for corresponding gv_FileNames.
Click OK, Click OK to exit from Dynamic Properties Task: Package properties menu.
To link them together
1. Click ActiveX script task, then CTL-click Dynamic properties task. Then Click Workflow>On Completion.
2. Click Dynamic properties task, then CTL-click connetion1. Then Click Workflow>On Completion.
3. Repeat #2 up to connetion20.
Now you are ready to execute.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply