sql server dts: Insert problem -- sql server dts

Re: Insert problem

Allan Mitchell
11/17/2005 9:39:42 PM

sql server dts:

Yep you can certainly do that.

Have you thought about linked servers?

Allan

[quoted text, click to view]

Re: Insert problem

Best Practice
11/18/2005 12:00:00 AM

The databases are all on the same server.

When i run the package it bombs out.

I'm i missing something simple????

Please any feed back would be appreciated.

April

[quoted text, click to view]

Re: Insert problem

Best Practice
11/18/2005 12:00:00 AM

I'm having a syntax error day here...any suggestions.

' Copy each source column to the destination column
Function Main()
DTSDestination("wexchid") = DTSSource("exchid")
DTSDestination("wexTitle") = DTSSource("exTitle")
DTSDestination("wexSku") = DTSSource("exSku")
DTSDestination("wexDescription") = DTSSource("exDescription")
DTSDestination("wexPrice") = DTSSource("exPrice")
DTSDestination("wexPrice2") = DTSSource("exPrice2")

If (DTSLookups("skuCHECK").Execute(DTSSource("exSku"))) Then
Main = DTSTransformStat_InsertQuery
End If

End Function

The lookup is set to check the destination against the source.

I guess this is where my problems lies.

If "what goes in here"
(DTSLookups("skuCHECK").Execute(DTSSource("exSku"))) Then

I'm stuck any help would be appreciated.

April

Re: Insert problem

Allan Mitchell
11/18/2005 12:00:00 AM

If the databases are on the same server then do not use lookups.

How about you simply say

INSERT TABLE(COLUMNS)
SELECT (COLUMNS)
FROM DATABASE.OWNER.TABLE DB1 LEFT OUTER JOIN dbo.TABLE T1
ON DB1.(Id Columns) =T1.id Column)
WHERE T1.(Is Column IS NULL)

Obviously you will need to play with the syntax for your environment.
This then would site in an ExecuteSQL Task

If you did this

SELECT (COLUMNS)
FROM DATABASE.OWNER.TABLE DB1 LEFT OUTER JOIN dbo.TABLE T1
ON DB1.(Id Columns) =T1.id Column)
WHERE T1.(Is Column IS NULL)

You could use a DataPump task

You say "Bombs Out". With what error?

[quoted text, click to view]

Insert problem

Best Practice
11/18/2005 12:00:00 AM

Hello Everyone.

I basically want to do an insert.

Here's my insert script which works when the database is empty.
In the destination table i created another index (siteid).

' Copy each source column to the destination column
Function Main()
DTSDestination("wexchid") = DTSSource("exchid")
DTSDestination("wexTitle") = DTSSource("exTitle")
DTSDestination("wexSku") = DTSSource("exSku")
DTSDestination("wexDescription") = DTSSource("exDescription")
DTSDestination("wexPrice") = DTSSource("exPrice")
DTSDestination("wexPrice2") = DTSSource("exPrice2")
Main = DTSTransformStat_InsertQuery
End Function

I don't know how to check if the DTSDestination("wexSku") exist.
If it does exist then skip that row during an insert.

If i was writing this in normal asp then it won't be an issue but i'm
struggling here.

Would it be just to write someting like this

If (DTSLookups("skuCHECK").Execute(DTSSource("wexSku"))) Then
Main = DTSTransformStat_InsertQuery
End If

My lookup checks the destination table and then matches it with the source.

I need some help here.

Thank you

April

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.