April 11, 2016 at 3:28 pm
I am pulling data from an application database. Our vendor is not the most responsive and the do not fulfill our requests very fast.
I want to hard code a Query to fill a table on our Local Database from the Vendors to add 2 rows with specific data.
I also want to put in some type of error handling IF the vendor updates the table without telling us.
Here is my Query:
SELECT [TransactionTypeId]
,[Code]
,[Description]
,[IsShown]
,[IsActive]
,[SortOrder]
FROM [dbo].[TransactionTypes] Union
SELECT 14,'WO','Write-Off', 1, 1,14 UNION
SELECT 15,'WO','Write-Off Reversal', 1, 1,15
What transform do I use in SSIS to Check the source database and if it has a 14 or 15 for TransactionTypeId already to either skip this step and go to another one (which i would have the Query without additional hard coding) or somehow append the 14 and 15 by adding 1 or 2 digits turning them into 16 & 17? does this need to be an update after I bring data down or can I do it during the Extract/Load part?
April 11, 2016 at 3:50 pm
jameslauf (4/11/2016)
I am pulling data from an application database. Our vendor is not the most responsive and the do not fulfill our requests very fast.I want to hard code a Query to fill a table on our Local Database from the Vendors to add 2 rows with specific data.
I also want to put in some type of error handling IF the vendor updates the table without telling us.
Here is my Query:
SELECT [TransactionTypeId]
,[Code]
,[Description]
,[IsShown]
,[IsActive]
,[SortOrder]
FROM [dbo].[TransactionTypes] Union
SELECT 14,'WO','Write-Off', 1, 1,14 UNION
SELECT 15,'WO','Write-Off Reversal', 1, 1,15
What transform do I use in SSIS to Check the source database and if it has a 14 or 15 for TransactionTypeId already to either skip this step and go to another one (which i would have the Query without additional hard coding) or somehow append the 14 and 15 by adding 1 or 2 digits turning them into 16 & 17? does this need to be an update after I bring data down or can I do it during the Extract/Load part?
First off, having two codes with the same WO code seems pointless! The code column should (I would have thought) have a unique index on it.
For similar reasons, your 16,17 idea is a bad one. You will be creating multiple rows with the same code, but different transaction type Ids.
The standard way of doing this sort of thing is either
1) To bring down the data to a staging table and then MERGE it to your target table. I would have expected this MERGE to match on code, but you'll have to think of a different way, given your data, or
2) To perform a lookup in your SSIS dataflow such that data which already exists in the target is sent into the ether & only new data is pushed into your target.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2016 at 7:01 am
I would suggest to have a unique or primary key constraint in your target table to prevent the insertion of rows 14 and 15. As Phil said, there's no use on duplicating them.
The MERGE and lookup, can be resource intensive which would delay your process.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply