activeX script help...beginner
-
Hi there, im using this vb script.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function
what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements) as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:
if record exists in destination
do update statement
if record not exist
do insert startement
many thanks in advance.
-
Personally I would not do the upsert (update/insert) in ActiveX because it is row by row processing and you may have performance problems.
A better solution, IMHO, would be to load the data into a separate table and then write a proc to do upsert as a set based process (just make sure you do the update before the insert or you will update the rows you have just inserted).
To find the existing rows you will need to query the existing table:
update <destination_table>
set SES_STATUS = SES_STATUS, ...
from <update_table>
where <update_table>.<primary_key> = <destination_table>.<primary_key>
and something similar for inserts but only for non matching rows.
J
-
thanks for that. I've abandoned the activeX and have decided to use an execute SQL task using this tsql:
INSERT INTO dbo.Target
SELECT *
FROM dbo.Source AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Target AS t
WHERE t.title_id = s.title_id
)
however my source and target tables are on different database, so when I choose a connection in the execute sql task I get an error as each database table exists different database, how could I over come this?
many thanks
-
If you use 3 level naming (database.owner.tablename) then it will not be a problem which connection you use. If the tables are on different servers or instances on the same server then you will have to use 4 level naming.
J
-
Thank you very much!!!!!!, btw, if I wanted to go with the active script, how would this code need to be modified to do the same thing as the execute sql task
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("SES_ID") = DTSSource("Session Code")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("SES_STATUS") = DTSSource("Session Status")
Main = DTSTransformstat_InsertQuery
End Function
-
I'm not exactly sure.
I think you can connect to an ODBC database within ActiveX which would allow you to execute queries (check exists, update or insert).
Alternatively, you could have three ExecuteSQL tasks in your pacakge (exists, update or insert) with parameters which you feed with data from the source:
1. Read row from source - assign to global parameters
2. Execute "check exists" task which takes the global parameters and assigns the result (exists or does not exist) to a global parameter
3. If exists, execute "update" task which takes in the global parameters for the query
4. Else execute "insert" tasks - again it takes in the global parameters for the query
5. Fetch next
I'm pretty sure this could be done but it's not very pretty.
J
-
Thanks again for you help. Im testing the sql for the execute SQL task, and im getting this error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider 'TRANSROM' does not contain table '"HYPE_LIVE"."dbo"."LIVE (Hyperion) Financials$Session Header"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='TRANSROM', TableName='"HYPE_LIVE"."dbo"."LIVE (Hyperion) Financials$Session Header"'].
This is what I'm trying:
1st:
use master
EXEC sp_addlinkedserver
@server = 'TRANSROM',
@provider = 'SQLOLEDB.1',
@srvproduct = '',
@provstr = 'Privider=SQLOLEDB.1;Data Source=TRANSROM;Initial Catalog=HYPE_LIVE'
/* add LOGIN details */
Exec sp_addlinkedsrvlogin
@rmtsrvname = 'TRANSROM',
@useself = true,
@locallogin = null,
@rmtuser = 'sa',
@rmtpassword = '*************'
2nd
INSERT INTO websiteDB.dbo.SESSIONS
SELECT [Session Code], [Subject Code], [Start Date], [Subject Type], [Stream], [Sub Location],
[Tutor Code], [Year/Sitting], [Session Status]
FROM TRANSROM.HYPE_LIVE.dbo.[LIVE (Hyperion) Financials$Session Header] AS S
WHERE NOT EXISTS
(
SELECT 1
FROM websiteDB.dbo.SESSIONS AS T
WHERE T.SES_ID = S.[Session Code]
)
TRANSROM = the remote server
HYPE_LIVE = the database
[LIVE (Hyperion) Financials$Session Header] = the source table.
Thanks
-
I guess that if you connect to the TRANSROM server through Query Analyser you have no problems accessing the source table?
If so, then I can only imagine that there is a problem with your connection or login - there is a typo in @provstr where you have Privider rather than Provider but not sure whether that is a true typo or a pasting into the forum error.
Have you tried making the connection using Enterprise Manager rather than using sp_Addlinkedserver/login?
J
-
Thanks again for your reply, It was a typo in the forum post. I've made a connection between the 2 databases using connection tools in the DTS package, but I get the exact same error message when trying to parse the query in the execute sql task properties.
-
The SQL Statement you execute in DTS won't know about the connections defined within the package.
Best way to do this would be to have a Datapump task transfer the data as-is from the HYP_LIVE database into the websiteDB database. Then run your basic insert/update statments as required all within the same database.
If you want to use your previously posted SQL statement,
"INSERT INTO websiteDB.dbo.SESSIONS
SELECT [Session Code], [Subject Code], [Start Date], [Subject Type], [Stream], [Sub Location],
[Tutor Code], [Year/Sitting], [Session Status]
FROM TRANSROM.HYPE_LIVE.dbo.[LIVE (Hyperion) Financials$Session Header] AS S
WHERE NOT EXISTS
(
SELECT 1
FROM websiteDB.dbo.SESSIONS AS T
WHERE T.SES_ID = S.[Session Code]
)"
you don't really need to involve DTS at all.
Connect to the server running websitDB using Query Analyzer and execute a simple select on the TRANSROM.HYP_LIVE database. This will allow you to resolve any connection issues without inserting/updating any data. Once you've got the connection issues sorted you can run your insert/update statements in a stored procedure or scheduled job...
--------------------
Colt 45 - the original point and click interface
-
For some reason I cant get the execute sql task to work, so I'm going back to the data driven query. Im using this activeX vbscript to either update or insert depending on if the record already exists in the destination table, it inserts OK, but never updates. Can somebody please help me to see where Im going wrong...many thanks
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
Dim sSessCode
sSessCode = DTSDestination("SES_ID")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
If DTSSource("Start Date") = "01/01/1753" Then
DTSDestination("SES_DATE") = "01/01/1980"
Else
DTSDestination("SES_DATE") = Cdate(DTSSource("Start Date"))
End If
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("SES_STATUS") = DTSSource("Session Status")
Select Case sSessCode
Case DTSSource("Session Code")
' MsgBox("Update")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_UpdateQuery
Case Else
' MsgBox("Insert")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_InsertQuery
End Select
End Function
-
For the data driven query, have you specified both the insert and update statements?
Is there a specific reason why you can't pump the data to the source and the run seperate insert/update statements?
--------------------
Colt 45 - the original point and click interface -
I orginally tried using a execute sql task to perform the insert/update, but the only databases that I can select from the linked server is the master database, but the database that I want to work with in the linked server is a table called 'HYPE_LIVE'
/* add NAVISIONSQL SERVER */
use master
EXEC sp_addlinkedserver
@server = 'NAVISIONSQL',
@provider = 'SQLOLEDB.1',
@srvproduct = '',
@provstr = 'Provider=SQLOLEDB.1;Data Source=NAVISIONSQL;Initial Catalog=HYPE_LIVE'
/* add LOGIN details */
Exec sp_addlinkedsrvlogin
@rmtsrvname = 'NAVISIONSQL',
@useself = true,
@locallogin = null,
@rmtuser = 'sa',
@rmtpassword = '***********'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply