Hi Guys,
Is there any Wizard or software or tools that I can use to create Triggers for my tables ... !??
It is for MSSQL 2005 database
Thanks for your help,
Mehdi
In Managenent studio try View->template explorer and next select Trigger from templates and select trigger you wont.
Hi,
I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.
So query in OLE DB Source:
Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between ‘1/1/2006’ and ‘1/31/2006’
OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.
My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.
Thanks,
That is a verry common scenario when loading data. The most popular solution in this forum is tu used a Lookup transform in the data flow against the destination table; no matches in LU transform are treated as errors; so you can configure the error output of the LU transform to 'redirect error'; then the error output is your 'new rows' out put and the no-error output is the 'existing rows' output.:
Somewhere in this thread there is a link to Jamie's blog where that technique is explanied (an other options discussed).
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=779836&SiteID=17
Be sure to understand how Lookup transform uses memory resources as that could play against you if the volume of data is to big or if memory not enough
|||I second Rafael's comments.
Hi,
I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.
So query in OLE DB Source:
Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between '1/1/2006' and '1/31/2006'
OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.
My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.
Thanks,
I can't find a simple way to do this in SSIS. I'd better do this in T-SQL, usingLinked Servers. Suppose you've set a linked server (namedSourceServer) for the source server on destination server, you can use such query to accomplish INSERT/UPDATE:UPDATE TableDest
SET [Desc]=src.[Desc], [CreateDate]=src.[CreateDate]
FROM TableDest dest JOIN [SourceServer].SourceDB..TableSrc src
ON dest.ID=src.ID
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'
INSERT INTO TableDest
SELECT * FROM [SourceServer].SourceDB.TableSrc src
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'
AND src.ID NOT IN (SELECT ID FROM TableDest)
