The destination is not affected

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)

The destination is not affected

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-02-01 : 09:59:48

I have this following activex script in my DTS package. When i run it, its saying:
" The Transformation is executed with a temporary file as destination. The destination you selected in the transformation is not affected."

The code I have is


'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()

If DTSDestination("OCN") <> DTSSource("OCN") then

	DTSDestination("OCN") = DTSSource("OCN")
	DTSDestination("OCN_Name") = DTSSource("OCN_Name")
	DTSDestination("OCN_Abbrev") = DTSSource("OCN_Abbrev")
	DTSDestination("OCN_State") = DTSSource("OCN_State")
	DTSDestination("Category") = DTSSource("Category")
	DTSDestination("Overall_OCN") = DTSSource("Overall_OCN")
	DTSDestination("StartDate") = DTSSource("Startdate")
	DTSDestination("EndDate") = DTSSource("Enddate")
	Main = DTSTransformStat_OK

Else If DTSDestination("OCN") = DTSSource("OCN") then

	If (DTSDestination("OCN_Name") <> DTSSource("OCN_Name") or DTSDestination("OCN_Abbrev") <> DTSSource("OCN_Abbrev") or DTSDestination("OCN_State") <> DTSSource("OCN_State") or DTSDestination("Category") <> DTSSource("Category") or DTSDestination("Overall_OCN") <> DTSSource("Overall_OCN") or DTSDestination("StartDate") <> DTSSource("Startdate") or DTSDestination("EndDate") <> DTSSource("Enddate")) then

		DTSDestination("enddate") = DTSSource("startdate")
		DTSDestination("startdate") = DTSSource("startdate")
		DTSDestination("OCN") = DTSSource("OCN")
		DTSDestination("OCN_Name") = DTSSource("OCN_Name")
		DTSDestination("OCN_Abbrev") = DTSSource("OCN_Abbrev")
		DTSDestination("OCN_State") = DTSSource("OCN_State")
		DTSDestination("Category") = DTSSource("Category")
		DTSDestination("Overall_OCN") = DTSSource("Overall_OCN")	
		Main = DTSTransformStat_OK

	End IF

Else
 	
	Main = DTSTransformStat_SkipRow

End If
End If
 	
End Function

Can somebody tell me what have i done wrong. Why isnt my destination affected by this script.

Appreciate your help,
Thanks a lot in advance,
Nitu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-01 : 12:46:15

I guess you didn't like my staging table approach mentioned in the other thread. It really is much simpler than what you are trying to do.

Tara Kizer
aka tduggan

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-02-01 : 15:29:26

I am sorry about your staging table approach, to be honest, as i told u iam a newbie, i couldnt understand properly what your approach was and how to do it.
If you could explain it more further, maybe with some example, that would be really great.

Thanks in advance,
Nitu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-01 : 15:33:19

First, create a table in your database that matches the format of the file. Import the data from the file into this table. Beyond that, we would need more information to help you with the T-SQL code. We would need to see the DDL (CREATE TABLE statements) for both tables and the business requirement of how to move the data into the actual table.

Tara Kizer
aka tduggan

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-02-01 : 16:34:45

I created a temp table to store the raw data that i am importing from a access table. These are the fields of the temp table:

OCN int
OCN_Name nvarchar
OCN_Abbrev nvarchar
OCN_State nvarchar
Category nvarchar
Overall_OCN int
Startdate text
EndDate text

They are inconsistance with the destination table, except for the startdate. The startdate in the temp table is a text since that the way it is in access table, and it is a smalldate time in the destination table.
But when i select the values of the temp table i do this,


SELECT OCN, OCN_Name, OCN_Abbrev, OCN_State, Category, Overall_OCN, 
CONVERT(datetime, CONVERT(varchar, Startdate, 101)) AS Startdate, 
CONVERT(datetime, CONVERT(varchar, Enddate, 101)) AS Endtdate
FROM source_tmp

After this the startdate is a datetime to store it in the destination.

Now the rules for insertion and updations are:
1. If the ocn(pimary key) of the temp table is not equal to the destination table. (meaning that the record in temp is a new rec) then insert the record set in the destination.
2. If the ocn of temp is equal to destination ocn then,
i. if any of the other fields of the temp is not equal to the respective field in the dest. table, then
a. update the destination table first, by changing the enddate as the startdate of the temp table.
b. insert this record to the dest table.
ii. if all the fields are same in both the tables then just skip that row.

