Showing posts with label flow. Show all posts
Showing posts with label flow. Show all posts

Sunday, March 11, 2012

Conditional Split Component - annotation issues

Greetings SSIS friends,

When I configured my conditional split component (directing the data flow in 2 directions) The annotation does not align properly with the lines. Is there anyway to shift the text as to make more presentable?

Thanks for your help in advance.

No, you can't.

You can turn off the annotation, and then add your own, though, by right-clicking on the background and selecting "Add Annotation."|||

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

Double click on the flow line and then set the PathAnnotation (found in the General section under Design) to "Never."|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

If you think there's an issue here then please raise it at Connect (http://connect.microsoft.com/sqlserver/feedback)

-Jamie

Thursday, March 8, 2012

Conditional Lookup & Returning Undefined Values on Error

Hi,

I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables.

I do lookuping like this

first a lookup that lookup my table with connected input column parameter

second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table

third a union that union lookup and derived column

i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step.

thanks

This link discusses two different ways to address this problem: http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

1. Lookup is configured to redirect rows that have no match in the reference table to a separate output (error output), then use a derived column to specify a default value, and finally merge both the lookup success output and the output of the derived column using a union all transform.

2. Lookup is configured to ignore lookup failures and pass the row out with null values for reference data. A derived column downstream of the lookup then checks for null reference data using 'ISNULL' and replaces the value with a default value.

The first way is what you're doing now; the second way is what you probably want to do.

With that said, making upstream changes in a data flow is almost always going to require tweaking to downstream tasks. That's just the nature of how SSIS relies on metadata...

Conditional Join on Data Flow?

Hi,

Can we make conditional joins on the data flow?

Imagine i want to join 2 tables based on a value and an interval... Imagine i have a positioning number in one table and in the other i have a price, from_position, to_position and i want to join the to tables like position >= from_position and position <= to_position

Can we do this in SSIS?

Best Regards,

You can't do a join like that using a single component. To achieve this you might try doing a full join and then using a conditional split or a custom script to drop the rows that don't meet the desired join criteria.

Saturday, February 25, 2012

Conditional Execution in the Control Flow via Script Task

Greetings.

I'm trying to conditionally execute a dataflow based on the presence of a data file. If the data file isn't present, I'd like to execute gracefully without error.

Logic is as follows:

If FileExists Then
execute dataflow
Else
exit w/o error
End If

I've got the code ready to go, but I'm not sure how to do this conditional branch logic. Right now, the code calls the Dts.Results.Success / Failure. The problem, however, is Failure is exactly that... which doesn't result in the graceful exit I'm looking for.

Anyone have any ideas?

Thanks in advance.

Here is how I would do that:

Create a script task in your control flow to check if the file exists and write that result into a SSIS variable, let's say FileExists=1 -->exists; FileExtis=0 -->Does not exist. Then create a precedence constraint from the script task to the data flow. Then Edit the precedence constraint to use evaluation operation 'Expression and constraint'; Value 'success' and write the expression like @.[User::FileExists]==1.

This way the dataflow will be executed only if the sript task succed and the value of the variable FileExists is equal to one.

Rafael Salas

|||This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.|||

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

|||

Rafael Salas wrote:

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

Indeed.|||

Good Idea. I followed your suggestion; for those interested in voting on that suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=231838

Rafael Salas

|||For those curious.. I documented the steps for future peoples that landed on this thread.

dichotic.wordpress.com

Conditional Control Flow element

Hi:

A branching element is critical to any process flow. Currently, as far as I know, there;s only a Conditional Split Data Flow Element. There is no direct way I can branch out in a control flow.

In some cases, I could branch by using a conditional operator ?: to either create a dynamic sql string for each patch, or a package name for a Package Execution task and so on.

This approach is not always good enough.

Are there others out there who want a Conditional Control Flow element?

TIA
Kar

Can't you use the normal constraints & change from "Constraint" to "Expression & Constraint" in the constraint properties, and then using variables for your branching, put your expression in the constraint? I've done this before to branch out the control flow.|||

karfast wrote:

Are there others out there who want a Conditional Control Flow element?

TIA
Kar

No, because expression-based constraints already exist in the control flow, which allow for what you desire.

Tuesday, February 14, 2012

Concurrency issues in stored procedures

I have a question regarding concurrency in stored procedures. I have a stored procedure with the following flow of control:

-- A couple of select statements here.

-- (placeholder - see below)

-- A couple of updates based on the data obtained
-- from the above select statements.

I'm concerned that while the stored procedure is running, if right when it gets to the spot in the code marked 'placeholder', the scheduling mechanism of SQL Server switches to another thread that contains requests from another client which makes changesto the data in the previous select statements. This will make my updates bad, since they rely on data (from the select statements)that has been modified.

I guess I'm curious what I would need to do to lock the records in the select statements until after my update statements are completed. Thanks in advance for any advice!

EverettBump