Welcome to blogs.conchango.com

Jamie,

How could I do this for each table in the database instead of writing a seperate dataflow task or package for each table in the databasee. I know I could configure the source and destination tasks from variable, but how will i do it for the lookup transformation task. Please advice

Dheepa

For some time now I have been promising Marco Russo from sqlbi.eu that I would take a look at their TableDifference

Is it possible to update the blob in the way u have suggested

How do you add graphical text comments to the package?

Thanks for the response. ALthough this is unfortunate news, it mean I can stop waisting my time looking around for a solution or playing with the toolbars in BIDS ... :)

Do u know if MS intends to add this functionality? (Some things I do miss from DTS)

Jamie,

I think we're talking abt the same thing. In the DTS designer, you could click on the [A|B] icon and dorp in text annotations into the package, purely for documentation purposes. The BIDS doesn't seem to have this functionality.

Evan

Jamie,

Thanks. I see it now...

BTW, I luv ur sight, 'specially 'cause it contains working pkgs.

regards, Evan.

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too?

Thanks Jamie. I will try the lookup transformation instead. I asked the same question on MSDN too and Phil Brammer had the comment to the same effect. Sorry about asking the same question twice.

I may be mistaken but when I attach a SQL Destination to the error output of a LOOKUP the Destination "times out" if there is no error output.  This could be a potential pitfall to be aware of.

Hi Jamie,

Can we or should we use this approach as an alternative to the SCD Transformation? For large volume dimension the SCD seems to be too slow and I would like to use only build-in SSIS transformations (i.e. I cannot use the TableDifference Transformation from Alberto Ferrari).

Thanks,

Great site, it helps a lot.

FO

Hi all - great article and comments the best i've found.i'm very new to SSIS and still trying to find my way around. Can someone tell me is there disadvantages to using SCD compared to lookups and ole command? i need to be able to update and insert from source to destination and also delete redundant records at destination no longer in the source.

thanks Maggie

Hi Jamie,

I ran into another problem using this technique, and I saw that your pointed it out in one of your discussion group (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=717965&SiteID=1).

My main problem is in the Conditional Split when my dataflow contains a NULL column as well as my Lookup Column. It generates an error because it doens`t like to have NULL value instead of a Boolean.

My concern (maybe trivial for you) is how can I achieve the proper Split to avoid this kind of error? I know it`s achieved using ISNULL functions, but I don`t seem to find the proper way to do it.

My expression looks like:

(FINAL_Name != Lookup_Name && (!ISNULL(FINAL_Nom_Mun) && !ISNULL(Lookup_Name)) but this will work only when, in fact both Final and Lookup columns are Null. What happens when only one column is NULL. How can I write an expression that will take this possibility in consideration?

Thanks again

FO

Sorry about the error above. Note that my expression will only work when both inputs are not null.

(FINAL_Name != Lookup_Name && (!ISNULL(FINAL_Name) && !ISNULL(Lookup_Name))

How can I achieve my proper split when only one column is NULL or both are Null?

Thanks again

FO

Jamie,

No, I`ve tried to use it but with no luck. Even with only the expression

FINAL_Name != Lookup_Name, if one column of the Lookup is Null, I get an error.

Any work around?

Thanks

Jamie, sorry about that,

Here's more details,

For the following cases:

1) If the Lookup_Nom_Mun value is NULL and the FINAL_Nom_Mun is not NULL

2) if the Lookup_Nom_Mun value is not NULL and the FINAL_Nom_Mun is NULL

3) if both columns are Null

With the expression FINAL_Nom_Mun != Lookup_Nom_Mun in the Conditionnal Split

I get the error message below:

[Check for Modifications [95974]] Error: The expression "FINAL_Nom_Mun != Lookup_Nom_Mun" on "output "Modified Rows (SCD1)" (102221)" evaluated to NULL, but the "component "Check for Modifications" (95974)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

Note that both columns are varchar.

Thanks

FO

This problem is giving me a headacke...

I've changed my expression to

(DT_BOOL)(FINAL_Nom_Mun != Lookup_Nom_Mun)

but I get the same error message as before.

[Check for Modifications [95974]] Error: The expression "(DT_BOOL)(FINAL_Nom_Mun != Lookup_Nom_Mun)" on "output "Modified Rows (SCD1)" (102221)" evaluated to NULL, but the "component "Check for Modifications" (95974)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

Any more ideas?

Thanks again for your help, it's much appreciate

FO

Thanks for your help

FO

Hi Jamie thanks your site is great I have learned a lot from you. I have tried this soultion and it works great. I am still new to SSIS and was wondering how you would check if more then one column has changed.

I would apprecate the help as I have a soultion but it is very long winded and heavy on the system. Also how would you do the updates and inserts once the conditional split has completed.

Thanks in advance Scott

Thanks Jamie it works like a dream. I really appreciate the help.

Hey Jamie,

Great stuff.  I continue to use your site to answer all of my questions.  I understand your look up to compare values above.  But the table I am working with can be up to 6 million rows at some properties and I need to look for changes in 40 different columns.  I have tried implementing this lookup and it is taking all of my memory to cahce the whole table.  Is there a better way to do this when dealing with large datasets?  Any advice would be greatly appreciated.

Nick

Hey Jamie,

I implemented the following 5 tasks merge join, cached lookup, non cached lookup, Slowly Changing Dim, and a script similar to Greg's above.  The non cached lookup worked the fastest with the script not to far behind it, but I had to stop the others due to paging.  You were right again.  Thanks for the help.

Nick

Jamie,

Having formerly been employed at Conchango, I defintely had a feeling of 'Small World' after I first came across your blog a few months ago when I began developing ETL solutions with SSIS. It's defintely one of the more helpful SSIS resources online.

I've been trying to implement a package based on the ideas in this article as well as the other articles referenced a the top of this one.

Update or Insert into Table 1 in DB_A on Server_A from

Table 2 (aproximately 6 million records) in DB_B on Server_B

I've tried the MergeJoin method as well as the Lookup (no cache) method. Both methods seem to choke. The Merge Join method chokes on the sort component for Table 2, the Lookup nethod chokes at the lookup component.

Do I have any hope of implementing this in SSIS or is the data volume just too much for these types of operations to handle?

Thanks.

Hi Jamie

Nice site. I like the Lookup method, but my destination table (Table B) uses a unique identity key (Is Indentity=Yes, Increment=1) which will not exist in my source table (Table A). Table A can contain updates to existing records and new records.

So after detecting which records in Table A are new and which exist already in Table B - using the Lookup task - then working out which columns in the existing records have changed using the conditional task,

how can I get the existing record data output to UPDATE rather than INSERT into the final destination (Table B).

Will the destination data flow always do a SQL INSERT or will it do a SQL UPDATE - if so how do I tell it?

The Table B is one of the lookup tables, so I can't dump it at the start otherwise all history will be lost, and there will be nothing to merge.

I guess I could make a duplicate of the Table B first - to Table C, then truncate Table B so it removes ALL records, then use the above data flow tasks to load it all back in again from joining Table A and Table C. This will populate Table B will new identity keys for all rows - old and new.. But that seems clumsy.

Jamie,

Thanks for the reply. During the first run through Table 1 will have maybe up to 100,000 records. After the first run it will have 6M+. I tried the Merge Join method first and when it choked, I tried the Lookup method which also choked. So, I just didn't think to go back and refine the first attempt.

But after your post I did that and it does work though it's not exactly fleet of foot. It takes about 45 minutes to run through the first time. On a subsequent run through when it is making no inserts/updates it takes about 25 minutes to run.

Though the speed is less than ideal, it will be running in the middle of the night as part of a scheduled process so it isn't the end of the world.

Thanks Again.

Thanks for the link. I'll defintely give it a look, though I probably won't have a chance till the weekend since it's over an hour long. Too bad there isn't a transcript available.

Most of the extra time in the 45 minute run is during the INSERT (There are very few UPDATES), the MERGE JOIN also apears to take longer, possibly since on the first run there is a huge difference between the 2 source tables and on the second run there is nothing being merged.

I work predominantly in the data integration space and I suspect most of you reading this do too. One

hi Jamie,

it a very useful help you got there. im sure many who read it would find it a relief, like me. however, how about involving two different database?? MySQL and sql server mobile edition?? you see, i have a dekstop system (web-based actually) tht uses MySQL and i have been asked to build its mobile version in a pocket pc, which is a TOTALLY new to me. i am having trouble of what to do or where to start. what do u suggest? i have installed the sql server into my pocket pc and done the normal sync between dsktop n ppc tht uses same dbase. but i'm lost when it is involving two different dbase. should i install the MySQL mobile edition instead??

thank you in advance. i really do appreciate it.

Hi,

I have created the package in the same manner. But in oledb command i am using Oracle Provider but its showing error while mapping the columns.

Error at Data flow task[oledb command[8717]]: An oledb error has occured. Error Code:0x80040E51. An Ole db record is available. Sourece:"OraOledb" Descrition:"Provider cannot derive parameter information and SetParameterInfo has not been called"

Unable to retreive destination column descriptions from the parameters of the sql command

Any suggestiion to this case??

Regards,

Roopa

Hi,

I started with the SCD component. Since a short time i use the lookup en split. But the trouble with both of these options are NULLS.

In the Conditional split u case use the ISNULL command. But in the SCD this is not possible and thats where the trouble starts. when NULL is compared with something else, by default the condition is FALSE. when i use ANSI NULL OFF, it seems to work in Management Studio.

set ansi_nulls off

if NULL <> 's' select 'bout' else select 'test' (RESULT is 'test')

set ansi_nulls off

if NULL <> 's' select 'bout' else select 'test' (RESULT is 'bout')

I would like to know if its possible to use ANSI NULL OFF in SSIS. I cant find a place where is can set this command. When i use a SQL-task before the update its still does not work.

Can u help me out here?

Jamie,

Thanks for your reply. Now i can stop searching for it :-)

But does this mean i simply cant use the SCD component because we have NULL values? Or is there another way to work around these NULL values?

with regard,

Arthur

Ok so simple question, I've been using the LOOKUP to see if the row needs to be changed but my tables have like 100 columns and using the ole db command is driving me nuts.  Because there is no nice GUI for it.  No build SQL.  

So I've been using the SCD to build the sql.  Is this correct or I'm I missing something?  

I mean doing an UPDATE or INSERT to a table is so standard that why is this such a hard thing to find out how to do it?  I feel like I'm doing the wrong thing.

Here is what I'm doing, please tell me I'm doing it right.

data comes in -> if the mod date changed then -> update all columns else -> insert new row.  I use a LOOKUP if the mod date != LOOKUPmod date.  

Simple right. Now with LOOKUP a new row is an ERROR? This does not sound right.

Michael

No, I use Insert Destination to do inserts.  Now is OLE BO Command is it just slow for UPDATES and I should write a set-based update (which I don't know what they are I'm assuming it's like writing a stored Proc to update rows.  Cursoring throught to do the update).  Or it's the same just not very user friendly.

THANK YOU!  I knew I was missing something simple!  Things are working much better now.

-Michael

Guys.. I am new to SSIS... I tried understanding this package.. and ashamed to ask this simple question... Is customerlookup data destination or source... I mean from where this package will read data and against whom it will validate data and finaaly where it will modify data.. Please help...

Jamie,

Excellent info.  I have a question, though.  How do you handle a check for a change after the age check?  Let's say you want to check if the name changed as well.

Thanks,

- Bob

I cannot use lookup with the column type float (double-precision  float DT_R8)

The lookup has a red X on it and it said "input column "columnName" (xxxxx) has a datatype which cannot be joined on.

I checked datatype of input the 2 tables(which I want to lookup them for conflict rows) and they are the same type DT_R8.

Any suggestion please?

I cant believe it is so difficult to insert or update data with SSIS ,I have sold a company SQL2005 on the basis that it is easy to do.

guess who looks like a complete and utter dodo now !!!!!!!!!

Hi Jamie,

How can I identifying the updates using SQL Task.

I think BINARY_CHECKSUM is not the correct way to doing this. Right?

If im using SQL Task for updates, Can I use the same thing for Insert? OR better to follow ur approch?

Cheers

DF

Could you show me how to configure the lookup error output? And how to do the inserting? In my case, suppose I lookup on column named "column1", and it could correctly route to the error output when a row doesn't exist on destination table. However the inserted row in DB is with null value for column "column1". I don't know what's wrong. Any help on this?

mail: niel_qiu@msn.com

Hi Jamie,

In a lookup operation,if we have more than one value in the reference table corresponding to the lookup column,then does it return the combination of all the values OR does it stop retrieving after the first value is encountered.

For eg: In the above blog there are two records corresponding to Susan

Susan 26

Susan 29

What if there is a record with values   "Susan 30" coming as input and a lookup is done on the reference table with customer name as lookup column and age is retrieved from the reference table.

What will be the output for the lookup??

I tried it for a similar scenario and am getting output as

Susan 30 26

Only one record is returned.

Jamie,

How can I update a row in source in the same data flow? Example - Source is table A, which has 'Id' and 'clubId' as fields. I pass  'clubId' field through derived column transformation and want to update 'clubId' back in the source A for the same 'Id'. How can I do that in the same data flow?

1. Is there any other way apart from using Execute SQL Task and OLE DB Command?

2. I have never used OLE DB Command Component. Could you please give me some pointers?

Is there any way to only pull out the key identifier along with the field that is different instead of the entire record that is different?

Thanks for your help!

Jamie,

What about rows that got deleted from the source tables? Is there a better way of doing it other than reversing the source and Lookups and going through all the rows to see what doesnt match and delete?

I am using this type of solution to update a datawarehouse and this problem keeps catching me. A user will input a temporary order into the system and my dtsx package will pick it up and bring it over to the warehouse. Then the user deletes the order and there is nothing in my package to check for now non-existant rows within the datawarehouse.

Thanks,

Keith

Hi,

Jamie,

 I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1  being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like

Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates.

I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it.

I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP.

Thanks  

Jamie,

 No i don't have but if i place that it will slow down the performance and as it do updates row by row and where should i place before OLE DB destination as my package consists of

source(ff)- Lookup(target)- conditional split- derived column- OLE DB destination. Please get back to me ASAP.

Thanks

Jamie,

 I used OLE DB Command with update table set col2 = src.col2 where col1=src.col1 and in the control flow and i added execute sql task and issued the same command also i added data viewers to see the data movement everything is working fine & it says success but no rows have been updated. Any suggestions why is it not updating the records.

Thanks

Jamie,

  I got it using Lookup, Conditional Split, OLE DB and execute SQL task in control flow. Thanks a lot for sharing information.

Hi jamie

Now we converting the dts packages to SSIS packages. but i am facing a problem when reading data from the flat file. The problem is I have n number of rows in my flat file and some of the rows is missing one column delimeter but its working fine in dts packages with same flat file. but in SSIS its not. The row which is having one less column delimeter will taking next row also. I think row delimeter will work only when all the column delimeter should present. give some suggestion regarding the same. Thanks in Advance

Mukunda

Hello,

I use MS SQL Server 2005 for a long time, but I never used SSIS before. So since some days I try to use SSIS for transfer of data and it works very very well. But in my source database are some datasets which are existing in my destination database. So SSIS couldn't insert the datasets because there is a Primary Key constraint. SSIS should check if the datasets are existing:

- if they are existing and they are exactly the same, then they should be ignored

- if they are existing and the data is changed, then it should be changed/ updated in the destination database

- if the dataset isn't existing, it should be inserted in the destination database

So I saw you example but my problem is to create this workflow. So I don't know which settings I should do and what I should do exatcly in each menu.

Do you know if there is anywhere a little tutorial or a help for this problem? Well I searched for websites, forums, books, pdf-files and so one - but nowhere was a really helpful example for me :-(

I hope you can give me a hint?

Best regards,

Tim

Jamie,

Please consider changing your scenario like this: maintain Names and Ages but add Genders. And now consider that you only want to update the Age (in those cases where it has changed) if the Gender of that record is the same of the previous record where an Age update was performed.

Something like this:

SRC TABLE

KEVIN | 23 | M

MIKE | 24 | M

SUSAN | 29 | F

LKP TABLE

KEVIN | 22

MIKE | 23

SUSAN | 28

We want to update KEVIN's Age from 22 to 23 and MIKE's from 23 to 24 (cos MIKE's Gender is the same as KEVIN's); but we don't want to update SUSAN's Age (although it has changed) because SUSAN's gender is not the same as KEVIN's.

Brgds,

BG

Hi Jamie thanks your site is great I have learned a lot from you.

my source table contains 3 records

1    PATRICK  25

2    JANE 30

3    PATRICK  25

While the first time the package is running it's inserting PATRICK 2 time in the dest table.

how can i avoid this?

Jamie,

Me it has been quite useful east article, I help myself to include/understand better the SSIS. The problem that I have at the moment is that I made the update of my local base of data otherwise. " generates a variable; MaxFecha" where I obtain the value maximum of a field date of my base (local in SQL), soon in query SQL that use for the base of which under the data (Oracle 10g) I include in where that brings all the fields when a field (date) is major that this variable. The problem is that when trying to mapear the parameter in origin OLEDB it throws an error to me of call to " SetParameterInfo" , that according to I saw in the MS site is an error that brings with MS OLE DB, something that nonuse, since I am using Oracle for OLE DB… _Me you will be able to give a hand with this? It pardons the language, I am using a tradutor one half (babelfish). Thank you very much!

I add in addition that the origin table has to the date 1300000 registries, which makes very ineffective when having update it like minimum 2 to 3 times per day. I only need that it adds the last registries that were added, that is to say, those that they have in a field of type date and hour a superior to which I have in table local (I recognize the new ones there)

Tnks!

I do not see this solution to be scalable at all in the big bad real world out there. I have to perform a Add-if-Update-else on a table with 25 tables and 250 columns in all (or say 50 with 500). And I do not see myself adding Lookups + Conditions using an advanced tool like SSIS - I am surprised an "ETL" solution does not have a simpler solution to this problem. I think that has led to so many folks asking for the solution. Call for an effecient drag-and-drop transform.

PingBack from http://sydnee.onlinepokervidsdirect.info/ssischecktableforrow.html

thank youu

thank youu

In early August I signed-up with Adcenter Analytics , a free service that can provide analytical information

In early August I signed-up with Adcenter Analytics , a free service that can provide information about

I see and grapple with this question a lot - how to incrementally merge a small source file into a large destination table, using upsert, where the data is all in nullable columns (read "from Excel"). I like and use this general solution, but the beastly part is that the tables always have MANY columns, not one or two. "Bob" asked exactly this question above. The hard/time consuming bit is composing the expression in the conditional split to compare a long list of nullable columns. I generally opt to write an expression that finds the identical rows, which can be discarded. The default output is then the rows with differences, which go to the OLEDB update command. Posting a sample of the expression next...

OK, so the expression to compare nullable columns requires combining the isnull() function with the basic column value to test both possibilities. For this, an "inline if" works. For example, if you have a string datatype, and null or empty string is equivalent, then you can do

(ISNULL(existingVal) ? "" : existingVal)

This will substitute the empty string for any nulls

Once the null is replaced, you can do a comparison

(ISNULL(existingVal) ? "" : existingVal) == (ISNULL(newVal) ? "" : newVal)

This will compare the results, which are now null-free

Tricky bit: the magic value you substitute for null (like the empty string above) has to be the same data type as the column. For example:

(ISNULL(existingDATE) ? (DT_DBDATE)"1900-01-01" : existingDATE) == (ISNULL(newDATE) ? (DT_DBDATE)"1900-01-01" : newDATE)

Same idea for numbers, etc.

PingBack from http://www.hilpers-esp.com/406995-actualizar-una-tabla-con-novedades