| Author |
Topic |
|
igbinosun
Starting Member
13 Posts |
 Posted - 2004-09-16 : 11:22:10
|
| I want to use DTS to make a backup .csv file, and I want to name the output file <input file>+<today's date>. Does DTS have a way to put the system date into a variable, then use the variable to name the file? |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-09-16 : 11:30:12
|
| Use ActiveXScript. This Article shows how to copy/move the files http://www.sqldts.com/?292. HTH..- Sekar |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-09-16 : 11:38:14
|
| Use an ActiveX script task to get the date and then set the property of the text connection. Something like this:Function Main() Dim strName Dim objPkg Dim objCon'Initialise local variables strPath = "C:\"'Put code to get current date and convert to string'in desired format here strName = "[Other text][Date as text].txt"'Instantiate package and connection objects Set objPkg = DTSGlobalVariables.Parent Set objCon = objPkg.Connections("Text File (Destination)")'Set DataSource property of text connection objCon.DataSource = "C:\" & strName'Destroy objects Set objCon = Nothing Set objPkg = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionYou can use vbscript / jscript functions to get the date.Mark |
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-16 : 12:42:07
|
Thank you so much Mark. May God bless you. I'll try this and give you a feedback later.quote:
Originally posted by mwjdavidson
Use an ActiveX script task to get the date and then set the property of the text connection. Something like this:Function Main() Dim strName Dim objPkg Dim objCon'Initialise local variables strPath = "C:\"'Put code to get current date and convert to string'in desired format here strName = "[Other text][Date as text].txt"'Instantiate package and connection objects Set objPkg = DTSGlobalVariables.Parent Set objCon = objPkg.Connections("Text File (Destination)")'Set DataSource property of text connection objCon.DataSource = "C:\" & strName'Destroy objects Set objCon = Nothing Set objPkg = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionYou can use vbscript / jscript functions to get the date.Mark
|
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-16 : 12:48:35
|
OK Mark. I created the ActiveX Script task but when I execute it I get the error: Connection text file(destination) was not found.quote:
Originally posted by mwjdavidson
Use an ActiveX script task to get the date and then set the property of the text connection. Something like this:Function Main() Dim strName Dim objPkg Dim objCon'Initialise local variables strPath = "C:\"'Put code to get current date and convert to string'in desired format here strName = "[Other text][Date as text].txt"'Instantiate package and connection objects Set objPkg = DTSGlobalVariables.Parent Set objCon = objPkg.Connections("Text File (Destination)")'Set DataSource property of text connection objCon.DataSource = "C:\" & strName'Destroy objects Set objCon = Nothing Set objPkg = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionYou can use vbscript / jscript functions to get the date.Mark
|
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-09-16 : 13:23:05
|
| Hi Sekar. The "Text File (Destination)" was just an example. This is what DTS will default to when you create a text file destination connection. You will have to replace with with the name of the connection whose SourceData property you want to update. (Just double-click the connection and take the value from the 'Existing Connection' dropdown)Mark |
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-16 : 13:57:14
|
Mark, once again thank you so much. It works this time except that I'm getting the error: The system cannot find the specified path. I guess it's because of the date being in this format:9/16/04. What escape sequence can I use so that the system doesn't read the forward slash(/) on the date. Thank you and God bless.quote:
Originally posted by mwjdavidson
Hi Sekar. The "Text File (Destination)" was just an example. This is what DTS will default to when you create a text file destination connection. You will have to replace with with the name of the connection whose SourceData property you want to update. (Just double-click the connection and take the value from the 'Existing Connection' dropdown)Mark
|
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-16 : 14:26:23
|
| Never mind Mark. I used the Replace() function to change the date format. Thanks a bunch! |
|
|
|
syedrehman
Starting Member
7 Posts |
Posted - 2004-09-16 : 14:31:30
|
| How about replacing the forward slash with a dash?replace([Date as Text],'/','-')This way you don't have to worry about slashes in the name of a file. |
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-16 : 15:16:47
|
| Thanks you all that responded to my problem. May God bless you all. One last thing I want to be able to do is to pass in the name of the table that I'm moving the data from to the dts package when I execute the package in the command line using dtsrun utility. I'll also be apending this table to the name of my output file. Any idea on how I can do this? Thanks. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-09-17 : 07:05:49
|
| HiI take it you want to make the package dynamic such that you can pass in any table name and have it output this table to a text file.Are you using a transform data task to output the table to the csv file? If so, are you dynamically creating the transformations? If not, this won't work.Mark |
|
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-09-17 : 11:21:06
|
| Thanks Mark. Yes I'm using the transform data task but I don't know how to dynamically create the transformations. Do you mind leading me through this? Thanks and God bless. |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-09-20 : 11:33:37
|
LOL! To whom you are writing too ..  quote:
Originally posted by mwjdavidson
Hi Sekar. The "Text File (Destination)" was just an example. This is what DTS will default to when you create a text file destination connection. You will have to replace with with the name of the connection whose SourceData property you want to update. (Just double-click the connection and take the value from the 'Existing Connection' dropdown)Mark
- Sekar |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-09-21 : 04:57:22
|
| Oops! Apologies Sekar!igbinosun, I haven't forgotten about this, it's just I haven't had time to put together a suitable response. If you want to get cracking in the meantime, start familiarising yourself with the DTS object model:[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_0jsd.asp[/url]Mark |
|
|
|
|
Using DTS