May 31, 2017 at 3:45 pm
hi
I have 2 files with key column. I have to compare by 1 key, if compare is match and other fields r not match i need to give those rows to third file and put 1 indicator as extract field in 3rd file.
same if data id available in one file and not in another then move those in 3rd and put indicator,
at the end i need to put number of record added, change or deleted in log files
how to do this in SSIS.i don't have any sql table it just coming as flat file and i don't want to dump it in any stage table.
thanks
May 31, 2017 at 4:36 pm
coool_sweet - Wednesday, May 31, 2017 3:45 PMhiI have 2 files with key column. I have to compare by 1 key, if compare is match and other fields r not match i need to give those rows to third file and put 1 indicator as extract field in 3rd file.
same if data id available in one file and not in another then move those in 3rd and put indicator,
at the end i need to put number of record added, change or deleted in log files
how to do this in SSIS.i don't have any sql table it just coming as flat file and i don't want to dump it in any stage table.
thanks
Why don't you want to import them into staging tables? From there, you could take the logic you described and translate it into queries.
May 31, 2017 at 5:32 pm
ok lets say i want to dump into staging,what is the logic here
May 31, 2017 at 5:39 pm
coool_sweet - Wednesday, May 31, 2017 5:32 PMok lets say i want to dump into staging,what is the logic here
You already have it - in your description.
WITH cteDiffs AS (
SELECT ID, Code, Description
FROM dbo.TableA
EXCEPT
SELECT ID, Code, Description
FROM dbo.TableB
)
INSERT INTO dbo.TableC(ID, Code, Description)
SELECT ID, Code, Description
FROM cteDiffs
ORDER BY ID;
There may very well be a simple way to do this in SSIS, but I'm not the one to help you if there is.
June 5, 2017 at 9:51 am
First - why don't you want to stage the data? That's a very common practice and a good filter to make sure your data is as you expect it to be.
If you want to do this in SSIS look at the Conditional Split transformation and Expression language. But at that point you're moving table to table so a stored procedure will probably be more efficient than a data flow.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply