Configure FTP in Foreach loop

I need to put a FTP task inside a Foreach Loop Container to upload data files to many different FTP servers. The container holds a variable with object data type that includes necessory FTP info (FTP server address, login, password and remote path). How do I configure FTP task so that I can pass the variables to it?

Thanks,

Jia

I get a little work around with command line FTP -

Inside the loop, I create a batch file with the variables passed from the container. Then use xp_cmsshell to call the file. It works although looks a little cumbersome. I still hope there is a better way to do it.

Conditionally execute a task

Hallo

Is there a way to conditionally execute a task?

I got a task “Mail Send“ and I would like to execute it, just if the variable X (my message source) has a value.

I try in expressions “Disable” = len(@.[Benutzer::Msg] ) > 5 ? false : true, but it does not work.

Link with an an arrow the task before send mail task double click the green line and (you have Precedence Constraint Editor window) choose at Evaluation Operation the value Expression and type

@.x is not null or other expression

so if the expression is true SSIS will go and run sendmail task

|||

Following ggciubuc post...

You can add an additional row to a scrit task in order to fail the package if the expression is the oposite of the first expression...

helped?

Regards

Conditional SQL Triggers

Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.

I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.

For instance, say I have the following table:
CREATE TABLE source
(
ID tinyint NOT NULL,
contacttype tinyint NOT NULL,
)

If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.

How can this be performed?You can attach a trigger to a table to respond to inserts, deletes or updates. I think you can narrow it down to a specific column (if columns_updated). Anything else goes in the trigger itself. See BOL for TRIGGER.|||create trigger blah on update
as
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype FROM inserted = 2
BEGIN
...insert code here...
END
END|||Understand, the TRIGGER will always fire. As shown, you want to control the logic inside the trigger.

What action do you need to take?

Just make sure the affected rows use the id of that row as the reason to modify that data|||Thanks for all the replies!

I tried mitchell007's code, and it helped a lot. I am now able to run SQL statements if a certain column is updated.
But I had a problem with the line: IF SELECT contacttype FROM inserted = 2
This results in a parse error. "Incorrect syntax near the keyword SELECT" and "Incorrect syntax near '='."

Here is my trigger code:
CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype from inserted = 2
BEGIN
print 'contacttype modified!'
END
END

Brett, my ultimate goal is to detect when a row in a contact table changes the value of the 'contacttype' column. If a row is created with contacttype = 2, or if an existing row is updated to that value, I want to create a new row in a different database.

Also, how can I know which row was altered? When all the trigger filters pass, I want to extract the updated row, and insert most of it's data into a different database.|||Okay, I've been working with this for some hours now, and have come a little further, but still have some obstacles to climb over.

First, when I try to use inserted.contacttype to get the value from the updated table, and into my new table, I get a "Error 128: The name 'contacttype' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

This works:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (1,2,3,4)
END

But this doesn't:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (inserted.contacttype,2,3,4)
END

Second, I am still struggeling with getting the trigger to run the INSERT statement only when contacttype changes to a specific number. Right now the INSERT fires when the contacttype field is updated to any value.|||The select statement after the update is not necessary. Try this:


CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF inserted.contacttype = 2
BEGIN
<perform needed operations here>
END
END|||Here is the complete trigger I've written so far, with tomh53's suggestion. With the "IF inserted.category_idx = 2" line I get a "Error 107: The column prefix 'inserted' does not match with a table name or alias name used in the query."

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
IF inserted.category_idx = 2
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma SELECT department, contact_id, name, number1, number2, business_idx, orgNr from inserted
END
END|||Scalpel ... my apologies for ** bad ** code. Try this:

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma
SELECT department, contact_id, name, number1, number2, business_idx, orgNr
FROM inserted
WHERE inserted.category_idx = 2
END

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Split to send top 10, top20, and top 30 in three different flows ?

I have dataflow that i wish to split. The recordset that i uses is sorted by the Sort Task. After that i'll like to take the Top 10 records and send in one direction, then ill like to take the top 20 and send in another direction and finally Top 30 in a third direction.

How can i do that ?

You can use a script component to give each record a number using the technique described here: http://www.sqlis.com/default.aspx?37

Then you can filter on the values that are created using a conditional split transform component.

Make sense?

-Jamie

|||Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....|||

cgpl wrote:

Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....

Yeah I agree, that would be nice. Perhaps you could request it at betaplace or http://lab.msdn.microsoft.com/productfeedback/Default.aspx?

-Jamie|||You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.|||

DarrenSQLIS wrote:

You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.

Oh yeah, Darren's right. And the syntax he's on about is <buffername>.AddRow()

-Jamie|||The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks|||

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

|||

cgpl wrote:

The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks

Yeah another excellent idea, you would assume that selecting the first N rows is easier than selecting a random number. Did you submit the idea to Microsoft?

-Jamie|||

jesal wrote:

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

As explained, no, not really. Is there any reason why you don't want to do this in a script?

-Jamie|||I did!

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=2bc4ee52-aec6-409d-b39c-6e2fb4945799|||Out of interest, would you want to send:
Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

or

Rows 1-10 to output1
Rows 11-30 to output2
Rows 31-60 to output3

?

I might have a crack at this myself...it sounds quite interesting.

-Jamie|||

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

|||

jesal wrote:

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

Jeppe,
This should do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx

-Jamie|||Thank you Jamie!

Your "SSIS Nugget: Select Top N in a data-flow" worked great. I have one question though - I couldnt get the "Output0Buffer" to work, but when I changed the SynchronousInputID for the Output to 0 it worked, what is the connection between the declaration of the Output0Buffer and the Synchronous setting?

Thansk again

- Jeppe

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Conditional Split problem

Don't know if anybody else has had this -

In a dataflow task I added a conditional split that currently has two possible outcomes.

Initially this was a straight choice. Later I added an "or" clause on the second leg thus

expression1 || expression2

on which the second leg ceased working.

After some mental anguish I deleted the component and then reimplemented it using identical logic and magically (or so it appears) it all worked.

So, it appears the old tricks still apply...

I don’t suppose you have the package before and after so we can look at
the differences?|||Sorry, no - versions under version control don't have the relevant logic in them. I'll keep an eye out for it in future though...|||

I have had people report something similar, but every time it turned out to be a slight error in the expression that resulted in one output never receiving data. What would be compelling as a bug would be if the number of rows on all outputs (with all outputs connected) did not equal the incoming rows.

If you see it again, please do post more details.

Donald

|||

When I created the new component, I copied the old expressions to Notepad and then pasted them back in again, so your scenario is unlikely (though of course not impossible.)

|||

Another possibility is that, if you have multiple conditional split conditions, they are being applied in a different order from your first implementation.

Donald

Conditional Split problem

Don't know if anybody else has had this -

In a dataflow task I added a conditional split that currently has two possible outcomes.

Initially this was a straight choice. Later I added an "or" clause on the second leg thus

expression1 || expression2

on which the second leg ceased working.

After some mental anguish I deleted the component and then reimplemented it using identical logic and magically (or so it appears) it all worked.

So, it appears the old tricks still apply...

I don’t suppose you have the package before and after so we can look at
the differences?|||Sorry, no - versions under version control don't have the relevant logic in them. I'll keep an eye out for it in future though...|||

I have had people report something similar, but every time it turned out to be a slight error in the expression that resulted in one output never receiving data. What would be compelling as a bug would be if the number of rows on all outputs (with all outputs connected) did not equal the incoming rows.

If you see it again, please do post more details.

Donald

|||

When I created the new component, I copied the old expressions to Notepad and then pasted them back in again, so your scenario is unlikely (though of course not impossible.)

|||

Another possibility is that, if you have multiple conditional split conditions, they are being applied in a different order from your first implementation.

Donald

conditional split on field in csv file

I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.

Thanks

Might there be trailing spaces on the file?

Try:

Right(Trim([FieldA]),4)=="ABCD"

|||

Thanks fo reply, yes I tried that one but it doesn't have trailing spaces. I think it has something to do with fields in Input file.

When I look at the file in notepad values are

"123","somevalue"

"123ABCD","somevalue"

I have seen some posts regarding text qualifier as a problem, mine is set to " but conditional split still fails.

|||Is it throwing any type of error message?|||

No the file processes fine.

If I add another row to file and put in the suffix then the conditional split works but I think this is because the text qualifiers are removed once I had row to file eg

Original file

"123","somevalue"

"123ABCD","somevalue"

goes thru data flow task with no errors but conditional split doesn't work

Modified file will look like

123,somevalue

123ABCD,somevalue

456ABCD,somevalue

and will work for both rows with suffix.

|||

Could you put a data viewer before the conditional split and see what data is getting in?

Thanks.

|||

Have put data viewer in and first riow looks correct but rows following look like they have another delimiter shown below

123, somevalue

o “456”, “somevalue”

o “123ABCD”, “somevalue”

Row delimiter is set to {CR}. Column 1 delimiter is set to {CR} if I change this to {CR}{LF} then the process fails with error delimeter for column 1 not found.

Thanks

|||

Got it to work in the Columns tab

Row Delimeter is {LF}

Column delimiter is Comma {,}

Text qualifier is "

in advanced tab

Coliumn delimeter of 2nd column is {LF}

which ended up making data come in as

123, somevalue"

456, somvalue"

123ABCD, somevalue"

which the conditional split was happy with.

Thanks for your help

Conditional Send Mail Task

Hi,

I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?

does any one have an idea about it,

regards

You need conditional workflow: http://www.sqlis.com/default.aspx?306

You can base your expression on a boolean variable that can be set from various places, including a script task.

-Jamie

|||

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

|||

Zadoras wrote:

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.

-Jamie

|||

that's what i was thinking... but i m searching for other way (may be the easiest way)

BTW... thanx for your support

if ne one come across to ne better idea than that please let me know

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 execution of first task

I have a situation where I'd like to conditionally execute the first task in a package based on the contents of a user variable.

If user variable "Var1" is false I want to begin execution with the first task.

If "Var1" is true I want to begin execution at the second task.

My first thought of course was SequenceContainer, but the same issue would exist for the first task in a SequenceContainer.

Is there a way to do this?

Thanks!

To conditionally execute a task you would use a constraint with an expression on it, but you need two tasks (containers) to do this. The ideal answer is to use something that does nothing, but offers a start point for the constraint. A sequence container works rather well, just drop it on and collpase it (the arrow on the right-hand side of the header). You can then link that to the real task, nothing should go inside that sequence container.|||

I have created a sample solution inline with Darren's suggeston to use 'Sequence Container' for placing the precedence condition @. http://mystutter.blogspot.com/2006/03/sql-server-integration-services.html

Please let me know your comments.

Thanks,
Loonysan

|||

Thank you. Your example was perfect.

I found another solution as well. I created a Sequence Container, inside that I created another Sequence Container and my first task. I put an expression constraint between those. Then I linked the outer Sequence Container to the second task.

I appreciate the help.

Conditional execution of DTS package task

I've searched everywhere for this but can't find the answer

I want to run a DTS package that simply executes a SQL statement to get
a count of rows in a table, if the resulting number is greater than
zero I want to execute another step in the same package, else just
quit.

I don't to pass global vars from one package to another

How can I do this?

Any help would be greatHi Scouser,

I've got a suggestion: Create a stored proc that does the count of the
records and then execute the DTS from the procedure, if the condition
is true.
Look at this message if you want to see how to execute the DTS-Package
from a stored procedure:
http://groups.google.de/group/borla...62580101f3 8fe

Michael
www.zankl-it.de

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 Container

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie