discarding rows - best practice?

I have a need to filter out certain rows from my data stream. I cannot apply the filter against the source data using my DataReader component, due to some constraints in the source system. Therefore, I must filter the data out after it enters my datastream (trust me on this part).

I have created a data flow that uses the Conditional Split transformation to do this. I created one condition that matches the rows I want to discard. I then connected the Default output stream to my target table. I have simply left the "discard" output disconnected. This appear to do what I want.

My question is: is it OK to leave outputs disconnected in this fashion? It isn't really apparent when viewing the package that the conditional split is discarding rows. Is there a better way to handle this situation? For now I've just added an annotation to the package that describes what is happening.

Thanks for any help

You can use a rowcount transform to make it more 'evident'. That offers the extra benefit of getting the number of rows you discarded; which comes handy for auditing proposes.

There is a 3rd party adapter here that you may want to look as well(i have not used it):

http://www.sqlis.com/56.aspx

|||Yes, you have implemented that perfectly. The only thing I would add is to hook your "filtered" rows up to a Row Count transformation. This will do two things. One, it will let you see that records are going down that flow when debugging. Two it will let you capture the number of rows that went through there in a variable so that you can log it later if you wish.|||

As the other guys have said, you have done this in exactly the right way.

I disagree with Phil slightly though (sorry Phil ). I wouldn't bother connecting the dangling output to a rowcount component. This will simply make the data-flow do some unnecassary work. If you DO want to count the number of filtered rows then sure, use a rowcount component - although you can still determine the number of filtered out rows with introducing an additional component by substracting the number of output rows from the number of input rows. These two values are available by logging the OnPipelineRowsSent event.

-Jamie

|||

Jamie Thomson wrote:

If you DO want to count the number of filtered rows then sure, use a rowcount component - although you can still determine the number of filtered out rows with introducing an additional component by substracting the number of output rows from the number of input rows. These two values are available by logging the OnPipelineRowsSent event.

-Jamie

Good idea! How easy is it then to capture that and use it in auditing from a control flow task?|||

Phil Brammer wrote:

Jamie Thomson wrote:

If you DO want to count the number of filtered rows then sure, use a rowcount component - although you can still determine the number of filtered out rows with introducing an additional component by substracting the number of output rows from the number of input rows. These two values are available by logging the OnPipelineRowsSent event.

-Jamie

Good idea! How easy is it then to capture that and use it in auditing from a control flow task?

You can get hold of it in the eventhandler. You'd have to parse it out of the message but that's no biggie.

Not as easy as rowcount though. Options...always options!

-Jamie

|||It's amazing how deep you can get in SSIS... Many dark corners yet unexplored!

Disadvantages of installing another DLL on Sql Server Box

Hi All,

We are planning to install a Visual Basic component on one of our Sql Server Box which is also the Production Box.This Component will get started at some scheduled time,do some extract,generate an output XML file and then die.This process is a non-stop process and will be running everyday.Now my question here is that what are the disadvantages/problems of installing another Component on the sql server Box(under Winnt/System32)?Won't there be any Memory problem on the Sql Server,Performance Degradation,restart of the Server and all?

Thanks!You wont have any problems with memory etc. I do similar things all the time the best solution i have found is

1) Create an ActiveX DLL with all the stuff you need.
2) Host it under MTS or COM+ (which ever makes no difference)
3) Build an NT service that calls starts the DLL.

Using this you can stop start pause or can the dll at any time also its a lot safer being managed by MTS/COM+. If you want the code for the NT service email me. (it inlcuded full details for scheduling etc) We have had this in production for 2 years and it hasnt died once.

Hope it helps.

disable pagination

Hello
Is it possible to disable pagination? I use the reportviewer component but i
want to see all my data on one page and i don't use drilldowns?
If you can disable it how do you do that?
grtzAll you have to do is put: = â'Falseâ' in the table properties visibility
expression, and it will display continuously.
--
Tony
"David" wrote:
> Hello
> Is it possible to disable pagination? I use the reportviewer component but i
> want to see all my data on one page and i don't use drilldowns?
> If you can disable it how do you do that?
> grtz
>|||Does this work?
I tried it out on my lists which are having the same problem, and all it did
was make the report take about 20 times longer to render, and the preview is
a blank page, and when I exports as PDF, nothing actually changes...?
It sometimes even produces a GDI error when rendering...
How does setting the visibility with an expression affect the way
Pagination works anyway'
"Tony" wrote:
> All you have to do is put: = â'Falseâ' in the table properties visibility
> expression, and it will display continuously.
> --
> Tony
>
> "David" wrote:
> > Hello
> >
> > Is it possible to disable pagination? I use the reportviewer component but i
> > want to see all my data on one page and i don't use drilldowns?
> > If you can disable it how do you do that?
> >
> > grtz
> >
> >|||I tried it on one of my nested lists, and interestingly, the Preview did not
apply any page breaks!! But unfortunatly the exports to PDF still seemed to :(

Disable auto mapping by matching column names?

Hi,

If you use a component that has a column mapping tab (most do) then you know that the component will try and map input and output columns automatically by name.

Is there a way to disable this feature when writing your own custom components? There is already a manual way to do this: rightmouse in the mappings tab of a component and chosen "Map Items By Matching Names". I find it is less helpful to auto-map initially because the designer tends to not think about the mappings.

A way of turning of auto-mapping in existing components would be cool too!

Cheers,

Martin

There is no way to turn it off currently You can easily remove all the mappings, though. This is the first request I hear for turning it off. Usually, people asked for additional smart mapping options.

Thanks,

Bob

|||

Bob,

Thanks for the reply. What I find is designers get lazy and don't question the auto-mapping. Then mistakes happen which they find hard to troubleshoot because their brains weren't involved in the mapping - it's not a step they try to replay as they look for problems.

Because it happens automatically when you switch to the map pane, I wonder if it's the SSIS equivalent of Clippy announcing your Word doc "looks like you're writing a letter" and reformatting it automatically without you getting a chance to say no! :-)

Further clever mapping options would be great, but I'd suggest the designer needs to make the conscious step of making it happen.

All the best,

Martin

Disable auto mapping by matching column names?

Hi,

If you use a component that has a column mapping tab (most do) then you know that the component will try and map input and output columns automatically by name.

Is there a way to disable this feature when writing your own custom components? There is already a manual way to do this: rightmouse in the mappings tab of a component and chosen "Map Items By Matching Names". I find it is less helpful to auto-map initially because the designer tends to not think about the mappings.

A way of turning of auto-mapping in existing components would be cool too!

Cheers,

Martin

There is no way to turn it off currently You can easily remove all the mappings, though. This is the first request I hear for turning it off. Usually, people asked for additional smart mapping options.

Thanks,

Bob

|||

Bob,

Thanks for the reply. What I find is designers get lazy and don't question the auto-mapping. Then mistakes happen which they find hard to troubleshoot because their brains weren't involved in the mapping - it's not a step they try to replay as they look for problems.

Because it happens automatically when you switch to the map pane, I wonder if it's the SSIS equivalent of Clippy announcing your Word doc "looks like you're writing a letter" and reformatting it automatically without you getting a chance to say no! :-)

Further clever mapping options would be great, but I'd suggest the designer needs to make the conscious step of making it happen.

All the best,

Martin