Renaming Destination in DTS

I searched, but couldn't find anything to match what I am looking for...

Basically, is there any way to tell DTS to create a new table each time that the backup is run? I am scheduling the backup for 1 hour intervals for 5 days, but need the databases that are backed up to be unique, so i would end up wiht 120 of them total.

Is there any way to do this through DTS? Or am I hosed?

Thanks in advanceYep, you can do it. You'll have to adapt what's in the links, but it does work. You can pass variables into a DTS job and have it alter execution each time it's run.

http://www.swynk.com/friends/green/textfile.asp
http://www.swynk.com/friends/green/DTSHowTo3.asp

http://www.sqldts.com/
http://www.dts2000.com/

Renaming Database

I got an MSSQL database which I'm trying to export to MySQL using DTS, the name of the database is MyDatabase.com and when I try to export the tables I get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Unspecified error
Could not find server 'MyDatabase.com' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Context: Error calling GetRowset to get DBSCHEMA_TABLES schema info. Your provider does not support all the schema required by DTS.

My guess is that since the database name ends with .com DTS is thinking that it's a server and not a db..... I got 2 databases that end with .com and a few others that don't, if I try to export any of the ones that end with .com I get the same error, but if I try to export another one it works just fine... My question is, is there any way I can rename the database to something else or is there any other way around this problem..?

Thanks in advance,
Fernansp_renamedb will rename a database.

Have you tried [MyDatabase.com]sql

Renaming a DTS package

I would like to rename a DTS package I've created, but I can't seem to find a way to do so.
Is there a table that contains all the packages with there properties somewhere? Thanks for the replyOriginally posted by WhiZa
I would like to rename a DTS package I've created, but I can't seem to find a way to do so.

Is there a table that contains all the packages with there properties somewhere? Thanks for the reply

Hello WhiZa.

Open that DTS package and save it as another DTS name. Then delete the original DTS package if you do not wish to keep it.

Best regards
Teck Boon

Rename File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt
'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change th
e
> file name after it is created to add the current timestamp in the file nam
e
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.t
xt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script '
Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
>|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
new|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:

> you might be able to use xp_cmdshell and pass in the rename of the file ju
st
> like you would from dos.
> so you already know the path the the file and the name of the file. you ju
st
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
>
> --
> new
>|||'***************************************
*******************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'***************************************
*********************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMM
DDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(syst
ime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & c
str(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)|||'***************************************
*******************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'***************************************
*********************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMM
DDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(syst
ime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & c
str(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)

Rename File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.
You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change the
> file name after it is created to add the current timestamp in the file name
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script ?
Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:

> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
new
|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:

> you might be able to use xp_cmdshell and pass in the rename of the file just
> like you would from dos.
> so you already know the path the the file and the name of the file. you just
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
>
> --
> new
>
|||'************************************************* *********************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc="\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf=fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
'Dim tsa
'Const ForAppending = 8
'set tsa=saf.OpenAsTextStream(ForAppending)
'tsa.writeline cstr(systime)
'tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)
|||'************************************************* *********************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc="\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf=fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
'Dim tsa
'Const ForAppending = 8
'set tsa=saf.OpenAsTextStream(ForAppending)
'tsa.writeline cstr(systime)
'tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)

Rename File

I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
Is there a way I can do this ?. T-SQL syntax from DTS package ?
Thanks for any help.You can do it before creating it.
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200
AMB
"DXC" wrote:
> I have a text file (*.txt) created from a DTS Package. I need to change the
> file name after it is created to add the current timestamp in the file name
> (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script '
Thanks.
"Alejandro Mesa" wrote:
> You can do it before creating it.
> How can I change the filename for a text file connection?
> http://www.sqldts.com/default.aspx?200
>
> AMB
> "DXC" wrote:
> > I have a text file (*.txt) created from a DTS Package. I need to change the
> > file name after it is created to add the current timestamp in the file name
> > (i.e. date and time).
> > Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'
> >
> > Is there a way I can do this ?. T-SQL syntax from DTS package ?
> >
> > Thanks for any help.|||you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.
so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.
that's really about the easiest way to do this imo.
DXC wrote:
> I have a text file (*.txt) created from a DTS Package. I need to change
> the file name after it is created to add the current timestamp in the file
> name (i.e. date and time).
> Ex: The filename is 'Myfile.txt' and I need to make it
> 'Myfile0506051022.txt'
> Is there a way I can do this ?. T-SQL syntax from DTS package ?
> Thanks for any help.
--
new|||Thanks. I am already using something like this:
declare @.rename varchar(255)
select @.rename ='ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @.rename
to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:
declare @.rename varchar(255)
declare @.filename datetime
Set @.filename = (LEFT(GETDATE(), 12) )
select @.rename ='ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@.filename'
+ '.BAK'
exec master..xp_cmdshell @.rename
Thanks..........
"beginthreadex" wrote:
> you might be able to use xp_cmdshell and pass in the rename of the file just
> like you would from dos.
> so you already know the path the the file and the name of the file. you just
> need to create a varchar with the correct formatted date/time in the name
> and pass the entire rename/move command to xp_cmdshell just like from dos.
> that's really about the easiest way to do this imo.
>
> DXC wrote:
> > I have a text file (*.txt) created from a DTS Package. I need to change
> > the file name after it is created to add the current timestamp in the file
> > name (i.e. date and time).
> > Ex: The filename is 'Myfile.txt' and I need to make it
> > 'Myfile0506051022.txt'
> >
> > Is there a way I can do this ?. T-SQL syntax from DTS package ?
> >
> > Thanks for any help.
> --
> new
>|||'**********************************************************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)|||'**********************************************************************
' Visual Basic ActiveX Script
' Author: Keith Kosmicki
' Date 10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = "\\Web1\ftp\Prime Vendor\test1"
Set fso = CreateObject("Scripting.FileSystemObject")
set saf = fso.GetFile(StrAccessSrc)
systime=now()
'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS
'Sub Comment(af)
' Dim tsa
' Const ForAppending = 8
' set tsa=saf.OpenAsTextStream(ForAppending)
' tsa.writeline cstr(systime)
' tsa.close
'End Sub
Sub RenameSFile(sf)
StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
sf.move StrNew
End Sub
Best,
Kos
User submitted from AEWNET (http://www.aewnet.com/)sql

Rename a file

Hello,

I have a DTS process to import data from txt file to the sql.
The File name I import is LLEEOD.txt

and the directory is from a d:\ftp\newdata\

When my import process completes, I have to rename that file to the current date, lets say 01-11-2006.txt

I was trying to create another DTC (txt to txt) , but I have to provide a name myself. which is a problem, since this is a weekly process.

Is there are other ways to rename the txt file>

Thank you very much.I have not enough experience doing these kind of things.. but that should be possible using an activex script in DTS.|||thank you, but I do not have exp. in activex.|||I found a half way to my solution,
CAN ANYONE HELP ME OUT WITH REST OF IT?

CREATE PROCEDURE [dbo].[Table_Rename] AS

Declare @.NewName varchar(100)

Set @.NewName = cast(datepart(mm,getdate())as varchar(2))
+ '-' +cast(datepart(dd,getdate())as varchar(2))
+ '-' +cast(datepart(yyyy,getdate())as varchar(4)) +'ord'

Select @.NewName
print @.NewName

EXEC sp_rename 'Test_1', @.NewName
GO

Thank you.

removing existing connecitons in a DTS

is it possible to remove connections created in a DTS?i realized that expressed this way this question is silly, so let me explain myself

in a dts you can drag and drop a conection to dts working area and doing this an icon representing the connection appears. Now you can easily remove the connection just selecting the icon and pressing Del key.

But you can also open connection icon properties and select new connection, provide a new name and change connection properties. Doing this way NO connection icon appears but simply another connection is added to connection collection. If you select disconected edit in package right button menu you will see two connections. So there're two connections but only one connection icon. How can you remove the connection without icon?|||Not silly at all.

It can be accomplished through VB.

The connection information is part of the Connections object and can be modified and saved using VB.

see the thread

http://www.dbforums.com/t373639.html