Get Previous step result in an SSIS Script Task

Hello,

I am using SQL Server 2005 Integration Services to create new values for my tables. One step I must do is execute a query and the script task that receive its constraint (it is set to completion) must do different things depending on the query result.

My question is: how can I know the result of the precedence constraint?

Thank you,

Pablo Orte

If a task executes then it matched your precedence constraint. If you say "Complete" and "VarA == 20", and it executes then you can be assured that was the result. Are you wanting to do branching, or have multiple precedent constraints?|||

The key point to what I think Sean is suggesting, is that if you can get a variable to hold the result, you could use an expression to influence the constraint, whether it is satisfied or not. Expression support on a constraint is very useful for influencing workflow.

I am not sure that this is useful to you, since you have to populate that variable somehow. One way would be to use an on error event handler on the task. Default the variable to true, and change it to false in the event handler.

I don't know why or exactly what you are trying to do, but my first reaction would be this sounds like a bad idea. I think there should be a better, more SSIS way of trying to achieve this. Forgive me, but your method reminds me of the hacks we used in DTS. To conditionally do something why not use two tasks, or why not use event handlers to do an out of band type operation when something fails.

|||

This thread might have somthing for you...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1

Rafael Salas

|||

Hi and thanks for your replies,

The problem I have is that I have to do this in a script task situated after an "Execute SQL". This cannot return any parameter and I need to have something in the code like this

IF objDTSPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionResult = 0 THEN

This code worked in SQL Server 2000, but not in SQL Server 2005.

Thanks,

Pablo Orte

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!