moving and renaming a file

I have a DTS package that I currently execute manually. This DTS package
reads a text file and imports it into a table.
I would like to have it run automaticly.
For that to work correctly, it would need to check to see if the txt file is
there (the name is always teh same), if it is not there, then to quit. If
it is there, to read and import it as normal, then once completed, for it to
rename teh file and move it to another location on teh computer.
How do I do that?I'd use ActiveXScipt and the FileSystemObject. Here is some sample code:
Function Main()
dim fso, source, dest, oldFile
set fso = CreateObject("Scripting.FileSystemObject")
source = "\\myBox\FAAIVR\BENEFIT_GROUP_INFO.txt"
oldFile = replace(source, ".txt", "_" & replace(date - 7, "/","_") & ".txt")
if (fso.FileExists(source)) then
dest = replace(source, ".txt", "_" & replace(date, "/","_") & ".txt")
fso.MoveFile source, dest
end if
if (fso.FileExists(oldFile)) then
fso.DeleteFile (oldFile)
"Johnfli" wrote:
> I have a DTS package that I currently execute manually. This DTS package
> reads a text file and imports it into a table.
> I would like to have it run automaticly.
> For that to work correctly, it would need to check to see if the txt file is
> there (the name is always teh same), if it is not there, then to quit. If
> it is there, to read and import it as normal, then once completed, for it to
> rename teh file and move it to another location on teh computer.
> How do I do that?
>
>

Moving all DTS packages from server to server

Hi,
Is there any way of copying ALL DTS Packages from server to server
apart from direct copy of sysdtspackages table and saving them one by
one?
ThanksThis tool can copy all packages or individual packages, and it also has
a command line version, so you can automate copying:

http://www.sqldts.com/default.aspx?242

Simonsql

Moving a DTS to another server

Hi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.

I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.

I tried the wizards but just got in a mess.

ThanksDTS stands for Data Transformation Services. It's a part of Sql Server that is useful for copying tables and other database objects, importing and exporting data, and doing other ETL (extract, transform, load) functions. While you may want to use a DTS package to move your database it doesn't by itself represent the set of tables, views, stored procedures, and other objects that make up a database.

Options for copying or moving a database include:

You can detach a database, copy the mdf and log files to another server, and then re-attach them on the other server. You need to detach from the current db before you copy because otherwise it will say the files are in use.

You can backup the database, copy the backup to the other server, and then restore it there.

If can connect to both database servers from Enterprise Manager you can use DTS to copy all the database objects (tables, views, stored procedures, etc.) to the other server. There is a wizard within Enterprise Manager to help you do this. Right click on the database, select All Tasks->Export Data. After selecting the source and destination servers, select "Copy objects and data between SQL Server databases". It will prompt you for various details about what to copy and whether you want to save it as a DTS package.|||Thanks for that, can I ask how do I go about detaching the database prior to saving the mdf file and also could you tell me where this mdf file resides?