So these are the steps i need to perform while transforming data from temp table to dest table.

I hope u can give me some sample.

Thanks a lot for your help,
--Nitu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-01 : 20:58:05

Sorry I haven't responded yet, been a long day working on production problems. We'll need the DDL for both the source and destination tables. You can generate this from Enterprise Manager via right click, all tasks, generate SQL script. We need the CREATE TABLE statement for both tables.

Tara Kizer
aka tduggan

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-02-02 : 10:30:27

here is the DDL of source and destination tables



CREATE TABLE [dbo].[Source_tmp] (
	[OCN] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OCN_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OCN_Abbrev] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OCN_State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Overall_OCN] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Startdate] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Enddate] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dest_tmp] (
	[OCN] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OCN_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OCN_Abbrev] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OCN_State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Category] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Overall_OCN] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartDate] [smalldatetime] NULL ,
	[EndDate] [smalldatetime] NULL 
) ON [PRIMARY]
GO

Thank you for all your help,
--Nitu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 13:48:57

1.
INSERT INTO Dest_Tmp
SELECT *
FROM Source_tmp
WHERE OCN NOT IN (SELECT OCN FROM Dest_tmp)

Could you provide data examples for 2? The data examples should all 3 conditions in 2. Please provide sample rows for these 3 conditions for both tables.

Tara Kizer
aka tduggan

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-02-02 : 14:39:45

Here is an example.



source_tmp							

ocn	ocn_name	ocn_abbrev	ocn_state	category	overall_ocn	startdate	enddate

1	sample1	sa1	va	sampcat1	1sa1va1	5/1/2005	null
2	sample2	sa2	va	sampcat2	2sa2va2	5/1/2005	null
3	sample3	sa3	va	sampcat3	3sa3va3	5/1/2005	null
4	sample4	sa4	va	sampcat4	4sa4va4	5/1/2005	null


dest_temp							


ocn	ocn_name	ocn_abbrev	ocn_state	category	overall_ocn	startdate	enddate
							
1	sample1	sa3	md	sampcat1	1sa1va1	4/1/2005	null
5	sample2	sa2	va	sampcat2	2sa2va2	2/1/2005	null
3	sample3	sa3	va	sampcat3	3sa3va3	5/1/2005	null
							

result dest							
							
							
ocn	ocn_name	ocn_abbrev	ocn_state	category	overall_ocn	startdate	enddate
							
1	sample1	sa1	va	sampcat1	1sa1va1	4/1/2005	5/1/2005
1	sample1	sa3	md	sampcat1	1sa1va1	4/1/2005	null
2	sample2	sa2	va	sampcat2	2sa2va2	2/1/2005	null
3	sample3	sa3	va	sampcat3	3sa3va3	3/1/2005	null
4	sample4	sa4	va	sampcat4	4sa4va4	4/1/2005	null
5	sample2	sa2	va	sampcat2	2sa2va2	2/1/2005	5/1/2005

Lets say that the dest_temp is the current table i have right now with me.
I get this new source_temp file.
Now i have to perform the following checks and then change the dest_temp as the result table.

checks:
1. the first record with ocn = 1 from the source is available in the dest already. But not all the fields are same, so first update the rec in dest_temp by puting the startdate of the source_temp in to enddate of dest_temp. after the updation, insert this newrec from source to dest.

2. second record doesnot exist in dest. this means this is new rec, so just insert it into dest.

3. 3 record is avaialble in the dest and all the fields are the same. so dont do anything just leave the rec as it is.

4. 4th record is also a new one to dest_temp, so its an insert.

5. if a rec is found in the dest and not found in the source, then the dest rect should be updated by changing the enddate to the startdate of the source_temp. the stardates in the source temp will be same in every record since its supplied every month.

so these are the actions to be performed.

while i was explaning this thing to you, I observed one thing. take the first condition, where the current rec is updated and a new rec is inserted. I dont think this can be done because, the ocn is a primary key, so we cannot have duplicates of the pk. so i think i need to keep the updated values in another table. so basically its all insert in the dest table. but do you have any suggestions as to how to take care of the updated record. I am thinking first update it, insert this in to another table, the delete it in the dest. Can you give me sample code for doing all three (update, insert in another table, and delete in current) together.

Really appreciate all your time and help,

Thanks a lot in advance,

--Nitu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources