DTS Packages from 1 SQL Server to another

Hi,

I am tring to transfer a number of DTS Packages from one SQL Server to another. Does anyone have any ideas.

I can save them as either DTS Strucure or Visual Basic Files, but how can I load them again.

After that I am going to rename the servers, I am going to rename the old one with _BAK and I am going to name the new one with the current servers name. Might this cause problems??

Rayread the following article

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp|||Thanks Much, I will check that out and let you know.

Ray

Quote:

Originally Posted by aneeshattingal

read the following article

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp

DTS Legacy and SQL 2005

I am in the process of migrating to a new SQL 2005 server. I have a number of DTS packages on my SQL2000 server, approximately 200, that are used on a daily basis. I used the migration wizard to migrate the packages from the 2000 server to the new 2005 server however there are issues with the way some were brought over. I would like to have all of the packages moved from the 2000 to the 2005 server and appear under Legacy DTS so that I can run them as 2000 DTS packages unitl I have a chance to correct the issues.

Here is where my question lies. The migration wizard migrates upgrades all of the packages. How do I move them from the one server to the other and perserve their 2000 DTS format? The servers are on 2 separate boxes with different instance names. Everything I've read tells you how to run the legacy packages but nothing seems to explain on to move them.

Any help would be appreciated

Pete

As you have found the migration wizard attempts to create new SSIS 2005 packages from your DTS packages. Due to the fundamentally different architecture this can be limited in its success.

Moving your DTS 2000 packages to a new server, should be effectively the same as moving DTS packages between 2000 servers, so the following information may still be valid: http://www.sqldts.com/default.aspx?204

However, note that I have not tested this scenario - it is only a suggestion and would need some testing and experimentation.

Donald

|||

Maybe not very polite, but what the heck! why you want migrate them to Sql25k instead of make them from the beginning taking advantatge of all that native stuff?

We've got around 500 and most of them on-daily basis. We have come to the conclusion that left the old ones inside a rubbish.

|||

Although, of course we're talking about bank sector...Thing going here slower

I suppose that if you have mainly DTS packages with the usual tasks (pumping, sql tasks, vbScript and nothing else) migration could be successful from Sql25k otherwise if you have DTS with custom tasks it might not easier.

DTS Import txt File looping.

Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>