Thanks again|||You can detach a database this way (just make sure that it's not currently in use):

sp_detach_db 'MyDB'

By default, databases are located at c:\Program Files\Microsoft SQL Server\MSSQL\Data and the logs are at c:\Program Files\Microsoft SQL Server\MSSQL\LOG. But you can change the default for a server or for individual databases, and you can scatter filegroups all over the place.

If you have Enterprise Manager, you can right-click on the database name, select Properties, and look under the Data Files and Transaction Log tabs to see where the files are located. If not, you can get the information using osql.

Enough information?

Don|||To detach a database:

1. Shrink it first to make the file sizes smaller. (This is sort of like compacting and repairing an Access file.) Select the database in Enterprise Manager, right-click, select All Tasks->Shrink Database... Click OK on the dialog that comes up.

2. Backup the database just in case: All Tasks->Backup Database...

3. Detach database: All Tasks->Detach Database... (If any process is still using the db it won't let you detach until they are cleared. If the database is being used for replication it also won't let you detach it).

4. File location. They are probably in C:\Program Files\Microsoft SQL Server\MSSQL\Data. Look for two files names: YourDbName.MDF and YourDbName.LDF. They should have very recent timestamps for when you detached them. If the files are not there just search the hard drive for one of those names. The db files don't have to be in that folder but it's the default if not otherwise specfied. If you are using MSDE then the path will be slightly different.

Move or delete file after uploading via SQL2k DTS Package

I have scheduled a DTS package (once a day) to open and import data from a fixed field file which is sent to our server via FTP from a data service bureau. The problem is that yesterday's file ends up remaining in the same location, while the new file being delivered by the service bureau via FTP does not over-write yesterday's file -- the file is added with a new reference...

Example:
Yesterday's file name "CLM_CLI.789" -- Today's file name "CLM_CLI.6890". Is there any way for SQL2k to delete the file after it is uploaded, or can anyone offer advice about how to use some other method to move the older file to another folder each day??

Thanks!
GlenHow does the DTS package know the name of today's file name?

I think you should be able to use xp_cmdshell to move the file to another directory.

Terri

Move DTS packages from SQL 2000 to SQL 2005

We have purchased a new Development SQL Server that is running SQL Server 2005. Our existing Development SQL Server is running SQL 2000.

How can I move the DTS packages from the SQL 2000 server to the new SQL 2005 server? I will be upgrading them to SSIS at some point, but don't have the time now.

thanks

sql server 2005 has a package migration wizard that might work for you.

Move DTS packages

Is there anyway to move the DTS packages to a new server. Can I backup and
restore the master database?
Any ideas?
ThanksGreg,
DTS packages are stored in MSDB not in master. The easist way to move a
small number of packages is to design the package, and click on the package
drop down menu, and click save as, and select the name of the new server.
--
Denny Cherry
DBA
GameSpy Industries
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:eM2Yy$iVDHA.1948@.TK2MSFTNGP11.phx.gbl...
> Is there anyway to move the DTS packages to a new server. Can I backup
and
> restore the master database?
> Any ideas?
> Thanks
>|||Greg,
Follow this link for good tips on moving DTS packages.
http://www.sqldts.com/default.aspx?6,105,204,0,1
Hope this helps
John

Move DTS package to another server

I'm building a DTS package on my development server which I want to
move to my test server. I can save the file and import it, but there
are connections etc that will be named differently.

Is there a quick and easy way of changing the names of connections ? I
also have some CSV files used for import/export (quicker than table
copy for my needs), but again I will need to change the path.

How can I do this without having to check each component ?

Thanks

Ryan"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0402130529.230f2596@.posting.google.c om...
> I'm building a DTS package on my development server which I want to
> move to my test server. I can save the file and import it, but there
> are connections etc that will be named differently.
> Is there a quick and easy way of changing the names of connections ? I
> also have some CSV files used for import/export (quicker than table
> copy for my needs), but again I will need to change the path.
> How can I do this without having to check each component ?
> Thanks
> Ryan

Have a look at some of these articles:

http://www.sqldts.com/default.aspx?101

One possible approach is to define global variables in the packages for the
connection properties. Then at the start of the package, set the connection
properties with a Dynamic Properties task. Since you can specify global
variable values on the command line using DTSRUN.EXE, this is also a handy
way to pass in the properties from a batch file.

Simon

Move DTS package from Server A to Server B

Hi

What would you say is the best method to move a DTS package from, say, a test environment to the production server please?

Thanks muchlySave it as a file instead of within msdb.|||DTS best practice,check this link.

http://vyaskn.tripod.com/sql_server_dts_best_practices.htm|||Thanks Blindman

Had just imported it as you posted - it is nearly the end of the Friday working day for me here - that's my excuse for not googling properly before posting :rolleyes:

You guys across the pond enjoy the rest of your Friday afternoon (working...) - I'm off to play the girly version of Football :D|||Check out DTSBackup at sqldts.com

Move DTS Package

Dear Sirs,
How can I move a DTS package from one server to another?
Regards,
YahyaHi,
Right click DTS which you want to move and chose Design Package. Click
Package then Save AS and chose server in which you want to save DTS.
Or you can save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
"Yahya Saad" wrote:
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>
>|||Try saving it as a .dts file rather than to SQL Server. Then move the file
and open it from the new server. You may strike some issues with ownership
and mappings if you use any flat files in the package, but they can be
worked around and re-mapped.
"Yahya Saad" <yahyasaad@.hotmail.com> wrote in message
news:uwR78bsCFHA.3528@.TK2MSFTNGP10.phx.gbl...
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>

Move DTS Package

Dear Sirs,
How can I move a DTS package from one server to another?
Regards,
Yahya
Hi,
Right click DTS which you want to move and chose Design Package. Click
Package then Save AS and chose server in which you want to save DTS.
Or you can save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
"Yahya Saad" wrote:

> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>
>
|||Try saving it as a .dts file rather than to SQL Server. Then move the file
and open it from the new server. You may strike some issues with ownership
and mappings if you use any flat files in the package, but they can be
worked around and re-mapped.
"Yahya Saad" <yahyasaad@.hotmail.com> wrote in message
news:uwR78bsCFHA.3528@.TK2MSFTNGP10.phx.gbl...
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>

Move DTS Package

Dear Sirs,
How can I move a DTS package from one server to another?
Regards,
YahyaHi,
Right click DTS which you want to move and chose Design Package. Click
Package then Save AS and chose server in which you want to save DTS.
Or you can save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
"Yahya Saad" wrote:

> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>
>|||Try saving it as a .dts file rather than to SQL Server. Then move the file
and open it from the new server. You may strike some issues with ownership
and mappings if you use any flat files in the package, but they can be
worked around and re-mapped.
"Yahya Saad" <yahyasaad@.hotmail.com> wrote in message
news:uwR78bsCFHA.3528@.TK2MSFTNGP10.phx.gbl...
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
DanHi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Another undocumented method:-
--
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> > We just bought a new server for our 23 DB. We are running SQL 2000.
> >
> > What is the best way to transfer our DB form our old server to our new
> one.
> > DTS?
> >
> > Thanks for any documentation that will help me acheive my goal.
> >
> > Dan
> >
> >
>

Moving instance of Sql server to different location

Is it possible to move an instance sql server from one drive to another, i.e
.
C: to D: without changing connection properties referenced in DTS packages,
etc.?
T.I.A.This should help:
http://support.microsoft.com/kb/224071
-oj
"LNN" <LNN@.discussions.microsoft.com> wrote in message
news:BE7ADDBD-6113-4BB2-9695-DEB61FC00A91@.microsoft.com...
> Is it possible to move an instance sql server from one drive to another,
> i.e.
> C: to D: without changing connection properties referenced in DTS
> packages,
> etc.?
> T.I.A.|||Hi
If you are moving a database then the connection properties will not change
if you following the instructions in the link posted by oj. If you are
rebuilding a new server and want to rename that server (to be the original
server's name) then you will also need
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
John
"LNN" wrote:

> Is it possible to move an instance sql server from one drive to another, i
.e.
> C: to D: without changing connection properties referenced in DTS packages
,
> etc.?
> T.I.A.