Transformation For Lookups Between Two Values
Jul 6, 2006
I have a dimension table for Retail Order Size. Each row in the dimension has a Starting Value and Ending Value column pair. In TSQL, the correct RetailOrderSize key is found by using the BETWEEN statement, like so:
SELECT RetailLevelKEY
FROM dbo.DimRetailOrderSize
WHERE @Sec1Retail BETWEEN StartingValue AND EndingValue
Is there a Data Flow Task Transformation in SSIS that replicates this functionality, or some other way of getting to the same answer in SSIS?
Thanks in advance for your help
View 1 Replies
ADVERTISEMENT
Jan 23, 2006
Hi, I am trying to use the Unpivot Transformation, and I have the following mapped out, following the example from the SQL Server 2005 BOL Unpivot Transformation:
Input
Destination Column
Subject
ValueNumeric
ValueString
ValueNumeric
Pivot Key Value
RACE
OTHRRACE
GENDER
Column Name
Subject
RACE
OTHRRACE
GENDER
Data Records
99999
1
NULL
1
88888
NULL
Hispanic
2
77777
2
NULL
2
Desired Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
ValueString
Data Records
99999
RACE
1
NULL
99999
OTHRRACE
NULL
NULL
99999
GENDER
1
NULL
88888
RACE
NULL
NULL
88888
GENDER
2
NULL
88888
OTHRRACE
NULL
Hispanic
77777
RACE
2
NULL
77777
GENDER
2
NULL
77777
OTHRRACE
NULL
NULL
This doesn't work, however, as I have multiple data types. So unless someone knows a better way, I had to split it into two Unpivot Transformations, which I later Sort and Merge together. The two Unpivot Transformations look like this:
Input
Destination Column
Subject
ValueNumeric
ValueNumeric
Pivot Key Value
RACE
GENDER
Column Name
Subject
RACE
GENDER
Data Records
99999
1
1
88888
NULL
2
77777
2
2
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
Data Records
99999
RACE
1
99999
GENDER
1
88888
RACE
NULL
88888
GENDER
2
77777
RACE
2
77777
GENDER
2
Input
Destination Column
Subject
ValueString
Pivot Key Value
OTHRRACE
Column Name
Subject
OTHRRACE
Data Records
99999
NULL
88888
Hispanic
77777
NULL
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueString
Data Records
99999
RACE
NULL
99999
OTHRRACE
NULL
99999
GENDER
NULL
88888
RACE
NULL
88888
GENDER
NULL
88888
OTHRRACE
Hispanic
77777
RACE
NULL
77777
GENDER
NULL
77777
OTHRRACE
NULL
The first Unpivot works great, because the data is mandatory. However, the second is full of NULLs so the actual output from the second Unpivot, prior to the merge looks like this:
Actual Output
Subject
Question
ValueString
99999
88888
OTHRRACE
Hispanic
77777
But I expected (and need) this:
Desired Output
Subject
Question
ValueString
99999
OTHRRACE
NULL
88888
OTHRRACE
Hispanic
77777
OTHRRACE
NULL
I hope someone can give me some advice on this!
Regards,
Richard Hein
View 3 Replies
View Related
Jun 28, 2006
Hi,
Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows
All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.
All NOT NULL values of Col1 should be processed by the Look up component.
If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.
Regards,
Gopi
View 3 Replies
View Related
Apr 10, 2007
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
View 14 Replies
View Related
Jun 5, 2006
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
View 5 Replies
View Related
Apr 22, 2015
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
View 3 Replies
View Related
Aug 24, 2001
I saw a DTS Lookup sample here on SWYNK, but I still have no idea what they're talking about.
Can someone dissect this piece of code and tell me what it does?
DTSLookups("TransformSpecialty").Execute(DTSSource ("LocalSpecialty").Value)
You can reference those tables they have in the example. http://www.swynk.com/friends/green/dtslookups.asp
Thanks!
Lost, ttlai
View 1 Replies
View Related
Feb 28, 2008
Hi,
My process passing 1,000,000 rows to a data flow with about 20 lookups to get the keys that I wantted .
Most lookups have small number of rows except one with over 5,000,000 rows. I cannot get the process to run (the process hanged) probably because of memory issue. Any clue where/how I can tune it.
Thanks
View 1 Replies
View Related
Nov 13, 2006
I want to do a lookup on date column. My lookup date is of type smalldatetime, and my date is of type datetime (date with time component). My lookup is failing because of incompatible data types.
How do I perform the lookup with date columns having date and time components?
View 1 Replies
View Related
Mar 17, 2008
Im having a little diff speeding up the below process
I have two tables table a contains 300,000 rows which are unique however the identifier can appear more than once. The fields im interested in are the identifier and a date/time field.
Table two also contains an identifier and a date field and again can contain multiple instances of the identifier with a variyity of dates.
For each row in table a i want to review the date and look up table b for the first date greater then or equal to the date linking by the identifier.
i have managed to do this via the code below however it takes 45 mins and i want to speed this up.
Select
a.*,
(select Min(DateB) as DateB From #tableB b where a.identifier = b.identifier and b.DateB >= a.DateA) asDATE
From #TableA a
View 2 Replies
View Related
Apr 28, 2008
Hello,
I have a source table with few million rows in it. As a part of transformation, I need around 10 lookups in 10+ different tables, all of them having few million rows each. I am looking for an approach that would be reasonably speedy and easy to manage future changes.
Here are some of the things that I have tried...
(1) If I implement as lookup components, they cause developer machine to go really slow and takes forever to run.
(2) I tried having OLE DB Source query to fetch required data up front. But the source query becomes very complicated which will be even harder for future changes. And this big query cause SQL Server to become unresponsive.
(3) Update queries on target table are also causing server to be unresponsive.
What would you guys suggest for this type of implementation?
Kapil
View 5 Replies
View Related
Sep 12, 2014
I have some SQL experience, but nothing past basic commands. I'm trying to take some data held by an application to use as CSV import into another application.I have two tables from an application, one holds references made in another.The first tables holds details about a person:
field1=name field2=age field3=country
Joe,50,1
Country is held as a number, then there is another table that holds all the countries:
field1=id field2=description
1,USA
2,France
3,Germany
I want to do a lookup where it returns:
Joe,50,USA
View 1 Replies
View Related
Mar 26, 2008
Hi,
We use lookups to join a few huge tables in SSIS (each has more than 40 million rows). The process took almost two days to complete when we select partial load on lookups. It stops/locks if we select full load on lookups.
We have a 32bit server so SSIS uses only 2-3GB of available memory no matter how big RAM we have. It seems the best solution for my problem is to move to 64bit server so SSIS uses up to 16GB of Ram.
For now I am researching for a remedy solution to get better performance from our current environment while we are waiting for the big server.
I’d like to hear your thoughts and options that may improve the performance of our package. Dose partitioning help? What else could be helpful?
View 4 Replies
View Related
Apr 16, 2008
Whenever we open a task and choose the lookups tab ellypsis button we get this error
Microsoft SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.
If you were in the middle of something, the information you were working on might be lost.
Any idea what might be causing this? Am I missing something?
View 3 Replies
View Related
Apr 25, 2006
When do a fact table load...I have to perform lookups against the dimension
tables. The dimensions tables I have support slow changes, however, and thus
have multiple rows for a single legacy key under different effective start
and end dates. In order to do this lookup, I have to not just join on the
legacy key, but also validate that the date of the transaction I'm loading is
between the effective date range of the dimension item.
It seems the Lookup task only supports equijoins. Am I missing something
here? How is this accomplished if you can use greater than or equal to and
less than type join conditions?
View 3 Replies
View Related
Dec 22, 2007
Ive got an ETL process I have written which takes about 10 million rows from a staging database and loads it into production database with an INSERT statement. The INSERT statement makes a function call to retrieve the surrogate key for each row. The function looks in a replicated copy of our production database so no load is on our production environment during this time.
So: INSERT INTO foo(...) SELECT name, address, zip, dbo.fnGetSurrKey( name, address)
It took about 12hrs to insert 6 million rows last night and Im wondering if there is a better way of doing this. Maybe a multithreaded way like SSIS might have.
Assuming my function is optimized as much as possible, does anyone have any tips for speeding this up?
Also, the machine this ran on has 16gb of RAM but was setup to use only 2GB during this process. I have already changed it to 12gb and restarted the process a week ago, but the change doesnt take affect until you reboot. Would I see a significant performance increase from that?
View 7 Replies
View Related
Feb 2, 2006
Is there an easy way to get the Lookup component to ignore case?
View 1 Replies
View Related
Nov 13, 2006
So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.
I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.
Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.
What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.
Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.
View 4 Replies
View Related
Feb 18, 2007
In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)
Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.
Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?
View 4 Replies
View Related
Jun 1, 2007
I am trying to make a SSIS package..
In the dataflow of my package, I must check from one table whether a row exists, and if that row exists, I should get some other row from another table, and update that..
I think to check whether a row exists, i should use "Look Up"
But cant we pass parameters to LookUP?
I am trying to use this SQL:
SELECT count(*) FROM ServicePackets where ID = ? and CHANGEDATE > ? and status = 1
I should get if that row exists or not only... (true or false)
How can i use parameters in LookUps?
View 7 Replies
View Related
Apr 10, 2008
OK I have this table I am grbbing from Oracle and I need to take selected columns and do a value lookup against another table: IE Here is a list of fields I get from Oracle:
Code SnippetENTRY_ID
SUBMITTER
CREATE_DATE
ASSIGNEE
LAST_MODIFIED_BY
MODIFIED_DATE
STATUS
SHORT_DESCRIPTION
CATEGORY
TYPE
ITEM
SCHEMA_NAME
SCHEMA_KEYWORD
ID
FULL_NAME
USERNAME
PAGER
PAGERPIN
PAGING_METHOD
RECORD_TYPE
GROUP_ID
GROUP_ALIAS
APPLICATION
NOTIFICATION_SERVICE
ARS_GROUP_NAME
GROUP_TYPE
AUDIT_TRAIL
FULL_NAME_
RESPONSIBILITY
PAGER_EMAIL_ADDRESS
Now I need to do a value lookup on these fields:
Code SnippetSTATUS
RESPONSIBILITY
In this example it is only 2 but in other conversions it could be 20 or more... Now here is my select statement for each Field (The ? being the FIELD from before IE Status or Responsibility):
The is for Status:
Code Snippet
SELECT VALUE AS STATUS_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Status')
This is for Responsibility:
Code SnippetSELECT VALUE AS RESPONSIBILITY_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Responsibility')
So for this the way I am doing it now is I have 2 "Lookup" Components setup... It works fine... However as I said when I get say 20 or so it gets really tiresome. I was wondering if I could feed it a EXCEL or XML file saying these are the fields that need a value lookup where:
FIELD - Project - FIELDNAME VALUE - OUTPUT VALUE
So with this example I would have a file saying something like this:
STATUS - Project Name - Status - STATUS_VALUE
RESPONSIBILITY - Project Name - Responsibility - RESPONSIBILITY_VALUE
Then it runs whatever and returns the *_VALUE for each row it goes through... Any suggestions?
View 12 Replies
View Related
Jul 20, 2006
Why can you not turn off the caching in a Lookup against Oracle?
I have an exceedingly complicated SQL statement like this -
SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE
If I turn off the cache for a lookup I get bombarded with this rubbish-
Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0
Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0
I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".
Any ideas ?
View 7 Replies
View Related
Jun 19, 2007
Hi,
Please help me out in loading the fact tables
I had used lookup on DIM table to get my SUK and if I use union transformation to get the out put from each lookup and then loading the data with some condition the data in my fact is not loading in a proper format.
The union transformation is splitting the out put in to different records
Please do inform me about which transformation should be used to get the data from lookup tables.
Or please do inform me the approach to load the fact table in SSIS.
I€™m basically INFORMATICA resource and I€™m implementing in terms of INFORMATICA
View 6 Replies
View Related
Sep 28, 2006
Hello,
I'm trying to clean my data using fuzzy lookup algorithm though SSIS, but i get null values everywhere. This is what i did:
I applied the fuzzy lookup in a table (tblValues). As source table i have the tblValues, and as reference table in Fuzzy Lookup i have the tblValues as well, resulting null values in all fields/columns.
Do i have to create my own reference table? If yes, how do i do that and what values will i have in this table?I didn't understand how the reference table must be in order the algorithm to work. Any suggestions?
Thank you in advance!
View 2 Replies
View Related
Apr 8, 2008
Our existing DW's ETL was written in a very complex fashion by the previous team. They use DTS package lookups to read a row in the Source SQL Server database see if that row exists in the taget SQL Server database. If the row does not exist, they use ActiveX scripts to INSERT the row in the target SQL Server database. If it exists, they update the row on the target side. How would you do this in SSIS? Apologize if this sounds like a basic question, however, I would have done this via Stored Procedures or SQL Scripts especially since it involves SQL Servers alone. Appreciate any help.
View 6 Replies
View Related
Feb 4, 2006
I notice that SQL Server 2005 creates worktables where SQL 2000 does not. Often these work tables appear in STATISTICS IO, but they show a 0 scan count and 0 logical reads. These worktables often appear to be substituted for bookmark lookups.
Has the optimizer decided to use worktables instead of bookmark lookups (often resulting in a higher cost plan)?
Sharon
View 7 Replies
View Related
Aug 12, 2007
Hello there,
Is there any way to increase the speed of search while performing fuzzy lookups against a 300,000 row Table ?
View 1 Replies
View Related
May 4, 2006
I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table.
My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory.
Any ideas/suggestions would be appreciated.
View 4 Replies
View Related
Jul 5, 2007
Is there any particular throughput/network/memory advantages to using Sql Compact files as lookup reference table sources, particular for static or largely static data, and if the SSIS package execution servers are running jobs remotely, aka, not "on the database server"?
I've been experimenting using Sql Compact as a OLEDB lookup source for reference data (business key => surrogate key), for example, using an OLEDB connection manager with the following connection string.
Data Source="C:\ISRoot\Cache\Cache.sdf";Provider=microsoft.sqlserver.mobile.oledb.3.0;
View 4 Replies
View Related
Dec 19, 2007
All
I am in trying to clean and standardize the data during the ETL processes using the €œLookup Data Flow Transformation€? in SSIS€¦
I am able to clean data by replacing the values in columns with values from a reference table, using an exact lookup to locate values in a reference table.
What I would like to do is €œif there is NO exact match€? replace it by e.g. zero or some other value which means €œno reference data available€?, how do I do this?
Any help is much appreciated.
Thanks,
Manojkumar
View 9 Replies
View Related
Feb 17, 2008
i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that.
so, i have a dataflow whose first component extracts certain kinds of data from an xml document.
each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update.
So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.
View 7 Replies
View Related
Jan 22, 2008
Have a situation where I need to check 100+ columns in the dataflow against lookup values to make sure all values are valid, and wanted to take a poll. Would it be better to
1) load the data into a working table and use traditional stored procedure (either NOT IN or LEFT OUTER JOIN where x is null) in order to weed out my bad values against my lookup table.....example
SELECT a.Col1
b.Col2
FROM Table1 a
LEFT OUTER JOIN Table2 b
ON (a.JoinCol = b.JoinCol)
WHERE b.JoinCol IS NULL
This results in poor performance b/c my temp work table is not really optimized for joins over to the lookup tables & I have so many columns that I don't really want to add all these indexes - my thoughts were that the index builds would take longer than the table scans.
OR
2) Use a huge number of lookup transforms in my data flow and keep it all in SSIS.
#1 is easier to maintain (my opinion) for future purposes but slower b/c I don't want to deal with indexes on the work table b/c it will be highly volatile. So - less cumbersome but slower
#2 will be more difficult to maintain b/c of the sheer # of lookups (since I can't change the SQL statement @ run time I have to put them all in separate lookups). Probably will run faster though b/c I won't have to deal with the transfer of the data to the db and also will avoid the table scans from #1. So - more cumbersome but faster.
What do others think? Or is there a better way?
View 4 Replies
View Related
Nov 16, 2006
Let's say I have 4 columns coming from my OLE DB source.
Column1
Column2
Column3
Column4
I also have a table that I'll be using in a lookup, LUPTable. In LUPTable, I have two fields, LUPField, ReplaceField.
In my data flow, I need to take columns, 2-4, and look them up against LUPField in LUPTable. I then need to add the value of ReplaceField (when a match is found) into the data flow.
The problem that I'm running into is that I don't want to sequentially do the lookups in the dataflow, because that's just a waste of time/memory. I only need to build the in-memory lookup table once, because that exact same data (it is static, for the most part) will be used for the remaining lookups.
What is the best way to achieve this?
The goal is to have the following columns remaining in the dataflow:
Column1
NewColumn2 (containing value from ReplaceField)
NewColumn3 (containing value from ReplaceField)
NewColumn4 (containing value from ReplaceField)
Column2-4 can be dropped from the dataflow after the lookups.
Thanks,
Phil
View 7 Replies
View Related
