How to join Excel Source to OLE DB Source while pulling in all columns from both sources?
I hope I can explain this question well - but I have an excel source that has 1.1M rows. The columns are draw date, data month, payor code, cpt code, account number and units. I am pulling from several tables on my OLE DB source with a SQL command (the SQL query works fine, I have checked in the server directly.) I am pulling in the same fields(columns) from my OLE DB source as my excel source. In the excel file, I have derived column that creates 2 new columns based off of draw date (month, year).
From there on the excel tree, I have data conversion so that the data type on unites, year and month from excel match the data type in my OLE DB Source.
I then have sort for month and year on both sources that connect into merge join. This is where my issues start. The data loads incorrectly as well as take a very, very long time to load into the server (200k rows in 4 hours). What am I doing wrong, and is there another way to execute this?
My DB source is reviewing about 21 M rows and spitting out like 2.5M after the merge join. Excel source is correctly loading in at 1.1M but seems to get stuck around the sort part and then pulls the wrong data through the merge join.
I need this to join on month and year in both sources and then bring everything matching the month and year in together. My DB source has billions of records, so my sql command has that limited down as much as it can. Thank you!