June 16, 2009 at 8:54 am
Good Morning,
I am trying to build an SSIS package that pulls data from 2 AS/400 query output files, and an MS Access table. The goal is to produce a SQL Server database table that will serve as the basis of a SSRS report. In order for this to work, I need all records from AS400 query A loaded into the table where they match records in query B such that the formula (query b discharge date - query A admit date)<=30 and is not null. Null records can be discarded. The final result needs to be written to my SQL Server database table with MD Name, affiliation and specialty looked up from the MS Access table. I created an SSIS package that has query A attached to a Lookup transformation, then a sort transformation on MedRec. Query B is also sorted by MedRec. The results are attached to a Merge Join using left outer join. (remember, I need one record from query A because I'm summing costs, charges etc.). The Merge Join is attached to a Conditional Split that evaluates the above formula. If records pass Case1, they are loaded into my SQL Server database table. If Case2 is true, nothing is done with those records (not written to any file). When I view my database table, I see multiple records from Query A for one instance, which does not permit me to sum charges, costs etc. The MD information from the Lookup does not get written at all, those database fields are blank. Can anyone suggest a better way to design my SSIS package such that the MD lookup works and I only have one record from query A in my resultoing database? Thanks so much for your help!
June 16, 2009 at 2:06 pm
:w00t:
June 17, 2009 at 1:33 am
hmmmm, before you redesign(if need be) your package, you'll need to determine where the duplicates are coming from. This is what will define your package structure. It's pretty difficult to give someone assistance as to how to build a package when you can't see the data. Good Luck
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 17, 2009 at 9:17 am
It turns out I was trying to do a little too much in my SSIS package. I had acquired the data using AS/400 work query and had the join logic in place there. My original package used a Merge Join which was the problematic component. By simplifying the package, elminating the merge join, and doing the join logic on the AS400, the end result was a much cleaner data set. There are still anomalies that occur secondary to the business logic, but at least I have a clean dataset on which to build my report.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply