Lookup error redirection

Hi,

I would like to know if the following is possible or if there is another way to implement this. I have a lookup transformation which i check to see if a specific record is stored. If nothing is returned i would like to insert a record into the table and then try the lookup again so that this time it will find the record and continue processing. How could i redirect the data flow to allow something like this to be done. I have tried linking the lookup failure constraint to an oledb destination and then attempted to link this back to the lookup but this has not worked.

Does anyone have any ideas on this?

Thanks in advance,

GrantInstead of using an OLEDB Destination component, use an OLEDB Command component. The Destination component will terminate flow while the OLE DB Command will pass records through.|||I seem to have problems when i try to use this. Should it allow me to use a subquery which obtains a record count from a table and if that returns a 0 then insert values from the input into another table?

Thanks

Grant|||I generally use stored procedures as the SqlCommand property. [Syntax is EXEC dbo.StoredProcedureName ?, ?, ?] One of the reasons I like stored procedures is that on the Column Mapping tab, the parameter names are displayed and it's easier to verify that I have the correct data mapped.|||

Another solution is to use multicast and union components: split the error output of Lookup component, direct one output to Sql or OLEDB Destination component, then merge another output with successful output of Lookup component using Union All component.

Lookup -> Multicast -> Destination
| |
Union All
|

|||

Martin, would you mind explaining in a little more detail?

At present, I use the OLEDB Command quite frequently, but with SQLCommands like, "UPDATE tblTEMP SET TempID = ?, SSN = ?,... WHERE ID = ?" etc. As you mentioned, it's a pain to map all of the fields to generic param0, param1, etc.

So what you are saying is that I can create a SP to "map" the parameters?

I appreciate any advice that you can offer a newbie...

Lookup / Merge Join / Script - Howto look up values by comparing to a range of values?

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:

"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @.zip_code AND addr_prim_lo <= @.street_number AND addr_prim_hi >= @.street_number " & _

" AND addr_prim_oe = @.addr_prim_oe AND street_pre = @.street_pre AND street_name = @.street_name " & _

" AND street_suff = @.street_suff AND street_post = @.street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @.expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"

My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value

Any suggestions?

thanks for your time...

After a bit of research, I have found a few different solutions to this problem. The first uses the lookup by altering the sql statement and parameter set under the advanced tab (enable memory restriction / modify the sql statement) as discussed here http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range. The second (a bit less likeable) option was to use a script component and found here http://sqlblogcasts.com/blogs/simons/archive/2005/10/04/628.aspx

I am a bit disappointed that the range functionality was not prebuilt in to the components (other than through the memory restriction)... Does anyone else have any ideas or suggestions?

Thanks for your time.

|||Although the logic in your query is pretty clear, the context and output is not - at least not to me. What exactly do you want to have happen in your data flow as the output of this process?|||

Reading back I can see how you would be confused...

I am trying to look up information based on an address range (i.e. if you are on the 23rd block (2300 - 2400) there will be one set of police and fire municipality codes, if you are on the 24th block 2401 - 2500 there will be seperate municipality code outputs) We are aggregatting these facts based on addresses supplied from our customer base to a table provided by the state which maps out the various address blocks for which we will need to break down our earned premiums such that we can pay taxes to the firefighter and police pension plans.

I think that the main point was that there is not an intuitive way of looking up information based on a fact tables value being within the dimension tables value range. I have also had this problem looking up values based on date ranges...

|||

I don't know how much anyone is interested, but here is a topic on the feedback site which you can vote on to bring this to the ssis teams attention...

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

Lookup / Merge Join / Script - How to?

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:

"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @.zip_code AND addr_prim_lo <= @.street_number AND addr_prim_hi >= @.street_number " & _

" AND addr_prim_oe = @.addr_prim_oe AND street_pre = @.street_pre AND street_name = @.street_name " & _

" AND street_suff = @.street_suff AND street_post = @.street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @.expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"

My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value

Any suggestions?

thanks for your time...

After a bit of research, I have found a few different solutions to this problem. The first uses the lookup by altering the sql statement and parameter set under the advanced tab (enable memory restriction / modify the sql statement) as discussed here http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range. The second (a bit less likeable) option was to use a script component and found here http://sqlblogcasts.com/blogs/simons/archive/2005/10/04/628.aspx

I am a bit disappointed that the range functionality was not prebuilt in to the components (other than through the memory restriction)... Does anyone else have any ideas or suggestions?

Thanks for your time.

|||Although the logic in your query is pretty clear, the context and output is not - at least not to me. What exactly do you want to have happen in your data flow as the output of this process?|||

Reading back I can see how you would be confused...

I am trying to look up information based on an address range (i.e. if you are on the 23rd block (2300 - 2400) there will be one set of police and fire municipality codes, if you are on the 24th block 2401 - 2500 there will be seperate municipality code outputs) We are aggregatting these facts based on addresses supplied from our customer base to a table provided by the state which maps out the various address blocks for which we will need to break down our earned premiums such that we can pay taxes to the firefighter and police pension plans.

I think that the main point was that there is not an intuitive way of looking up information based on a fact tables value being within the dimension tables value range. I have also had this problem looking up values based on date ranges...

|||

I don't know how much anyone is interested, but here is a topic on the feedback site which you can vote on to bring this to the ssis teams attention...

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

Lookup / Merge Join / Script - How to?

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:

"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @.zip_code AND addr_prim_lo <= @.street_number AND addr_prim_hi >= @.street_number " & _

" AND addr_prim_oe = @.addr_prim_oe AND street_pre = @.street_pre AND street_name = @.street_name " & _

" AND street_suff = @.street_suff AND street_post = @.street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @.expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"

My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value

Any suggestions?

thanks for your time...

After a bit of research, I have found a few different solutions to this problem. The first uses the lookup by altering the sql statement and parameter set under the advanced tab (enable memory restriction / modify the sql statement) as discussed here http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range. The second (a bit less likeable) option was to use a script component and found here http://sqlblogcasts.com/blogs/simons/archive/2005/10/04/628.aspx

I am a bit disappointed that the range functionality was not prebuilt in to the components (other than through the memory restriction)... Does anyone else have any ideas or suggestions?

Thanks for your time.

|||Although the logic in your query is pretty clear, the context and output is not - at least not to me. What exactly do you want to have happen in your data flow as the output of this process?|||

Reading back I can see how you would be confused...

I am trying to look up information based on an address range (i.e. if you are on the 23rd block (2300 - 2400) there will be one set of police and fire municipality codes, if you are on the 24th block 2401 - 2500 there will be seperate municipality code outputs) We are aggregatting these facts based on addresses supplied from our customer base to a table provided by the state which maps out the various address blocks for which we will need to break down our earned premiums such that we can pay taxes to the firefighter and police pension plans.

I think that the main point was that there is not an intuitive way of looking up information based on a fact tables value being within the dimension tables value range. I have also had this problem looking up values based on date ranges...

|||

I don't know how much anyone is interested, but here is a topic on the feedback site which you can vote on to bring this to the ssis teams attention...

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

Looking for tutorials/walkthroughts on Report parameters

Could someone please point me to a tutorial on how to implement complex
parameters in Reporting Services reports? I ask this because the online help
available seems to be extremely minimal.
What I will probably be doing is writing reports with multiple parameters
including single values, dropdown lists, and calendars. Also I probably will
need to hide/expose/change-value-of some parameters based on the selection of
others.
Any sort of info or even a recognizable starting point to research this
would be of help. Thanks!Sorry. False alarm. Please cancel.
(I should say I found the help for the Report Parameters editing screen
minimal. Finally Googled and found the Technet tutorial on cascading
parameters which is roughly what I was looking for.
However this is all academic because I've just been taken off the project.)
"B. Chernick" wrote:
> Could someone please point me to a tutorial on how to implement complex
> parameters in Reporting Services reports? I ask this because the online help
> available seems to be extremely minimal.
> What I will probably be doing is writing reports with multiple parameters
> including single values, dropdown lists, and calendars. Also I probably will
> need to hide/expose/change-value-of some parameters based on the selection of
> others.
> Any sort of info or even a recognizable starting point to research this
> would be of help. Thanks!

Looking for existing tool/product to integrate in MS CMS/SQL based website

Hi all!
I am the IT manager for the Flemish Red Cross and I am looking for an
existing tool/product that we can use to implement a full text-search
(including webpages, Word documents and PDF's) on our new website, thta
we're building on MS CMS and SQLServer.
Any suggestions? We don't really have the time to develop something
ourselves, and I am convinced that there are existing tools or products that
can do the job.
We're willing to spend some money on it, but not in the area of 10000
USD/EUR per processor...
Any help would be greatly appreciated.
I can be e-mailed directly at werner.huysmans@.advalvas.be or
werner.huysmans@.rodekruis.be
Thx in advance!
Werner
SQL Full Text Search is optimal for this and it is free.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Werner huysmans" <werner.huysmans@.advalvas.be> wrote in message
news:pHrhf.61734$rA.54376@.blueberry.telenet-ops.be...
> Hi all!
> I am the IT manager for the Flemish Red Cross and I am looking for an
> existing tool/product that we can use to implement a full text-search
> (including webpages, Word documents and PDF's) on our new website, thta
> we're building on MS CMS and SQLServer.
> Any suggestions? We don't really have the time to develop something
> ourselves, and I am convinced that there are existing tools or products
> that
> can do the job.
> We're willing to spend some money on it, but not in the area of 10000
> USD/EUR per processor...
> Any help would be greatly appreciated.
> I can be e-mailed directly at werner.huysmans@.advalvas.be or
> werner.huysmans@.rodekruis.be
> Thx in advance!
> Werner
>