Help with using the right approach

Greetings my friends

I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package.

The package does the following :

1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table.

2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above)

3) Update the actual DIM table with the new data help in the staging table.

For this task I want to learn the use of the Lookup component which I think is appropriate.

My questions are as follows :

If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!

I am totally confused... I don't even know where to start with this.

Your help would be appreciated.

Thanks SQL friends.

dreameR.78 wrote:

If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!

This should explain it: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Hi Jamie,

Firstly, thanks for the link. Although I haven't implemented a solution yet, I think I know how to go on about solving my problem. But I have one more question regarding the last paragraph from your article.

You say :

"So the only thing you have to do after that is find a way of changing @.[VariableContainingFilterValue]. That's not within the scope of this post because there are many ways that you could do this. I'll leave it to your imagination!"

I am thinking of adding a SQL Task component to set the value of my initial variable. In other words, create a SQL Task containing :

"select max(PRICE_ID) from Price_DIM"

and hold that value in a variable which I can then use to replace @.[VariableContainingFilterValue].

I am not sure if this the most elegant way to do this. Can you suggest alternatives?

Thanks for your help in advance.

|||

dreamer,

In your situation, that is absolutely the correct thing to do! You'll have to give max(PRICE_ID) an alias. Like this:

"select max(PRICE_ID) as MAX_PRICE_ID from Price_DIM"

because you need to reference that alias when you pass the value into your SSIS variable.

-Jamie

|||

Hi Jamie,

Thank you once again.

I have created two variables, one to hold my maximum PRICE_ID and one to hold the SQL String which I plan to use in my data source component. The problem is, when I define the expression (as suggested in your blog) I get an error saying that the expression for variable SourceSQL failed evaluation. There was an error in the expression.

I don't understand as I believe I put the right expression which is :

"Select * from Offer_Price_Dividend where OFFER_PRICE_ID >" + @.[User::MAX_OFFER_PRICE_ID]

One thing I haven't done is cast my MAX_OFFER_PRICE_ID as a string. I will try that and let you know.

Thanks.

|||

dreameR.78 wrote:

Hi Jamie,

Thank you once again.

I have created two variables, one to hold my maximum PRICE_ID and one to hold the SQL String which I plan to use in my data source component. The problem is, when I define the expression (as suggested in your blog) I get an error saying that the expression for variable SourceSQL failed evaluation. There was an error in the expression.

I don't understand as I believe I put the right expression which is :

"Select * from Offer_Price_Dividend where OFFER_PRICE_ID >" + @.[User::MAX_OFFER_PRICE_ID]

One thing I haven't done is cast my MAX_OFFER_PRICE_ID as a string. I will try that and let you know.

Thanks.

You've worked it out on your own :)

Yes, it needs to be casted as a string.

-Jamie