Putting reusable code into SSIS components....

If I have standard code for logging, event handling, error handling, etc...How to do you get the standard SSIS components to include this code...

I would hate to have to add this standard code, each time I add a component to my package...especialy if I have to build hundreds of them...

Thx in advance...

RC

You have a few options. The 2 worth considering are:

1) Write a custom log provider
2) Build your custom logging/error handling into eventhandlers.

2) is, I would say, easier. This demo may help: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
Oh, and don't forget about the ability to create template packages. Your template can contain all the logging/error handling functionality that you need which means you only have to define it once. See here for more on templates: http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

Trust me, you don't have to define all this stuff every time you add a component/task.

-Jamie|||Note that BOL has a topic "Creating a Custom Log Provider in Integration Services" that has enough code to get you going. However at RTM there was not a separate standalone custom log provider code sample.

-Doug
|||What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.
|||

Perry_Rapp wrote:

What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.

Reusability will be addressed in a big way in the next version I am quite sure of that!

If you are logging to a database table then you could write a sproc to do the logging and call that from wherever you need to.

What I do is have a "master" package in which all my logging is configured. Then from that I call the packages that actually do the work. I pass in an ID and the "master" package looks up in a metadata DB which package(s) it needs to call for that ID.

Also be aware that you can build template packages that contain all your logging. http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

-Jamie

Putting reusable code into SSIS components....

If I have standard code for logging, event handling, error handling, etc...How to do you get the standard SSIS components to include this code...

I would hate to have to add this standard code, each time I add a component to my package...especialy if I have to build hundreds of them...

Thx in advance...

RC

You have a few options. The 2 worth considering are:

1) Write a custom log provider
2) Build your custom logging/error handling into eventhandlers.

2) is, I would say, easier. This demo may help: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
Oh, and don't forget about the ability to create template packages. Your template can contain all the logging/error handling functionality that you need which means you only have to define it once. See here for more on templates: http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

Trust me, you don't have to define all this stuff every time you add a component/task.

-Jamie|||Note that BOL has a topic "Creating a Custom Log Provider in Integration Services" that has enough code to get you going. However at RTM there was not a separate standalone custom log provider code sample.

-Doug
|||What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.|||

Perry_Rapp wrote:

What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.

Reusability will be addressed in a big way in the next version I am quite sure of that!

If you are logging to a database table then you could write a sproc to do the logging and call that from wherever you need to.

What I do is have a "master" package in which all my logging is configured. Then from that I call the packages that actually do the work. I pass in an ID and the "master" package looks up in a metadata DB which package(s) it needs to call for that ID.

Also be aware that you can build template packages that contain all your logging. http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

-Jamie

Putting Report Services inside DotNetNuke 3.1+

Hi All,
we are building a portal that will include RS inside DNN3.1+. I see we
can just include the address in the IFRAM control but we then need
something to control parameters etc...we read the article below to find
this out...
What we are looking for is to have good control over things like
parameter selection and placement of paramters on the page...we have
cascading dependent prompts and we want the related prompts on the same
line etc...we are told we cannot do this in just the standard web
interface....
So we are looking around for people who have done DNN 3.1+ and RS..
We also posted to DNN forums and have had one hopeful answer so
far..but we thought we would ask around!!
Thanks in Advance
Peter Nolan
http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/04/08/sqlserverreportingservices/default.aspxHave you looked at the modules at www.snowcovered.com ? I also found this
one..
http://dnndev.icpconline.com/DotNetNuke/Default.aspx?tabid=54
"Peter Nolan" wrote:
> Hi All,
> we are building a portal that will include RS inside DNN3.1+. I see we
> can just include the address in the IFRAM control but we then need
> something to control parameters etc...we read the article below to find
> this out...
> What we are looking for is to have good control over things like
> parameter selection and placement of paramters on the page...we have
> cascading dependent prompts and we want the related prompts on the same
> line etc...we are told we cannot do this in just the standard web
> interface....
> So we are looking around for people who have done DNN 3.1+ and RS..
> We also posted to DNN forums and have had one hopeful answer so
> far..but we thought we would ask around!!
> Thanks in Advance
> Peter Nolan
> http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/04/08/sqlserverreportingservices/default.aspx
>|||Hi Shoeman,
I searched snowcovered for 'report services' but didn't find
anything....should I?
Also, thanks for the pointer to the other site...I've registered and
I'll download it asap...looks like it might be something like what I
am looking for....
Though I am told that it is possible to integrate better charts from
Dundas even in the 2000 release....and it does not look like this site
does that yet....but we are learning... :-)
Thanks again
Peter
www.peternolan.com|||Hi,
look at the WebHarpoon module:
http://www.nukesmith.com/
Walter Haas
Peter Nolan wrote:
> Hi All,
> we are building a portal that will include RS inside DNN3.1+. I see we
> can just include the address in the IFRAM control but we then need
> something to control parameters etc...we read the article below to find
> this out...
> What we are looking for is to have good control over things like
> parameter selection and placement of paramters on the page...we have
> cascading dependent prompts and we want the related prompts on the same
> line etc...we are told we cannot do this in just the standard web
> interface....
> So we are looking around for people who have done DNN 3.1+ and RS..
> We also posted to DNN forums and have had one hopeful answer so
> far..but we thought we would ask around!!
> Thanks in Advance
> Peter Nolan
> http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/04/08/sqlserverreportingservices/default.aspx|||Hi Walter,
thanks for the pointer to webharpoon...I did see it earlier but I am
not sure how it might help me so much with report services....from my
reading of the functionality it will help me get content from report
services but it looks like it will just pull back RS reports rather
like an iframe.....of course, being a newbie in this area I might be
misunderstanding....
Is it able to do more with RS than just bring back the report content
similar to an iframe?
Thanks
Peter

Push Subscription - will it overwrite existing data first?

If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison

Push referenced records

Hi,
I am facing the following problem. Please help me to solve it.
My question is: How to force the replication engine to include not only
the updated records but the relevant records as well?
I am using merge replication (SQL2kSP3 with SQL2kCE) with row filtering.
I many cases i have to replicate tables with "many to many"
relationship, where i operate the connection table (BOOKPERSON in the
example bellow). All the rows are filtered by PERSON_ID through out the
database.
Eg.
PERSON records to send:
select * from person where person_id = HOST_NAME()
BOOK records to send:
select * from book inner join bookperson on book.book_id =
bookperson.book_id and bookperson.person_id = HOST_NAME()
BOOKPERSON records to send:
select * from bookperson where person_id = HOST_NAME()
My problem is the following. The newly initialized subsciption
replicates fine, but in case of inserting 1 row to the table BOOKPERSON
(which means associating a book with a person) causes the replication to
fail, because the it replicates only the inserted BOOKPERSON record, and
does not replicate the the relevant BOOK record that is referenced now
by the BOOKPERSON record.
So my question is: How to force the replication engine to include not
only the updated records but the relevant records as well?
Thanks in advance
Pierre
The relevant schema is as follows:
CREATE TABLE Book (
BOOK_ID int not null,
BOOK_TITLE varchar(30) not null,
constraint PK_BOOK primary key clustered (BOOK_ID)
)
CREATE TABLE Person (
PERSON_ID int not null,
PERSON_NAME varchar(30) not null,
constraint PK_PERSON primary key clustered (PERSON_ID)
)
CREATE TABLE BookPerson (
BOOK_ID int not null,
PERSON_ID int not null,
constraint PK_BOOKPERSON primary key clustered (BOOK_ID, PERSON_ID)
)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_BOOKPERSON foreign key (BOOK_ID)
references BOOK (BOOK_ID)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_PERSONBOOK foreign key (PERSON_ID)
references PERSON (PERSON_ID)
Pierre,
presumably the personid value for a bookperson and book are not really
referring to the same entity (a person can be related to an individual book
as eg an author and as a reader), otherwise the related book would already
be replicated? In that case I'd say that the filter clause on the Books
article is incorrect. The simplest way would be to drop this filter and
replicate all the books.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think I forgot to tell that we have thousands of records in the "BOOK"
table, and our subscribers have very limited storage capacity (~1000
thousand book records).
The relation between the two tables (person, book) is not like "authors
of a book" but "books 'thouched' by person", so there is a real M:N
relationship.
Meanwhile found the solution to my problem. I think I have to create
triggers (for insert, delete and update) on the table BookPerson which
will use a stored procedure (sp_mergedummyupdate) to force the
replication engine to put the relevant book records into the publication.
Am I right?
regards
Pierre
|||Pierre,
this sounds correct. Actually I misread your original post and didn't notice
the inner join, so was thinking that the PersonID was a FK directly to the
books table as an author. You'll need to make sure that the FK relationship
is 'Not For Replication', as you can't guarantee the replication order (in
SQL 2005 the default is PK then FK records but not in SQL 2000).
Regards,
Paul Ibison
|||Thank you for the tip, i`ll try it.
pierre
|||Paul,
we have tried to script the references with the "NOT FOR REPLICATION" option
and it caused SQL Server CE to completly break down at replication. After a
quick search on MSDN we found a reported bug saying that SQL Server CE
doesn't support the option mentioned above. (to be specfic SQL Server CE
supports NOT FOR REPLICATION, but there is a bug so we can't use it.)
If I'm not mistaken we have to drop all our references in these
circustances. Am I right? Is there any way to control the order of bulk
inserts at replication. (after PDA downloaded the appropriate snapshot, it
starts to bulk insert & update the rows)
regards
Pierre
"Paul Ibison" wrote:

> Pierre,
> this sounds correct. Actually I misread your original post and didn't notice
> the inner join, so was thinking that the PersonID was a FK directly to the
> books table as an author. You'll need to make sure that the FK relationship
> is 'Not For Replication', as you can't guarantee the replication order (in
> SQL 2005 the default is PK then FK records but not in SQL 2000).
> Regards,
> Paul Ibison
>
>
|||Pierre,
can you post up your reference for this CE bug (I'll put it on my website).
I haven't seen this reference, but if that is the case, you could increase
the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters (to the max of 2000) to
avoid splitting parent and child changes across generation batches.
See http://support.microsoft.com/default...b;EN-US;308266 for more
info.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||BUG: NOT FOR REPLICATION Clause Causes SQL Server CE Replication to Fail
http://support.microsoft.com/default...b;en-us;300597
...however SQL Server CE Books Online says that "NOT FOR REPLICATION" is not
supported in case of foreign key constriants...
anyway, thanks for the tip, i'll try that.
pierre
sql

pull from two tables, but include everyone

I want to pull ALL users from Table1 and include information that personmay have in Table2. As of now, it is only including users that have something in Table2. How do I include everyone?
Thankschange your JOIN to a LEFT OUTER JOIN

publication include new tables created automatically?

Hi All,
I have a merge replication on a SQL 2000 database to a remote site.
I have selected "publish all" in publication properties. Now if I a new
table is created, does it automatically runs the snapshot agent and include
that table in to the table schema?
Thanks in advance for the answer... :-)
Maani...
Using sp_addmergearticle followed by running the snapshot agent and merge
agents should be enough.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .