PedroCGD


Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it Using a script Can I use a Script Component That receive 2 parameters for input and give me the fields returned from query as output

Thanks!!





Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Jamie Thomson


PedroCGD wrote:

Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it Using a script Can I use a Script Component That receive 2 parameters for input and give me the fields returned from query as output

Thanks!!

Why doesn't the OLE DB Source work for you

-Jamie







Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

because the source already come from OLEDB datasource and other transformations...





Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Jamie Thomson

PedroCGD wrote:
because the source already come from OLEDB datasource and other transformations...

Pardon

I'm very confused. If the data is already in your data-flow, why do you need to go external to get it

-Jamie






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

Because I have one lookup up that I made and have no matchs dates... and for these dates with no records, i need to check the nextdate for each row and return the value.... and continue the dataflow...

I cant do it in a second lookup and I cant use a OLE DB Command because It doesnt return me the results to output... :-(

Understood

Thanks






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Jamie Thomson

PedroCGD wrote:

Because I have one lookup up that I made and have no matchs dates... and for these dates with no records, i need to check the nextdate for each row and return the value.... and continue the dataflow...

I cant do it in a second lookup and I cant use a OLE DB Command because It doesnt return me the results to output... :-(

Understood

Thanks

No I don't think so. Sorry.

So you have a record that has a date of (say) 2007-04-17. If the lookup fails for that date then you want to attempt a lookup on the same row but this time with a date of 2007-04-18. Is that correct

-Jamie






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

Jamie,

Could be 2007-04-18 or 2007-04-19 or other, must be the next date for each row...

If you dont have the date 2007-04-18 and have the date 2007-04-19, so, the matched that would be2007-04-19.

If you have another option, please tell me!!

Thanks!!






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Jamie Thomson

PedroCGD wrote:

Jamie,

Could be 2007-04-18 or 2007-04-19 or other, must be the next date for each row...

If you dont have the date 2007-04-18 and have the date 2007-04-19, so, the matched that would be2007-04-19.

If you have another option, please tell me!!

Thanks!!

Maybe I'm stupid but I still don't get it.

How can the same row have multiple values in a particular column

-Jamie






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Phil Brammer

No, here's his deal.

Source inside dataflow:

COL1 COL2
XCY 04/17/2007
DHB 05/16/2007

Then he has a lookup table:
DATE VALUE
04/17/2007 ABC
05/18/2007 CBD

Pedro wants to take COL2 from his data flow and look it up in this lookup table. If he doesn't have a match (05/16/2007) he wants to keep looking until he runs into 05/18/2007, which is the next date in the table AFTER his lookup date.

It's a tough challenge, for sure. He has a function written that does this though, but he wants to use this function (see one of his other many threads on this topic) inside his data flow if all else fails. The problem is passing in COL2 to his function (inside the data flow) and returning the function's value.





Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

Dont worry, maybe I didnt explain you correctly... I will try it again...:-)

Imagine this tables:

FactTable
Date бн бн Rate
10-10-2005
10-10-2005
12-10-2005

DimensionTable
Date Rate
10-10-2005 1,234
14-10-2005 2,8
15-10-2005 2,3
20-10-2005 3,1

For example I have a lookup to check each rate for my factTable based on each date, and currency (but forget the currency right now).

and the result is:

FactTable
Date бн бн Rate
10-10-2005 1,234
10-10-2005 1,234
12-10-2005

and the date 12-10-2005 was not found...

In this cases I need to get the next date, and in this case, for the date 12-10-2005, the next date is 14-10-2005.

I created the query

SELECT TOP 1 Data, Currencies_Name, RevalRate

FROM Spot_Rates

WHERE (Currencies_Name = @MyCurrency) AND (Data > CONVERT(DATETIME, @MyDate, 102))

ORDER BY Data ASC

that gives me for each date, the next date and the respective revalrate... (forget the currency, isn't to much important)

So, I need the final result like:

FactTable
Date бн бн Rate
10-10-2005 1,234
10-10-2005 1,234
12-10-2005 2,8

Understood

How can I do it :-)

Using a script component that exexute the query and returns the output for the dataflow, I thing is one option.. what you think I never used script component as a transformation...

Regards!






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

Jamie Thomson

Phil Brammer wrote:
No, here's his deal.

Source inside dataflow:

COL1 COL2
XCY 04/17/2007
DHB 05/16/2007

Then he has a lookup table:
DATE VALUE
04/17/2007 ABC
05/18/2007 CBD

Pedro wants to take COL2 from his data flow and look it up in this lookup table. If he doesn't have a match (05/16/2007) he wants to keep looking until he runs into 05/18/2007, which is the next date in the table AFTER his lookup date.

It's a tough challenge, for sure. He has a function written that does this though, but he wants to use this function (see one of his other many threads on this topic) inside his data flow if all else fails. The problem is passing in COL2 to his function (inside the data flow) and returning the function's value.

OOOHHHHHHHHHHHH!. I get it.

Read this, it will help alot:

SSIS Lookup with value range

(http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range)

-Jamie






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

I already tried these advanced options of lookup transform, but I will try again... just a moment... :-)






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

I created in advanced tab:

select * from
(select * from [dbo].[Spot_Rates]) as refTable
where [refTable].[Data] =
(SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ) AND (Data > CONVERT(DATETIME, , 102))
ORDER BY Data ASC)
and [refTable].[Currencies_Name] =

but returned an error qhen I tried to define the parameters:

"Parameter information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command."






Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

jaegd

The range based lookup can be accomplished with or without a script transform.

Using a lookup: Set the Caching SQL Statement in the advanced tab to the desired query.
For example, the Caching SQL Statement would equal.

SELECT TOP 1 * from (
SELECT TOP 100 PERCENT Data, Currencies_Name, RevalRate
FROM Spot_Rates
ORDER BY Currencies_Name, Data) as refTable
WHERE refTable.Currencies_Name = AND refTable.Data >

The reference table itself, would be equal to the derived table:

SELECT TOP 100 PERCENT Data, Currencies_Name, RevalRate
FROM Spot_Rates
ORDER BY Currencies_Name, Data

This answer presupposes some kind of index/partition combination on the Spot Rates Currencies_Name and Data columns for any kind of lookup speed.

Now, using a script transform, there is the "live cache" technique, used frequently throughout Project Real. The general idea is that for each lookup attempt (make or miss), the transform is invoked. The transform detects whether there was a lookup miss, and if so, looks up the data in cache first, and secondarily calls a stored procedure, caching the results in .NET Generic Dictionary. This example is taken from the Fact_StoreInventory_Daily_MT package available from the project Real download.

Code Snippet

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Public Class ScriptMain
Inherits UserComponent

Private objCommand As New OleDbCommand("etl.up_DimStore_CreateInferredMember")
Private parmBusinessID, parmSurrogateID As OleDbParameter
Private htCache As New Generic.SortedDictionary(Of Integer, Short)
Public Overrides Sub InputMain_ProcessInputRow(ByVal Row As InputMainBuffer)
If Row.SKStoreID_IsNull Then
Dim intBusinessKey As Integer = Row.STORENUM
Dim intSurrogateKey As Short
If Not htCache.TryGetValue(intBusinessKey, intSurrogateKey) Then
intSurrogateKey = Me.ExecuteSP(intBusinessKey)
htCache.Add(intBusinessKey, intSurrogateKey)
End If
Row.SKStoreID = intSurrogateKey
End If
End Sub

Private Function ExecuteSP(ByVal BusinessId As Integer) As Short
parmBusinessID.Value = BusinessId
parmSurrogateID.Value = 0
objCommand.ExecuteNonQuery()
Return CShort(parmSurrogateID.Value)
End Function

Public Overrides Sub PreExecute()
MyBase.PreExecute()

With objCommand
.CommandType = CommandType.StoredProcedure
.Connection = New OleDbConnection(Connections.SQLREALWarehouse.ConnectionString)
.Connection.Open()
With .Parameters
parmSurrogateID = .Add("@SK_Store_ID", OleDbType.SmallInt)
parmSurrogateID.Direction = ParameterDirection.InputOutput

parmBusinessID = .Add("@Store_Num", OleDbType.Integer)

End With
.Prepare()
End With
End Sub

Public Overrides Sub PostExecute()
MyBase.PostExecute()

htCache = Nothing
objCommand.Connection.Close()
End Sub
End Class







Re: Execute a query inside dataflow and use the fields returned to continue dataflow... how?

PedroCGD

Dear jaegd,

your queries for the lookup doesnt work, and if you read the previous post you will find why...

About the script I think that it could be a good example to try this option... I will check it and give you soon some feedbak..

Thanks!