How to insert a lookup table row aftre lookup fails

I want to do a a look-up, then if that lookup does not find a record I want to insert a new record into the lookup table, so that effectively the lookup now succeeds.

How can I do that? I can't figure that one out.

TIAI have solved this problem.

I am using a two pass mechanism in the Control Flow. In the first pass (first Data Flow) I do a lookup and then on failure redirct to insert the records.

I have a number of these to do - so in the first pass I multicast the source data to each of the lookups.
You see, I am loading survey data entered into Excel spreadsheets by humans. :)|||FYI it is often more performant not to redirect the error rows but to just do a filter on the rows that don't have the lookup value. This is all down to the redirect creating a new row, and the associated costs of assigning memory etc.
where as processing the normal flow no extra data has to be created in the buffer.

Does that make sense?|||It certainly does Simon. Thank-you very much ... i did not think of that. :)|||

SimonSa wrote:

FYI it is often more performant not to redirect the error rows but to just do a filter on the rows that don't have the lookup value. ...
Does that make sense?

Simon,

Maybe you can provide some insight on a similar scenario. In my control flow, I do a bunch of processing over millions of rows and finally I want to insert a subset into an xyz_master table. However, I want to check if the record already exists or not. I am using the error row redirecting mechanism and it is painfully slow. As per your suggestion, I should do some kind of filter. How do I do it?

Let me know if I need to explain further.

TIA,
Nitesh|||So you have you output from the lookup which is the input to the lookup along with a value that was looked up, i.e the lookup value. For rows found in the lookup this column will contain the key and rows not found will be empty.

This extra column is added to the buffer so the whole row doesn't have to be copied to a new buffer, which is what happens with the redirect, only the new column value is populated in the existing buffer

You then use the conditional split to only output those rows with no value in the lookup key column. The key here is that the conditional split is a synchronous transform and so again uses the same buffer so no data is copied.

You can then use the output of the conditional split (the one where lookup column is null) in any other component.

One thing I will say is that if you are using the lookup make sure you are only selecting the columns you need from the lookup tables, i.e. just the lookup value and any other values needed to be added to the flow.|||Great ... that is exactly what I did :) Glad to see I am on the right track.

I love SSIS!

How to improve the performance of a query?

I have a table called DMPD_Product_Lookup_Dom. It is a lookup table which contains values for certain fields of other tables in the database.
This takes long time to run.
Is there any way to improve performance of this query ??

SELECT
BNAD.Benefit_Admin_Cat_CD AS 'AdminCategory',
DOM1.Value_Description AS 'AdminCategoryDesc',
BNAD.Benefit_Component_Name_CD AS 'BenefitAdmin',
DOM2.Value_Description AS 'BenefitAdminDesc',
BNDT.Benefit_Rider_CD AS 'BenefitRider',
DOM3.Value_Description AS 'BenefitRiderDesc',
BNDT.Benefit_Exception_Ind AS 'Exception',
BNDT.Benefit_Detail_Desc_CD AS 'BenefitDetail',
DOM4.Value_Description AS 'BenefitDetailDesc',
DMCS.Cost_Share_Value_Type_CD AS 'CSValueType',
DOM5.Value_Description AS 'CSValueTypeDesc',
DMCS.Cost_Share_Value AS 'CS_Value',
DMCS.Cost_Share_Rule_Cat_CD AS 'CS_Rule_Cat_CD',
DOM6.Value_Description AS 'CS_Rule_Cat_Value',
BNCS.Cost_Share_Rule_Type_CD AS 'CS_Rule_Type_CD',
DOM7.Value_Description AS 'CSRuleTypeDesc',
BNCS.Cost_Share_Mbr_Family_Ind AS 'MemberOrFamily',
DOM8.Value_Description AS 'MemberOrFamilyDesc',
BNCS.Network_Ind AS 'NetworkInd'
FROM
prdtrk01..DMPD_Product_Lookup_Dom DOM1,
prdtrk01..DMPD_Product_Lookup_Dom DOM2,
prdtrk01..DMPD_Product_Lookup_Dom DOM3,
prdtrk01..DMPD_Product_Lookup_Dom DOM4,
prdtrk01..DMPD_Product_Lookup_Dom DOM5,
prdtrk01..DMPD_Product_Lookup_Dom DOM6,
prdtrk01..DMPD_Product_Lookup_Dom DOM7,
prdtrk01..DMPD_Product_Lookup_Dom DOM8,
prdtrk01..BNAD_Benefit_Admin BNAD,
prdtrk01..BNDT_Benefit_Detail BNDT,
prdtrk01..BNCS_Cost_Share_Rule BNCS,
prdtrk01..DMCS_Cost_Share_Dom DMCS
WHERE
BNAD.Benefit_Admin_ID = BNCS.Benefit_Admin_ID
AND BNDT.Benefit_Detail_ID = BNCS.Benefit_Detail_ID
AND DMCS.Cost_Share_Rule_ID = BNCS.Cost_Share_Rule_ID
AND DOM1.Product_Domain_Entity = "BNAD"
AND DOM1.Product_Attribute_Type = "Benefit_Admin_Cat_CD"
AND DOM1.Domain_Value = BNAD.Benefit_Admin_Cat_CD
AND DOM2.Product_Domain_Entity = "BNAD"
AND DOM2.Product_Attribute_Type = "Benefit_Component_Name_CD"
AND DOM2.Domain_Value = BNAD.Benefit_Component_Name_CD
AND DOM3.Product_Domain_Entity = "BNDT"
AND DOM3.Product_Attribute_Type = "Benefit_Rider_CD"
AND BNDT.Benefit_Rider_CD *= DOM3.Domain_Value
AND DOM4.Product_Domain_Entity = "BNDT"
AND DOM4.Product_Attribute_Type = "Benefit_Detail_Desc_CD"
AND DOM4.Domain_Value = BNDT.Benefit_Detail_Desc_CD
AND DOM5.Product_Domain_Entity = "DMCS"
AND DOM5.Product_Attribute_Type = "Cost_Share_Value_Type_CD"
AND DOM5.Domain_Value = DMCS.Cost_Share_Value_Type_CD
AND DOM6.Product_Domain_Entity = "DMCS"
AND DOM6.Product_Attribute_Type = "Cost_Share_Rule_Cat_CD"
AND DOM6.Domain_Value = DMCS.Cost_Share_Rule_Cat_CD
AND DOM7.Product_Domain_Entity = "BNCS"
AND DOM7.Product_Attribute_Type = "Cost_Share_Rule_Type_CD"
AND DOM7.Domain_Value = BNCS.Cost_Share_Rule_Type_CD
AND DOM8.Product_Domain_Entity = "BNCS"
AND DOM8.Product_Attribute_Type = "Cost_Share_Mbr_Family_Ind"
AND DOM8.Domain_Value = BNCS.Cost_Share_Mbr_Family_Ind
AND BNCS.Product_ID = @.Product_ID
ORDER BY
DOM1.Sort_Seq_No,
DOM1.Value_Description,
DOM2.Sort_Seq_No,
DOM2.Value_Description,
DOM3.Sort_Seq_No,
DOM3.Value_Description,
DOM4.Sort_Seq_No,
DOM4.Value_Description,
DOM5.Sort_Seq_No,
DOM5.Value_Description,
DOM6.Sort_Seq_No,
DOM6.Value_Description,
DOM7.Sort_Seq_No,
DOM7.Value_Description,
DOM8.Sort_Seq_No,
DOM8.Value_DescriptionDo you have indexes on these tables?|||

Quote:

Originally Posted by iburyak

Do you have indexes on these tables?

Yes, the tables mentioned have indexes. But i want to know is there any other way to improve the performance of this query.|||Unfortunately you didn't provide specific answer.
To help you I need to see all index compositions.
Some people think they have indexes but it could be that they are not the once that such query will use.

To view if your query uses indexes in SQL Query Analyzer go to Query Show Execution Plan.
Execute your query and point to each item and see if your indexes are actually used.|||well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.|||

Quote:

Originally Posted by abhishek8236

well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.

Thanks Everyone! The Problem has been resolved.

How to ignore timepart when using LookUp with date.

Hi

I want to lookup the datekeys from my datedimension. However it does not work because matching for same dates does not work because of different timeparts. The dates in my lookup-table have a 00:00:00 timepart, but the dates in my input table have a non-zero timepart. How can I ignore the timeparts or make the timeparts zero?

Regards,
HenkI found a workaround:

add a derived column component before and cast to DT_DBDATE first and than back to DT_DBTIMESTAMP to get rid of the timepart: (DT_DBTIMESTAMP)(DT_DBDATE)DateColumn.

I am still interested in a solution that doesn't require an extra component.

Henk|||Can you not use T-SQL to change the data at source?

-Jamie|||Yes that's true, but the sports of SSIS is in doing it without coding T-SQL

How to identify a dialog as encrypted?

I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.

How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?

There is no way to find out looking only at the sys.conversation_endpoints table. But if you request encryption and there is no master key, the messages will stay pending with status set to 'The session keys for this conversation could not be created or accessed. The database master key is required for this operation.' in the sys.transmission_queue.

|||Thats what I guessed, shame really