March 14, 2008 at 5:37 pm
I am using SQL 2005.
I have a 2 tables. A source and a destination table.
The source table is refreshed every 30 seconds with data that contains (updates to records and new records) from a flat file using SSIS.
I want to update/insert to the destination table from the source table. The source table will have approx 10,000 records.
What is the best way to accomplish this? Or should I say most efficient way?
Thanks
Teekay
March 14, 2008 at 7:08 pm
Like anything else in SQL, there are a score of ways to do this. :hehe:
If you are enforcing uniqueness on the target table with a primary key or unique constraint, you can use the error output of your data destination adapter. Run the insert on your destination adapter, and the error rows (those that already exist and therefore violate the constraint) will be redirected to the error output, at the end of which you can drop in your UPDATE logic.
You could try a lookup component instead, but you'll touch the target in three different components instead of just two, which will likely increase performance overhead (assuming that is a consideration).
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
March 17, 2008 at 5:26 am
Hi ,
Here you can intoduce one more table that will contain the flagging like "DP" (Duplicate In Production Table) or "GR" (Good Records that not exists in Production) and after that move the records accordingly..
After completion of Job truncate the flaggging table...
It is the most fastest method , as i believe......
Thanks ,
Amit Gupta
MVP
May 20, 2008 at 1:51 am
hi amit this is parwinder, could you please elaborate your method to upsert into dest table.
June 3, 2008 at 7:29 pm
Another way to do an upsert is using the Slowly Changing Dimension data flow transformation. Just connect it to the data source and setup as a Type 1 SCD (updates existing and inserts new records).
May need to be aware of performance on larger datasets.
Cheers,
Matt
MattF
June 4, 2008 at 1:02 am
Any issues with using a trigger?
~PD
June 4, 2008 at 1:05 am
November 25, 2008 at 11:15 am
Thanks!
I was going out of my mind trying to figure out how to do an update as part of a data flow. This was exactly what I needed to get it done on a small data set.
February 4, 2009 at 11:50 am
terific solution....Thanks...
It reduced lot of effort....thanks...
May 18, 2009 at 3:20 pm
Thanks a bunch!
March 17, 2010 at 5:43 pm
I'm sorry I am new to SSIS. I am trying to convert a SQL script into an SSIS packae. My problem appears to be a little more complex than what you posted (or I'm just not getting it yet). I was given a query whose results I want to periodically import into my own DB. In this query the admin has a data source that joins several tables and does an initial extract to a temporary table then using a key field from the temp table creates a cursor which joins several tables to look up some information. Finally there is a select from the temp table to presents the results.
I can get the source query to work for everything up to the cursor section. I can get all the initial data but I don't know how to go about using the result set in a subsequent look up to update the manager and support group fields.
I am inserting the SQL code below to help make this more clear. The two fields that get updated are for the Manager and the Support Group. The script only looks up the Manager for now and i will be adding the Support group lookup after I get this working.
SET NOCOUNT ON;
DECLARE
@machine_id varchar(32)
,@txt varchar(255)
DECLARE @mtm TABLE(
[rec_Id] int IDENTITY(1,1) NOT NULL
,[machine_id] nvarchar(100)
,[OS] nvarchar(50)
,[server_name] nvarchar(130)
,[product_name] nvarchar(30)
,[production_ind] bit
,[manager] nvarchar(255)
,[pdba] nvarchar(62)
,[on_call_group_id] nchar(5)
,[application_domain] nvarchar(50)
,[instance_domain] nvarchar(50)
,[support_domain] nvarchar(255)
)
INSERT INTO @mtm(
[machine_id]
,[OS]
,[server_name]
,[product_name]
,[production_ind]
,[manager]
,[pdba]
,[on_call_group_id]
,[application_domain]
,[instance_domain]
,[support_domain]
)
SELECT DISTINCT
m.[machine_id] AS machine_id
,m.[os_nm] AS OS
,vi.[instance_nm] AS server_name
,vi.[product_nm] AS product_name
,dis.[production_in] AS production_ind
,replicate(' ', 255) AS manager
,rtrim(p.[last_nm]) + ', ' + rtrim(p.[first_nm]) AS pdba
,vda.[on_call_group_id]
,a.[domain] AS application_domain
,vi.[domain] AS instance_domain
,replicate(' ', 255) AS support_domain
FROM
machine m
JOIN v_instance vi ON m.machine_id = vi.machine_id
LEFT JOIN v_database_app vda ON vi.machine_id = vda.machine_id
AND vi.instance_nm = vda.instance_nm
AND vda.Ainactive_in = 0
AND vda.Iinactive_in = 0
AND vda.Dinactive_in = 0
AND vda.product_nm IN ('Oracle', 'Sybase', 'DB2', 'Microsoft SQL Server')
LEFT JOIN db_instance_stage dis ON vda.db_instance_stage_nm = dis.db_instance_stage_nm
LEFT JOIN application_person_role apr ON vda.app_id = apr.app_id
AND apr.app_role_id = 'PDBA'
LEFT JOIN person p ON apr.person_id = p.person_id
LEFT JOIN src_ait src ON convert(int, vda.ait_nmbr) = src.appidasint
LEFT JOIN application a ON vda.app_id = a.app_id
WHERE
vi.[product_nm] IN ('Oracle', 'Sybase', 'DB2', 'Microsoft SQL Server')
ORDER BY
m.[machine_id];
DECLARE mch cursor for
SELECT DISTINCT
machine_id
FROM@mtm
FORREAD ONLY;
OPEN mch;
FETCH mch INTO @machine_id;
WHILE(@@fetch_status = 0)
BEGIN
SELECT @txt = null;
SELECT DISTINCT
@txt = coalesce(@txt + ', ', '') + CAST(p.last_nm as varchar(20)) + ' (' + CAST(p.first_nm as varchar(20)) + ')'
FROM
person p
JOIN mgt_rpt_group mrg ON p.person_id = mrg.manager_id
JOIN person_mgt_rpt_group pmrg ON mrg.mgt_rpt_group_id = pmrg.mgt_rpt_group_id
JOIN application_person_role apr ON pmrg.person_id = apr.person_id
JOIN v_app_database vad ON apr.app_id = vad.app_id
WHERE
apr.app_role_id = 'PDBA'
AND rtrim(vad.machine_id) = @machine_id
AND p.last_nm <> 'Holtsinger'
IF(@txt is null)
BEGIN
SELECT DISTINCT
@txt = coalesce(@txt + ', ', '') + CAST(p.last_nm as varchar(20)) + ' (' + CAST(p.first_nm as varchar(20)) + ')'
FROM
person p
JOIN on_call_group ocg ON p.person_id = ocg.resp_mgr_person_id
JOIN person_on_call_group pocg ON ocg.on_call_group_id = pocg.on_call_group_id
JOIN application_person_role apr ON pocg.person_id = apr.person_id
JOIN v_app_database vad ON apr.app_id = vad.app_id
WHERE
apr.app_role_id = 'PDBA'
AND rtrim(vad.machine_id) = @machine_id
END
IF(@txt <> '')
BEGIN
UPDATE@mtm
SETmanager = @txt
WHEREmachine_id = @machine_id
END
FETCH mch INTO @machine_id;
END;
CLOSE mch;
DEALLOCATE mch;
SELECT DISTINCT
rtrim(isNull([machine_id], ''))AS 'Machine_ID'
,rtrim(isNull([server_name], ''))AS 'Instance_Name'
,rtrim(isNull([OS], ''))AS 'OS'
,rtrim(isNull([product_name], ''))AS 'RDBMS'
,rtrim(isNull([pdba], ''))AS 'Primary_DBA'
,rtrim(isNull([manager], ''))AS 'Manager(s)'
,CASE
WHEN production_ind = 1 THEN 'PROD'
ELSE 'NON-PROD'
ENDAS 'Production (Y/N)'
,rtrim(isNull(on_call_group_id, '')) AS 'OnCall_Group_ID'
,rtrim(isNull(application_domain, '')) AS 'Application_Domain'
,rtrim(isNull(instance_domain, '')) AS 'Instance_Domain'
,rtrim(isNull(support_domain, ''))AS 'Support_Domain'
FROM
@mtm;
March 18, 2010 at 8:18 pm
Hi,
The SCD method requires a "business key" to match the records in the source and destination - as long as they have this then you should be able to use an SCD tutorial to set it up.
MattF
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply