Good websites for Custom Components

Hi,
I am pretty new to SSIS, so I really appreciate the help!
I have been building a few packages for the ETL in a mini data warehouse and
I have come across a few tasks that are very common. These include things
that, ensuring the business/natural key(s) are unique, or ensuring
many-to-one hierarchies are not violated. These kinds of tasks would be used
over and over again in almost any ETL project with different inputs.
Does anyone know of some good websites were users share their custom
components? I would like to avoid building this kind of thing if someone else
has done it already.
Thanks
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.codecomments.com ***
|||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" wrote:

> 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.codecomments.com ***
>

Good websites for Custom Components

Hi,
I am pretty new to SSIS, so I really appreciate the help!
I have been building a few packages for the ETL in a mini data warehouse and
I have come across a few tasks that are very common. These include things
that, ensuring the business/natural key(s) are unique, or ensuring
many-to-one hierarchies are not violated. These kinds of tasks would be used
over and over again in almost any ETL project with different inputs.
Does anyone know of some good websites were users share their custom
components? I would like to avoid building this kind of thing if someone els
e
has done it already.
ThanksThere 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.codecomments.com ***|||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" wrote:

> 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.codecomments.com ***
>|||> 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) t
o
> 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 int
o
> 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|||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

> 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.
>|||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 <5aaa4480100ca8c8d70bf92cbae8@.msnews.microsoft.com>,
allan@.no-spam.sqldts.com says...
> 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
>
>
>|||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
[vbcol=seagreen]
> 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.
> In article <5aaa4480100ca8c8d70bf92cbae8@.msnews.microsoft.com>,
> allan@.no-spam.sqldts.com says...
>|||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 <5aaa4480102118c8d7deb1db066c@.msnews.microsoft.com>,
allan@.no-spam.sqldts.com says...[vbcol=seagreen]
> 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 pu
ts
> 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
>|||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,
"Darren Gosbell" wrote:

> 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 <5aaa4480102118c8d7deb1db066c@.msnews.microsoft.com>,
> allan@.no-spam.sqldts.com says...
>

global code

Is there a place where I can put custom code so that all reports in a
solution can access the same code?
I now copy the code into every new report I createYou can have assemblies that are shared between reports. Search books on
line for custom code and it will tell you all about it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uTpyCPrCFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Is there a place where I can put custom code so that all reports in a
> solution can access the same code?
> I now copy the code into every new report I create
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
news:%2393IKdsCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You can have assemblies that are shared between reports. Search books on
> line for custom code and it will tell you all about it.
Thats not what I meant. The code for calling the assemblies will have to be
duplicated in every report one creates.|||Servé La wrote:
> Thats not what I meant. The code for calling the assemblies will have
> to be duplicated in every report one creates.
I did'nt understand your question. Each report is like a single programm.
If i need an assembly in two programms, i have to write
IMPORTS myassemblyclass
in BOTH programms
frank|||I haven't done this but I think it would work. It looks to me that
report.rdl in this directory:
C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\ProjectItems\ReportProject
Is what is used by the report wizard. My guess is if you change this then
all the new reports would have the code. Save a copy of it just in case. Let
me know if that works for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uS0vSqsCFHA.3324@.TK2MSFTNGP15.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
> news:%2393IKdsCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> > You can have assemblies that are shared between reports. Search books on
> > line for custom code and it will tell you all about it.
> Thats not what I meant. The code for calling the assemblies will have to
be
> duplicated in every report one creates.
>|||"Frank Matthiesen" <fm@.xax.de> schreef in bericht
news:36hi9eF50g2hqU1@.individual.net...
> Servé La wrote:
> > Thats not what I meant. The code for calling the assemblies will have
> > to be duplicated in every report one creates.
> I did'nt understand your question. Each report is like a single programm.
> If i need an assembly in two programms, i have to write
> IMPORTS myassemblyclass
> in BOTH programms
Suppose you have created a component where you have to set a few properties
and then calculate some value.
You create a function for this like the following:
Function Compute(name as String, x as Integer, y as Integer, z as Integer)
as Integer
Dim obj as new MyComponents.Component(name)
obj.X = x
obj.Y = y
obj.Z = z
Try
Compute = obj.Compute()
Catch e as OopsException
Compute = -1
End Try
End Function
You then want to use this function in a few reports. Right now I see no
other way then to put that code in every report and call it, while I
expected to put that function somewhere globally where all reports could
access it. In reality the code gets a lot more complex very fast.|||Could you not store the function as a 'stored procedure' in the database
then just call the stored procedure as you need it?
Dale
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%23whb6AtCFHA.1188@.tk2msftngp13.phx.gbl...
> "Frank Matthiesen" <fm@.xax.de> schreef in bericht
> news:36hi9eF50g2hqU1@.individual.net...
> > Servé La wrote:
> > > Thats not what I meant. The code for calling the assemblies will have
> > > to be duplicated in every report one creates.
> >
> > I did'nt understand your question. Each report is like a single
programm.
> > If i need an assembly in two programms, i have to write
> > IMPORTS myassemblyclass
> > in BOTH programms
> Suppose you have created a component where you have to set a few
properties
> and then calculate some value.
> You create a function for this like the following:
> Function Compute(name as String, x as Integer, y as Integer, z as Integer)
> as Integer
> Dim obj as new MyComponents.Component(name)
> obj.X = x
> obj.Y = y
> obj.Z = z
> Try
> Compute = obj.Compute()
> Catch e as OopsException
> Compute = -1
> End Try
> End Function
> You then want to use this function in a few reports. Right now I see no
> other way then to put that code in every report and call it, while I
> expected to put that function somewhere globally where all reports could
> access it. In reality the code gets a lot more complex very fast.
>|||You are not being consistent here. On the one hand you said you understood
about using custom assemblies. On the other hand you posted this. Your code
can go in a custom assembly once. Then all you have to do is reference that
assembly in your reports. You do not have to have that function defined in
each and every report.
As I suggested before, read up on custom code. You can do custom code two
ways, code behind report and in your own assembly. What you are talking
about here is code behind report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%23whb6AtCFHA.1188@.tk2msftngp13.phx.gbl...
> "Frank Matthiesen" <fm@.xax.de> schreef in bericht
> news:36hi9eF50g2hqU1@.individual.net...
> > Servé La wrote:
> > > Thats not what I meant. The code for calling the assemblies will have
> > > to be duplicated in every report one creates.
> >
> > I did'nt understand your question. Each report is like a single
programm.
> > If i need an assembly in two programms, i have to write
> > IMPORTS myassemblyclass
> > in BOTH programms
> Suppose you have created a component where you have to set a few
properties
> and then calculate some value.
> You create a function for this like the following:
> Function Compute(name as String, x as Integer, y as Integer, z as Integer)
> as Integer
> Dim obj as new MyComponents.Component(name)
> obj.X = x
> obj.Y = y
> obj.Z = z
> Try
> Compute = obj.Compute()
> Catch e as OopsException
> Compute = -1
> End Try
> End Function
> You then want to use this function in a few reports. Right now I see no
> other way then to put that code in every report and call it, while I
> expected to put that function somewhere globally where all reports could
> access it. In reality the code gets a lot more complex very fast.
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
news:uvhbbYtCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You are not being consistent here. On the one hand you said you understood
> about using custom assemblies. On the other hand you posted this. Your
code
> can go in a custom assembly once. Then all you have to do is reference
that
> assembly in your reports. You do not have to have that function defined in
> each and every report.
> As I suggested before, read up on custom code. You can do custom code two
> ways, code behind report and in your own assembly. What you are talking
> about here is code behind report.
I guess I meant code behind then. I have some cases where methods need to be
called from code behind in a certain order so it would be nice to have this
functionality in one single function defined somewhere globally.
I take it the answer is "no you cant do this" then :)|||You are still missing the point. You can have code behind and you can have
custom assemblies. You can definitely put your common functions in a custom
assembly that all your reports will use. You can have both code behind and
custom assemblies. The tricky part of custom assemblies is deployment of the
assembly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:%233S6lYODFHA.3256@.tk2msftngp13.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schreef in bericht
> news:uvhbbYtCFHA.2540@.TK2MSFTNGP09.phx.gbl...
> > You are not being consistent here. On the one hand you said you
understood
> > about using custom assemblies. On the other hand you posted this. Your
> code
> > can go in a custom assembly once. Then all you have to do is reference
> that
> > assembly in your reports. You do not have to have that function defined
in
> > each and every report.
> >
> > As I suggested before, read up on custom code. You can do custom code
two
> > ways, code behind report and in your own assembly. What you are talking
> > about here is code behind report.
> I guess I meant code behind then. I have some cases where methods need to
be
> called from code behind in a certain order so it would be nice to have
this
> functionality in one single function defined somewhere globally.
> I take it the answer is "no you cant do this" then :)
>|||"BruceLC" <brucelc@.newsgroup.nospam> schreef in bericht
news:OOGvMxRDFHA.1040@.TK2MSFTNGP09.phx.gbl...
> You are still missing the point. You can have code behind and you can have
> custom assemblies. You can definitely put your common functions in a
custom
> assembly that all your reports will use. You can have both code behind and
> custom assemblies. The tricky part of custom assemblies is deployment of
the
> assembly.
I am talking about calling a custom assembly from code behind! It's about
both!!
The methods in the custom assembly need to be called within a certain order
so this has to be done in code behind.|||Try putting your common code in a custom assembly and then placing it into
GAC
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Servé La" <blabla@.bestaatniet.nl> wrote in message
news:uTpyCPrCFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Is there a place where I can put custom code so that all reports in a
> solution can access the same code?
> I now copy the code into every new report I create
>