Updating DTS packages

[whitebelt]

Hi,

I am new to sql server 2000, I really need your help out there to guide me on how to:

I have two servers, server A and server B, I moved all my dts packages from server A to server B successfully. When I make changes to packages on server A, I want them to be insynched "automatically" to server B, is there anyway to make it happens?

Your help is much appreciated!

-Whitebelt

[rmiao]

No you can't, save it to server b again after making change on server a.

[Loren]

I don't think that's exactly correct.
if you used structured storage files instead of storing your DTS packages natively within SQL server then you could have more than one server reach these common packages on a common drive share.

[rmiao]

That's true. But in this case, packages are stored in sql server.

[whitebelt]

Thanks for your quick response Loren! My next question would be: do I save the updated package to the same name or to a different name on the server B?

Thank you for reponding to my question!

-Whitebelt

[rmiao]

If you save the package to file as Loren said, it should have only one name and every server calls it with that name.

[whitebelt]

Thank you!

[whitebelt]

Hi,

Can someone show step by step how to load an excel spreadsheet as a source file (server A) into a table in the destination of the server B please?

Thank you so much in advance!

-whitebelt

[rmiao]

Right click the db in em, choose all tasks -> import data, select Microsoft Excel xxx as data source and specify file path\name, select sql server as destination, choose table you like to load data into, map columns in transformation page, ready to go.

[whitebelt]

Great, thank you so much for your prompt response!

-whitebelt

[whitebelt]

Hi there,

I have a question regarding the DTS package which I am not really familiar with so I don't really know what to do...

Here's the question I have:

1. Prepare a DTS package that loads data using a data driven query into the ‘tableA’ table using data from the ‘tableB’ table in the 'Testing' database on the test server.
2. The ‘tableB’ table has data from all geographies in it and it must be transferred to the tableA’ table.
3. The tableA’ table already has data in it including some of the data that is in the 'TableB’ table therefore your Data Driven Query must have the ability to both update/ insert records.
4. Please implement the appropriate DTS tasks so that the DTS package will be logged in the 'PackageLog' and 'ActivityLog' tables similar to what all of the other DTS jobs do. You may be creative with assigning values for the following global variables when setting up your package:
1. 'PackageName'
2. 'PackageLabel'
3. 'ActivityName'

Solution:

What I did was I create:
1. Create an Executive Package Task with a package name ‘Insert Package Log’ with two outer package global variables (PackageName, PackageLabel)
2. Then I created the Dynamic Properties Task properties with an ActivityName
3. Then another Package Task with a package name ‘Activity Log Insert’ with four outer package global variables (PackageName, PackageLabel, ActivityName, ActivityLabel)
4. Then I created Data Driven Query Task where on the:

Source tab: I have the connection is sql server, table/view is ‘tableB’
Bindings tab: I have the connection is sql server, table/view is ‘tableA’
Transformation tab: ‘Select All’, then click on ‘New’, and of course the source Columns and the Binding Columns are the same
Queries tab: I clicked on ‘Build’ button to build the script, here is what I have:
Insert into ‘tableA’ (all the columns in this table)
Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
And then I have two database connections which are in the package but they are not connected with anything else. Both of them connected to the 'Testing' database using window authentication with an existing connection (sql server)

Didn’t work anyhow….when I run the package, there are errors on the ‘insert Package Log’, ‘Insert Activity Log; and ‘Data Driven Query Task’.

Please help me and let me know where i need to correct this so that this package would run probably.

Thank you for your help!

-whitebelt

[rmiao]

Is it for home work?

[whitebelt]

It's kind of, it was a test !