Script a DTS package - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Script a DTS package

Author

Topic

CanadaDBA

583 Posts

Posted - 2008-06-27 : 14:26:06

I have 53 databases and they are same as each other with different data. There are two tables with static data in each database. It happened that I lost the data in these two tables for all 53 databases. I want to restore the data from backup. I have restored all 53 dbs into a Temp server.

I want to copy the two tables from Temp server into Test server (from all 53 databases into their corresponding databases on dest server). Both servers are SQL2000.

I can do one db from source into another db on destination using DTS package. Then I have to repeate creating other 52 DTS packages. Then question is:

1. How that is possible to script a DTS package?
2. Is there an easier way?

Thanks,

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-27 : 14:30:17

Why are you even using DTS for this? This would be fairly easy with just T-SQL.

I believe you can script them out if you save them as a vbs file, but the problem is that you can't re-import them, instead you have to run the vbs file in a vb program.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 14:36:15

Are those two tables structure and data same for all 53 databases?

CanadaDBA

583 Posts

Posted - 2008-06-27 : 14:51:14

Yes, they are.

quote:
Originally posted by sodeep

Are those two tables structure and data same for all 53 databases?

Canada DBA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 15:00:44

Create SSIS package with Multicast task to send those datas to tables in multiple DBS.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 15:01:56

Sorry,Ignore that as you are on SQL 2000.

CanadaDBA

583 Posts

Posted - 2008-06-27 : 15:13:17

If I want to go based on Tara's suggestion, my query should be like this:


Truncate Table [ServerB].[DB1].[dbo].[T01]
GO
INSERT INTO [ServerB].[DB1].[dbo].[T01]
SELECT [F1], [F2], [F3] FROM [ServerA].[DB1].[dbo].[T01]

Well, I know [ServerB].[DB1].[dbo].[T01] is incorrect.. my mind has frozen this afternoon!!

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-27 : 15:24:34

Four-part naming convention:
LinkedServerName.DatabaseName.ObjectOwner.ObjectName

But haven't you already transferred the object and the data to one of the databases on your temp server? Can't you just use that as your soure? Then you can easily loop through them by adding dynamic sql for the insert/select where I've got the call to isp_ALTER_INDEX: http://weblogs.sqlteam.com/tarad/archive/2007/11/28/60417.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CanadaDBA

583 Posts

Posted - 2008-06-27 : 15:59:18

Thanks for the naming convention. I am not sure the table in each database has the same value. Therefore, I have to copy the table from each database into its corresponding db and table.

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-27 : 16:02:36

What do you mean by "same value"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-06-27 : 16:29:35

To save the DTS package to another server, do the following:
1. Open DTS package
2. Go to Package, Save As
3. Enter the Server Name you want it saved under
4. Enter the username + password
5. Click OK

This should do it

Alternatively, if you want to script it out, then do steps 1, 2, and then under Location, Choose either Structured Storage File or Visual Basic file, and save it where you need it.

CanadaDBA

583 Posts

Posted - 2008-07-03 : 10:09:21

quote:
Originally posted by tkizer

What do you mean by "same value"?

These 2 tables in each database has different data. So I need to transfer each table into its corresponding table for each DB.

I want to do the following:


EXEC sp_addlinkedserver 'SERVERA', N'SQL Server'
GO
Truncate Table [ServerB].[DB1].[dbo].[T01]
GO
INSERT INTO [ServerB].[DB1].[dbo].[T01]
SELECT [F1], [F2], [F3] FROM [ServerA].[DB1].[dbo].[T01]

But thought it's better to do this test first:


EXEC sp_addlinkedserver 'SERVERA', N'SQL Server'
GO
SELECT * FROM [ServerA].[DB1].[dbo].[T01]

And I got the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.

SA password is different in ServerA and ServerB. What should I do?

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-03 : 10:30:44

You can setup the linked server to use your other sa password.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CanadaDBA

583 Posts

Posted - 2008-07-03 : 10:30:58

OK. This worked. I mean the linked server part. But the insert got an error.


TRUNCATE TABLE [DB02].[dbo].[T1]
Go
SET IDENTITY_INSERT [DB02].[dbo].[T1] ON

INSERT INTO [DB02].[dbo].[T1]
	SELECT [F1],[F2],[F3] 
	FROM [SERVERA].[DB01].[dbo].[T1]

SET IDENTITY_INSERT [DB02].[dbo].[T1] OFF

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'F02.dbo.GL00200' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Canada DBA

CanadaDBA

583 Posts

Posted - 2008-07-03 : 10:33:22

quote:
Originally posted by tkizer

You can setup the linked server to use your other sa password.

Do you mean change the linked server SA password? I did this for my test. So, is it true to say the linked server sa password has to be same as the server you are working on?

Canada DBA

CanadaDBA

583 Posts

Posted - 2008-07-03 : 10:48:58

Found my mistake:


INSERT INTO [DB02].[dbo].[T1] ([F1],[F2],[F3])
	SELECT [F1],[F2],[F3] 
	FROM [SERVERA].[DB01].[dbo].[T1]

Works fine. Thanks!

Canada DBA

CanadaDBA

583 Posts

Posted - 2008-07-03 : 12:18:21

Does the linked server stays until I close my connections; i.e. AQ or Management studio? Is there any command that disables the following?

EXEC sp_addlinkedserver 'SERVERA', N'SQL Server'

Canada DBA

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-03 : 23:32:50

You can drop linked server in em or with sp_dropserver.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources