Re: Good websites for Custom Components

[Darren Gosbell]

There are a couple of sites that share some custom SSIS components.

http://www.sqlis.com/

http://www.sqlbi.eu/

However I feel I should add that for both the examples that you gave, I
normally enforce at the relational database level using unique
contraints and foreign key relationships.

--
Regards
Darren Gosbell [MCSD]
Blog: http://geekswithblogs.net/darrengosbell

*** Sent via Developersdex http://www.developersdex.com ***

[Eager to Learn]

Thanks Darren.

The two you mentioned are two of my favorites at the moment. I just want to
keep looking for more!

As for enforcing these using a relational database...It would be great if I
could do this, but I am going to be doing these kinds of checks on many
different inputs. It would take a LOT of time (both design and run time) to
put all of these into 3NF just to check for these flags. At the same time,
with the type 2 changes, I could only use the built ing relational
constraints if I filtered by "validAt" before importing the flat files into
the database.

Thank you very much for your help!

[Darren Gosbell]

> As for enforcing these using a relational database...It would be great if I
> could do this, but I am going to be doing these kinds of checks on many
> different inputs. It would take a LOT of time (both design and run time) to
> put all of these into 3NF just to check for these flags. At the same time,
> with the type 2 changes, I could only use the built ing relational
> constraints if I filtered by "validAt" before importing the flat files into
> the database.

If you are tracking type 2 changes you would generally build a composite
unique constraint over something like SourceSystemId, BusinessKey and
ValidFromDate.

The problem that you would have with doing these sort of checks in an
SSIS component is that it would probably need to be a synchronous
component, which would dramatically increase memory usage as the entire
input would need to be buffered in order to check uniqueness. Typically
SSIS likes to work in smaller batches, pushing each batch right through
the entire pipeline if it can in order to reuse its internal memory
buffers.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

[Allan Mitchell]

Hello Darren,

Do you mean an Async component?

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

[Darren Gosbell]

Hi Allan

I did mean synchronous. I was thinking that it would need to block
subsequent components from executing until it had processed all the
incoming rows so that it could check for uniqueness.

Although now you bring it up, I see that it could be written as an async
component. It would only need to maintain a buffer of all the business
keys that have passed through it and not necessarily buffer the entire
input, so the memory usage would not be as bad as I first thought.

So it might be a viable approach to check uniqueness in either an SSIS
component or in a relational store.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <5aaa4480100ca8...@msnews.microsoft.com>,
al...@no-spam.sqldts.com says...

[Allan Mitchell]

Hello Darren,

If we didn't want everything popping out the bottom of the component that
came in at the top we would have to be async. A sync component simply puts
everything it saw coming in, out, no change of buffer.

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

> Hi Allan

>
> I did mean synchronous. I was thinking that it would need to block
> subsequent components from executing until it had processed all the
> incoming rows so that it could check for uniqueness.
>
> Although now you bring it up, I see that it could be written as an
> async component. It would only need to maintain a buffer of all the
> business keys that have passed through it and not necessarily buffer
> the entire input, so the memory usage would not be as bad as I first
> thought.
>
> So it might be a viable approach to check uniqueness in either an SSIS
> component or in a relational store.
>

[Darren Gosbell]

Thanks Allan, sounds like I did have my component terminology round the
wrong way. :(

But you can see where I was heading. Orginally I was thinking that it
would need to be a blocking component that would need to examine the
entire input before allowing subsequent component to execute. But I now
think it could be done in a much lighter fashion, only maintaining an
internal list of business keys and generating an error if it hits a
duplicate.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <5aaa4480102118...@msnews.microsoft.com>,

[Eager to Learn]

Thank you both for your comments. I did decide to build it in T-SQL for the
hierarchy check. Essentially I have a stored Procedure that uses dynamic SQL
to look for duplicates as at particular date. SSIS just loops through the
dates. For each insert, I just combine this stored procedure into the same
transaction (in SSIS) as the insert. this way, if there is ever a hierarchy
violation, the insert will fail. It will generate an SSIS error that I can
catch and handle.

But my original question still stands. If anyone knows of any other websites
for good custom components, please post here.

Thanks again,