Newbie: Use DTS to update a column

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

Import/Export (DTS) and Replication (2000)

Newbie: Use DTS to update a column

Author

Topic

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 05:41:00

Dear All

Every month I execute
UPDATE T
SET C = NULL

I am new to DTS and this looks like a way to learn something about DTS. Every DTS package I wrote fails however. Usually they complain about me inserting duplicate keys into a PK column. I know where that comes from, I just don't know how to correct it. I allready read BOL and now I'm reading SqlDts.com. Can someone find the time give me a step by step (hold me by the hand) instruction. Thanks.

I want to use this package on three servers (Dev, Test and Prod). HoweverI don't want to maintain three packages. I was thinking of creating on package and run it at three server (with DTSrun). Maybe this should not be a DTS, but something like an osql that is scheduled by the operating system.

Kind regards,
SqlSearcher

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 06:28:42

Did you get any error?
How about having a sp that does the updation and schedule it as job to run each month?

Madhivanan

Failing to plan is Planning to fail

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 06:50:56

Hello Madhivanan

Here's what I did:
Pick two Sql Server Connection, pointing to the same database. I call them 1 and 2. I select 1, click Transform Data Task (TDT) and select 2. In the properties of TDT I select table T, both as source and as destination. At transformation I Delete All, and add a New one (using Active X) with column C as source and target. When I execute the saved package I get: Cannot insert the value NULL into column PK, column does not allow nulls. INSERT fails. This is correct, PK is a primaira key column. So yes, I know what went wrong, I just don't know how to fix it. I hope this will be helpfull.

Kind regards,
SqlSearcher

PS I used oSql and scheduled tasks to solve it. But I still want to know how to do it in DTS. But maybe you're right in telling me to use a SP.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 07:54:28

You cant update Primary key column to have NULL value
Post table structures of tables

Madhivanan

Failing to plan is Planning to fail

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 08:09:05

Dear Madhivanan

I don't want to update a PK! I have a table with two columns, one of them is a PK. It's the other one I want to update. When I try to do it using DTS, it complains I want to update a PK. But I don't want to update this PK. All I want is a simple update of a simple column that is NO Primairy Key.

Kind regards,
SqlSearcher

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 08:11:33

Cant you use simple update query?
Why do you use DTS?

Madhivanan

Failing to plan is Planning to fail

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 08:33:29

Dear Madhivanan

I know how to write the query. But it is still manual. When I am on leave the query has to run without me. Nobody else will do it. Now I heard those great stories about DTS, so I thought, let's write a DTS to do this simple task. This way I learn about DTS and the query will run without me.

Kind regards,
SqlSearcher

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 08:51:45

As I told you earlier, create stored procedure that does the updation and schedule it in the job to run every month. Refer BOL for more details

Madhivanan

Failing to plan is Planning to fail

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 09:46:00

Dear Madhivanan

The purpose was twofold: first automate a query, second learn DTS.
I solved the first purpose with osql (please read one of my previous replies). The second purpose still stands; I still want to learn DTS. Using SP won't help me learning DTS.

Kind regards,
SqlSearcher

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 09:51:50

Refer
www.sqldts.com

Madhivanan

Failing to plan is Planning to fail

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-09 : 09:57:44

Dear Madhivanan

I visited SqlDts.com before I posted this question (as you can read in the first posting). But I could not get a good tutorial or example. Allmost all examples and tutorial are about transforming data from one table to another table. With me source and destination are the same, hence the problem with the PK.

I might sound a bit snappy, but I realy do apreciate your help. So please keep giving hints and tips. I liked the one about ConnectionStrings.com and SqlDts.com is also a good one. Do you have more good URLs?

Kind regards,
SqlSearcher

SQLsearcher
Starting Member

47 Posts

Posted - 2006-01-10 : 07:43:05

Solved!

Don't use two Sql Server Connections, use only one. And use the Execute SQL Task.

Kind regards, SqlSearcher

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-10 : 07:48:59

Well. But to update table you can simply use query or sp than DTS

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources