Welcome to blogs.conchango.com

Great work!

The performance of the pipeline can probably be further improved by modifying the lookup queries in the lookup component so that only the needed columns are cached in memory. In the case of the Dim_Patient table this lookup query would be:

SELECT Patient_id, SKPatient_Id
FROM Dim_Patient

45 minutes from 2 days isn't an improvement, it's a miracle :)

Anyway, this case study was interesting, but i'd like to know more about using memory restriction in the advanced tab. When am I supposed to use it and in which ways can it improve lookups?

Thanx

You might consider using a SQL update statement, if speed is your main concern.

Firstly, I assume the looked-up fields have an index set. So in the example there should be an index on a field like DIM_PAT.Patient_Id. This will make lookups lightning fast.

Secondly, the single UPDATE-statement might be something like:

UPDATE STG_FACT_TRANSACTION
SET
FK_SKPatient_Id = ISNULL(pa.SKPatient_Id,-1),
FK_SKBillDate_Id = ISNULL(bd.SKBillDate_Id,-1),
FK_SKEntryDate_Id = ISNULL(ed.SKEntryDate_Id,-1)
FROM
STG_FACT_TRANSACTION s
LEFT OUTER JOIN DIM_PAT pa ON s.PATIENT_ID = pa.PATIENT_ID
LEFT OUTER JOIN DIM_DATE bd ON s.BILLDATE = bd.DATE
LEFT OUTER JOIN DIM_DATE ed ON s.ENTRYDATE = eb.DATE

Add all the other lookups in a simular fashion. (I made up the fact that the time dimension is called DIM_DATE and has a lookup-field called DATE)

You can add a WHERE-clause to optimize things even further:

WHERE
ISNULL(s.FK_SKPatient_Id,-1) = -1 OR
ISNULL(s.FK_SKBillDate_Id,-1) = - 1 OR
ISNULL(s.FK_SKEntryDate_ID,-1) = -1

What you lose, amongst others, is detailed logging functionality. Something the built-in task would support.

Hopefully this has some value in your discussion.

Greetings,

Frans van Bree

Correction:

UPDATE STG_FACT_TRANSACTION
SET
FK_SKPatient_Id = ISNULL(pa.SKPatient_Id,-1),
FK_SKBillDate_Id = ISNULL(bd.SKDate_Id,-1),
FK_SKEntryDate_Id = ISNULL(ed.SKDate_Id,-1)
FROM
STG_FACT_TRANSACTION s
LEFT OUTER JOIN DIM_PAT pa ON s.PATIENT_ID = pa.PATIENT_ID
LEFT OUTER JOIN DIM_DATE bd ON s.BILLDATE = bd.DATE
LEFT OUTER JOIN DIM_DATE ed ON s.ENTRYDATE = eb.DATE

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices.

One reason why it was also taking so long is because the sort component is much slower than if you let database engine do the sort for you.  If you have data from an oledb source then pass that to the sort component, that's slower than if your oledb source is already sorted.  So instead of selecting a table as your oledb sourece, use a sql query with an order by.

Hey guys,

i'm a DTS user, moving toward SSIS. and i have a question regards to mapping source and destination data.

In DTS, we were able to write up a script during the mapping process. so i could of say something like,

if field A is blank, then set the value to NUll

or

if field B is blank, then set the value to 0

Can we do something like that in SSIS? i can't find anywhere to write up a script in the mapping process

Shimmy

I want to know is the lookup component works the same as a LEFT JOIN or more like a INNER JOIN?

I've take out the data with a OLE DB Source,then I use another OLE DB component to populate the data from the iother DB and finally I uses a MERGE JOIn component. All of this to do a LEFT JOIN query.

It would be better if I use LOOK Up component?

To HR said

Lookup doesn’t work like INNER JOIN or LEFT OUTER JOIN. In INNER JOIN or LEFT OUTER JOIN records may multiply like rabbits ;)

I belived that LOOKUP component took all the records in table A and "look" on the table B for the fields where you conditions match, for example:

Table A contains IDemployee and Name

Table B contains IDempoloyee and DeparmentName

I belived that if you use a OLE DB component to populate the records from table A. Then use a LOOK UP component to find the DeparmentName it would OutPut all the records for the OLE DB component (table A) with the daparmet name. Returning deparmentName as NULL whe condition doesn´t match. (like a Left Outer Join).

If it isn't that the way it works, could you ilustrate me how if its done?

Thanks in advance!

P.S.: I congratulate you guys for this blog, it has helped me a lot. Thanks again!

HR, I have the same exact question.  

What I would like to do is use the lookup component to basically perform an update on a datasource. If the ID exists in the lookup column, update the table. If the id doesn't exist in the lookup column, move on.

It doesn't appear to work like this.  Does anyone have a good example?

What if I had 45 million rows in the fact table and 2 million rows in one of the dim tables. Whats the option for me? Do I go with Lookup , Merge or just the SQL statement. I cannot afford more than 45mins to load my fact table.

Thanks for the info. This is what I would like to do. The process runs for every 2 hours and loads the Dimension and Fact tables. You can call it as incremental loads. So while trying to load the fact table using the Lookup, the lookup is caching the fact table rows before the data flow task gets executed which is taking forever. I did wait for more than an hour and lost my patience and stopped the process execution. I am not even sure if SSIS can handle heavy loads. I am not even thinking of using MergeJoin. The only other option I have is to build the SQL query on a fly and passing it to the Ole DB source transformation.

Thanks

Ashwin

I have around 30 dimensions and i need to built a fact table taking the keys from the dimesnion tables. Could someone explain to me how to built this?

Thanks in advance

Very nice post. I have now learned some thing I can take to my next job :)

How does merge join compare with lookup if the data is pre-sorted ?

One disadvantage I saw with lookup was that lookup doesn't have expressions

I can't change the reference table name dynamically

Whereas in merge join, I can have 2 inputs ( coming from OLE DB source )

I can set the table name dynamically in the OLE DB Source

Good job... and if your try the new SQL 2008, using the cache component, you will be reducing your package to 5 minutes!! :-)

Cheers!!!!

Hi guys

I am new on SSIS, and I am trying to follow above sample to update 1 column on one table.

But when I run the SSIS, it dispaly an error saying that some other columns does not allow nulls.

Seems my SSIS is trying to Insert new records insted of update the existing...

I do not know what I am missing to use the update insted of the insert... any light will be really appreciate it

Thanks

Thanks Jamie

Nope, I was using a SQL Destination and OLEDB Destination, I tried the OLEDB Command and it worked :)

But I notice it is slow to make the updates, seems it is updating record by record...

There is not other option to update tables... the situation is that I have table A on one Server and Table B on other server, then I am trying to update Table A with values of Table B... but I was looking for options insted of use LinkedServers

Thanks

Hi,

I want to insert data in fact table in such a way that only new data is inserted.I have source in which the data is increasing day by day.for example In fact table earlier if there 100 rows I want to insert 10 more rows.One method I know is delete fact table when we run the query and reinsert the data but I think it is not gud solution.So please suggest me how can I insert data if the row is found in destination fact table then it will update it and if no it will insert it.

Thanks Jamie,it is working but I have one confusion as we dnt have primary key in fact table so how I will map from available  input column to available output column.I have map all the foreign keys.

Thanks a lot..

Hi, I have more query.Error output of lookup is actual destination.I want the output in actual output.How can I do this.

Hi,

I want to insert data in fact table in such a way that only new data is inserted.I have source in which the data is increasing day by day.for example In fact table earlier if there 100 rows I want to insert 10 more rows.As in ur blog I have taken lookup tranformations and redirect rows to error output all the unmatched column will go in error output and this will insert new data in the destination table .  Instead of using error output I want unmatched column in main output.Can I do this.It is just reverse of lookup.Unmatched column in main output and matched column in error output.

The whole answer is not visible in this blog.Some part is hidden.

Hi,

If you find the solution please reply me.

Hi,

Some Light green cover your answer.But I find the way just copy it and paste it nodepad.We will see complete answer.

Solution of lookup ummatched row in loopkup output not in error output

Hi,

Can You me one thing suppose I got new concept then I jsut want to know how to write article about this in this blog site

ok..Thanks a lot..

Hi ,

I am currently using Lookup in SQL 2000 where I have two tables ..

one table contains ID's and other contains ID and its corresponding description.

Both these tables are on Open GL Progress database and I am connecting to this using ODBC drivers to fetch data .

Now these two tables are in two different databases and both of these databases are running on different port numbers (which is possible in progress) ,I can not directly join these two tables in a single sql statement because of progress limitations and hence I have created a lookup in DTS where I search the corresponding description for a Id.

Now I am trying to build this same thing in SSIS and there I have found that I can create lookups only on OLE DB connection and not on ODBC connections.And hence I can not do a remote lookup.

Since this desription table is huge (around 5 million records) I can not afford to do a daily refresh of this dimension table.

I am badly stuck at this point .. Can someone help me out ...

Thanks in advance.

PingBack from http://blog.todmeansfox.com/2008/06/24/etl-subsystem-14-surrogate-key-manager/

Hi Jamie,

Came across a situation where I had 2 tables on 2 different servers as follows

Table1 on Server1

-------------------

IDStartRange bigint not null

IDFinishRange  bigint not null

Description nvarchar(255)

Table2 on Server2

-----------------

ID bigint not null

Description nvarchar(255)

I am trying to update "Description" column on Table2 from Table1, based on the condition "Table2.ID >= Table1.IDStartRange and Table2.ID <= Table1.IDFinishRange".

As you can see i cannot use "Merge Join" as it uses equi-joins.

It will be too expensive to fill all the possible IDs in Table1 (or in Query temporarily) between IDStartRange and IDFinishRange as they span across millions.

Is there anyway we can make use of any of built-in "Data flow transformations"?

Cheers

Raj Chidipudi