Multiple Fuzzy Lookup

Is it possbile to have multiple fuzzy lookup within a data flow?

I need to have at least 3 fuzzy lookup in a data flow. Here're the conditions that I try to find match: 1=Zip&City, 2=Zip&State, 3=City&State. I've the first fuzzy lookup working fine. After that, I've a conditional split to get any unmatch, then use another fuzzy lookup for a second condition...at this point, I get the error saying "The package contains two objects with duplicate name of output column _Similarity..." I do not need to get the _Similarity and _Confidence, so is there a way to exclude them from returning in the output?

Any comments?

Thanks in advance.

Ash,

Using the Advanced Editor for the Fuzzy Lookup task, you can change the name of the _Similarity and _Confidence fields. This would not exclude them from the data flow, but would solve the duplicate name issue.

Frank

|||

Frank,

I do not find any options to re-name the _Similarity and _Confidence fields from the Advanced tab of the Fuzzy Lookup Transformation Editor. Can you please be more specific?

Thank you for the comment.

|||

On the 'Input and Output properties' tab -'Fuzzy Lookup Output'-'Output Columns'

Use the 'Name' property to rename the fields.

Frank

|||Thanks Frank. That's exactly what I was looking for.

Multiple execution paths in SSIS control flow

Hello all,

Is there documentation somewhere about multiple execution paths in SSIS control flow? I didn't find documentation anywhere. I have a situation where I have two tasks that take considerable time, but could be executed in parallel (to speed up things) and I was wondering whether SSIS supports parallelism.

To illustrate the issues in simultaneous execution, I created a test SSIS package. In the package, I have five tasks, let's call them T1, T2, T3, T4 and T5. The taks are connected with "green arrows" like this:

T1->T2

T1->T3

T2->T4

T3->T4

T5 is not connected. The tasks can be e.g. Send Mail tasks, that's not relevant to this issue. I put a breakpoint in each task and execute the package.

When I execute package, T1 and T5 become active, i.e. the arrow that displays where the package execution currently is, is in two tasks simultaneously. Now F10 (step over) doesn't seem to work "Unable to step. Not implemented". If I press F5 nothing happens. After I press F5 for a second time tasks T1 and T5 and executed. Why don't they execute with the first pressing of F5? I would additionally like to know whether these two tasks are executed in parallel or sequentially, i.e. in the same thread or in two threads? Is there documentation of this?

The execution stops at T2&T3. Again, pressing F5 doesn't do anything, but the second time I press F5 T2 and T3 are executed.

T1 and T5 will execute in parallel, as will T2 and T3 since they both only depend on T1.

Using Execute SQL tasks with WAITFOR DELAY '00:00:05' commands (varying the timeout of each one) is helpful for testing parallelism and precedence.

See the Package.MaxConcurrentExecutables property documentation for how to control the degree of parallelism.

I don't know about your breakpoint question. I don't use them.
|||I think Jay is partially right. SSIS will *try* to run those tasks in parallel. I believe MaxConcurrentExecutables is a hint. There are no guarantees that the tasks will truly run in parallel. MaxConcurrentExecutables will ensure that three tasks can never run in parallel if it is set to 2, for instance. I don't know that setting MaxConcurrentExecutables to 3 will force three available tasks to run in parallel though. (again, for instance)|||

Phil Brammer wrote:

I think Jay is partially right. SSIS will *try* to run those tasks in parallel. I believe MaxConcurrentExecutables is a hint. There are no guarantees that the tasks will truly run in parallel. MaxConcurrentExecutables will ensure that three tasks can never run in parallel if it is set to 2, for instance. I don't know that setting MaxConcurrentExecutables to 3 will force three available tasks to run in parallel though. (again, for instance)

I think that is true for the EngineThreads in a Data Flow, but I've never seen a control flow decide to execute fewer tasks in parallel than allowed with MaxConcurrentExecutables.

http://sqljunkies.com/WebLog/ashvinis/archive/2005/03/29/9825.aspx
|||

Thank you all for your answers.

As a conclusion

SSIS packages support concurrent execution in Control Flow. The exact behaviour of concurrent execution is poorly (if not at all) documented in MSDN, for example if you have a look at the Control Flow documentation there's no mention of such feature (http://msdn2.microsoft.com/en-us/library/ms140234.aspx) The number of concurrent executing tasks is controlled by SSIS package property MaxConcurrentExecutables.

Multiple data flow tasks in one SSIS package

What are the advantages and disadvantages of having multiple data flow tasks in one SSIS package?

Is this a good idea at all considering the workflow may be similar now but may change in the future? Should it be left as one data flow per package?

It's up to you and how you like to manage your work. I like to break my packages into logical units -- each containing as many data flows as required. You'll want to balance this with maintainability though -- the less in a package, the easier to maintain.

Multi-parameters issue in data flow task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

The "Parse Query" never works when you have ? in your SQL command. Just ignore the error.