February 7, 2012 at 5:48 pm
I'm struggling to get my first SSIS package off the ground so here goes:
I've got a flat file source that will very from say 100,000 rows to 1,000,000+ rows,
Flat File:
"Name""Data Type""Precision""Scale""Length""Code Page""Sort Key Position""Comparison Flags""Source Component"
"Status""DT_STR""0""0""50""1252""0""""Flat File Source"
"Weight""DT_NUMERIC""18""0""0""0""1""""Flat File Source"
"VisionGrade""DT_STR""0""0""50""1252""0""""Flat File Source"
"SizeName""DT_STR""0""0""50""1252""0""""Flat File Source"
"BatchId""DT_STR""0""0""50""1252""0""""Flat File Source"
"GrowerCode""DT_STR""0""0""50""1252""0""""Flat File Source"
"BatchName""DT_STR""0""0""50""1252""0""""Flat File Source"
"StartTime""DT_DATE""0""0""0""0""0""""Flat File Source"
"VarietyName""DT_STR""0""0""50""1252""0""""Flat File Source"
"SizerSerial""DT_STR""0""0""50""1252""0""""Flat File Source"
"SizerName""DT_STR""0""0""50""1252""0""""Flat File Source"
"SizerGradeName""DT_STR""0""0""50""1252""0""""Flat File Source"
I need to compare the "Weight" field against a different data set (in SQL 2005 view) to see where what range the "Weight" falls in:
Weight View:
"Weight_Min" "Weight_Max" "Sized"
.00 .25 0
.25 .50 1
.50 .75 2
It's simple enough in SQL to determine the "Sized" value (by design this will always return only 1 value per record in tableA):
select tableA.*, tableB.Sized from tableA
inner join tableB on tableA.weight >= tableB.Weight_Min and tableA.Weight < tableB.Weight_Max
I'd like to replicate the above functionality in SSIS 2005 for each row, so I can then pass the results to an Aggregate to roll up the data and insert the results 20-80 rows into a table. I've tried to use this http://blogs.msdn.com/b/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx to work through a look up or merge join, but I've been unsuccessful.
Merge Join Method:
To get the Merge Join to run I had to add a Derived Column to both data sets to create an artificial key, and I then had to add a sort so that both data sets were sorted by the FakeKey and they could join. I added the conditional split, added criteria in the same context as the sql example above, but the results were all null for the size/weight data. Additionally because there is no literal key or ability to specify additional join criteria like the sql select above the join is not 1:1,so the test data of 500,000 rows turned into a join of 8,000,000+ and was rather slow.
Lookup Method:
When I try this method again using a FakeKey I get the following error:
Error1Validation error. Data Flow Task: Lookup [11552]: input column "Weight" (13302) has a datatype which cannot be joined on.Package.dtsx00
As far as I can tell the data type for the Weight column and the Min/Max Weights are both the same DT_NUMERIC.
Modified SQL Lookup Statement (based on the link I was following):
select * from
(select '1' as FakeKey, convert(decimal(15,3),weight_min) as weight_min, convert(decimal(15,3),weight_max) as weight_max, size_name
from v_ap_weight_tp_cpe_packout
where pack_schema = 'TP') as refTable
where [refTable].[FakeKey] = ? AND ([refTable].[weight_min] <= ?) AND ([refTable].[weight_max] > ?)
I saw mention of other DT_DataTypes that can prevent the join or look up, but I did not see mention of DT_NUMERIC being one of those.
I'd appreciate any help.
Chris
February 13, 2012 at 4:09 pm
So the fix for me was to add a manual FakeKey to both data sources because you have to have at least 1 straight field a = field b mapping to get a merge join to work, you can not merge on an expression. So I added a Derived Column after the Flat File Source flow called FakeKey with a static "1", then added a Sort on the new FakeKey field because merge joins want sorted inputs. Modified the OLE source to include a FakeKey column with a static "1," and set the output to IsSorted and SortKeyPosition to 1 for the FakeKey field. That let them enter the merge join properly, with the down side that every record merges to 16x the original count since I can only specify the FakeKey field to join on, not an expression to join on weight > min_weight and weight <= max_weight like I'd like too.
February 14, 2012 at 12:10 am
Thanks for posting the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply