ssis journal

satya - 12/24/2019, 4:58:33 PM

Understand data flow: merge join

satya - 12/24/2019, 5:00:40 PM

Merge join

joins can be done easily in databases

both inner and outer joins

But in an ETL tool the sources may not be relational all the time.

Looks like such a merge join on disparate sources is allowed

Not sure how efficient this is

Atleast it is available

satya - 12/24/2019, 5:01:00 PM

Overall presentation seem to be good.

Overall presentation seem to be good.

satya - 12/24/2019, 5:10:35 PM

Additional notes

The inputs to the merge join must be sorted

Makes sense

Then sort by what? :)

There is no direct obvious way to indicate that the input source can be said to be sorted

You have to go to the advanced editor on the input source

Go to input and output properties

Set issorted to true

Go to output columns and pick the column to be sorted

set sortKeyPosition (the column number)

satya - 12/24/2019, 5:22:32 PM

what is union all task in SSIS?

what is union all task in SSIS?

Search for: what is union all task in SSIS?

satya - 12/24/2019, 5:25:33 PM

union all task docs

union all task docs

satya - 12/24/2019, 5:25:50 PM

union all task as a debugging task in ssis

union all task as a debugging task in ssis

Search for: union all task as a debugging task in ssis

satya - 12/24/2019, 5:27:51 PM

using union all as the last task in ssis data flow

using union all as the last task in ssis data flow

Search for: using union all as the last task in ssis data flow

satya - 12/24/2019, 5:30:21 PM

debugging with data viewer in ssis

debugging with data viewer in ssis

Search for: debugging with data viewer in ssis

satya - 12/24/2019, 5:31:54 PM

Docs on data viewer

Docs on data viewer

satya - 12/24/2019, 5:32:43 PM

Briefly

highlight the input arrow to a destination icon

right click

enable data viewer to see the data flowing into the destination

satya - 12/24/2019, 5:37:00 PM

More on sorting

Apparently the sort hint will not sort the source

it is upto the source to explicitly sort it

you can do this in db

or use an explicit sort component in the stage

satya - 12/24/2019, 5:45:08 PM

Look up option

You can use a look up option if the right table is a reference data and small in number

Then that will get loaded into memory for lookups

If the data is large then you want the merge join

For smaller data set on the right look up is faster

Nice to know that distinction

satya - 12/24/2019, 5:56:28 PM

Here is a playlist for learing SSIS from the above site: you tube playlist

Here is a playlist for learing SSIS from the above site: you tube playlist