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?
>
>

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 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 data from SQL 2005 to DB2/as400 using DB2OLEDB

I have a simple SSIS package that I want to move data from SQL 2005 to DB2/AS400. In order to use OLEDDB destination, I installed HIS2004 that provides DB2OLEDB support.

For simplicity and testing purpose, I created a connection pointing to a SQL table that contains only one ccolumn "last_name" of type "nvarchar(50)". This is my source, I then created another connection using DB2OLEDB pointing to a table on DB2 that contains only one field "name" of type "GRAPHIC(50)". This is my destination and the connection works fine to DB2 database. When I run the package, it has type conversion error as follows:

[OLE DB Destination [34]] Error: An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E07 Description: "Data or literal value could not be converted to the type of the column in the data source, and the provider was unable to determine which columns could not be converted. Data overflow or sign mismatch was not the cause.".

If I manually assign a vaule to the "last_name" using the "Derived Column" task like "Hello John", it just works fine.

Anyone knows what went wrong? Did I miss anything here? Thanks.

As a FYI: DTS works great in moving data from SQL 2000/2005 to DB2.

Your help will be appreciated.

There are quite some conceptual differences between SQLServer's nvarchar/nchar and DB2's vargraphic/graphic, for example, nvarchar/nchar are unicode columns, graphic/vargraphic however are not, unless you bind them with a unicode CCSID.

I was able to accomplish similar scenarios using DB2OLEDB provider by following the steps below:

1) Set DB2 connection's "HOST CCSID " property to "Unicode - UTF(8) [1208]"

2) At DB2 destination column, for example, for storing data from your nvarchar(50) input column, set the column type to Graphic/Vargraphic(100) CCSID 13488.

HTH

Wenyang