Dynamic Lookup

Hey all,

I'm going to start by saying that I haven't been able to search the forums before asking this, because I wouldn't know how to start looking for it (which words etc). So if this has been asked a couple of times before: sorry. We're having a bit of a lookup problem and was wondering whether you guys have any advice.

Say we have a table that contains info about products that changed productgroup. We always get an IN and an OUT line. The OUT line where it goes out of the system, the IN where it returns under a new group. We want to link those. There are a couple of criteria that need to be considered.

id must be the same (of course) the datediff can max be 1 day there is a commentfield that states for the OUT on to which new one it's changing and for the IN it states where it came from.

Any ideas how to handle this in SSIS? Thanks in advance,

Kevin
I don't think I'd try to use a look up for this. Based on what you said, I'd build a table through an Execute SQL. However, you didn't really describe what you are doing in the data flow, so I may not understand the problem correctly.|||Well, the dataflow will be linking the IN and OUT line together. What we're basically doing in this dataflow is taking the IN lines and adding a field that contains the ID of the OUT line. In another dataflow we do it the other way around.|||

From the description, it sounds like a product can only move once in a 24 hour period (or is once in the same day)? If it is only once a day, and the both rows show up on the same day, set your lookup to query only OUT rows, and match on the ID and date.

If it is in a 24 period (requiring a condition on datediff = 1), you might be able to use the lookup by enabling memory restriction and customizing the SQL Statement executed. However, I think it would be easier (and better performing) to perform the join in the source component. You'd have to create two inline views on the same table, one filtering for INs, one filtering for OUTs. Then join on the ID and the DATEDIFF condition.

|||

jwelch wrote:

From the description, it sounds like a product can only move once in a 24 hour period (or is once in the same day)? If it is only once a day, and the both rows show up on the same day, set your lookup to query only OUT rows, and match on the ID and date.

If it is in a 24 period (requiring a condition on datediff = 1), you might be able to use the lookup by enabling memory restriction and customizing the SQL Statement executed. However, I think it would be easier (and better performing) to perform the join in the source component. You'd have to create two inline views on the same table, one filtering for INs, one filtering for OUTs. Then join on the ID and the DATEDIFF condition.

I think John and I are thinking along the same lines. This work should be done in T-SQL and then can be read from a Source or a Lookup as needed.

Maybe something like this:

Code Snippet

SELECT DISTINCT
ProductId
, ComingFromGroupId = A.ComingFromGroupId + B.ComingFromGroupId
, GoingToGroupId = A.GoingToGroupId + B.GoingToGroupId
FROM
(
SELECT
ProductId
, RecordDate
, ComingFromGroupId = CASE WHEN CHARINDEX('came from',CommentField)>0 THEN CONVERT(int,SUBSTRING(CommentField,0,0)) ELSE 0 END
, GoingToGroupId = CASE WHEN CHARINDEX('going to',CommentField)>0 THEN CONVERT(int,SUBSTRING(CommentField,0,0)) ELSE 0 END
FROM
MoveTable
) AS A
INNER JOIN
(
SELECT
ProductId
, RecordDate
, ComingFromGroupId = CASE WHEN CHARINDEX('came from',CommentField)>0 THEN CONVERT(int,SUBSTRING(CommentField,0,0)) ELSE 0 END
, GoingToGroupId = CASE WHEN CHARINDEX('going to',CommentField)>0 THEN CONVERT(int,SUBSTRING(CommentField,0,0)) ELSE 0 END
FROM
MoveTable
) AS B
ON A.ProductId = B.ProductId
AND ABS(DateDiff(d,A.RecordDate,B.RecordDate))<=1
) AS X

Hopefully, your products aren't changing groups more often than once a day, otherwise the wrong associations might be made.
|||Thx for your input on this.
JayH: I'll be looking into this and trying this one out, thx.

jwelch: I've been trying to get the modified SQL Statement going but I always end up with 2 problems. The first one tells me that the parameters I use are not Input. I get around this one by going into advanced mode and selecting the columns I need in the "Input Columns" tab. After that, I get an error stating that the column is a datatype which can not be joined on. I'm puzzled whith this. Has anyone hit this before?
|||No, what data source are you using?|||Native SQL OLE DB
|||Can you post the SQL statement you are using?|||

Clopin wrote:

Thx for your input on this.
JayH: I'll be looking into this and trying this one out, thx.

If you do, you should add "AND A.CommentField <> B.CommentField" to the ON clause of the join between A and B. Need a way to keep the same rows from finding each other. Another field would work better if you have one (like a record identifier).
|||The modified SQL for the lookup?

select * from (SELECT HDID03, HDTP03, HDTM03, CNID03, LYND03, HDST03, HDDT03, OP0103, HDRA03, SYSTEM FROM STG_MD_CTHNDL WHERE (HDTP03 = 'IN') AND (HDST03 = 'OL')) as refTable where [refTable].[HDTM03] = ? and [refTable].[CNID03] = ? AND CHARINDEX([refTable].[OP0103],?,1) <> 0 AND CHARINDEX(?,[refTable].[LYND03],1) <> 0) AND (DATEDIFF("dd",[refTable].[HDDT03],?) <= 1)
|||

Clopin wrote:

The modified SQL for the lookup?

select * from (SELECT HDID03, HDTP03, HDTM03, CNID03, LYND03, HDST03, HDDT03, OP0103, HDRA03, SYSTEM FROM STG_MD_CTHNDL WHERE (HDTP03 = 'IN') AND (HDST03 = 'OL')) as refTable where [refTable].[HDTM03] = ? and [refTable].[CNID03] = ? AND CHARINDEX([refTable].[OP0103],?,1) <> 0 AND CHARINDEX(?,[refTable].[LYND03],1) <> 0) AND (DATEDIFF("dd",[refTable].[HDDT03],?) <= 1)

I'd guess the parser isn't handling the CHARINDEX properly, since it's on the left side of the equality check. Unfortunately, I'm not in a position to test that right now, but you might try making it 0 <> CHARINDEX([refTable].[OP0103], ?, 1) and see if that makes a difference.

|||Unfortunately... it didn't =/
|||Just a heads up on how I solved it, because I did. Like jwelch asid, I modified the SQL Statement. But that didn't really go as planned. Scroll up a bit and you'll notice the error's I got. What made it tick, was that I had to link the fields first ni the mappings. Then I could use them. I couldn't if I didn't do this mapping, giving me the errors I stated earlier.

So if anyone gets those errors, try linking them first, then go and modify the sql statement as you please.