LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.

> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql

looping through a recordset

Hi
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>

Loop thru data subset

hi

I have the following query:
select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid

This returns multiple rows of data. I want to loop through each row, extract 2 values and see if they exist in another table. I need to do this in Query Analyser.

Can anyone please help!

Thanking you in advance
PORRASTAROf course you can use cursor, but I prefere to use fake cursor:

Save your result in temporary table and do loop by newid.

select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid,
IDENTITY(int, 1, 1) AS newid -- add identity filed
into #tmp -- temp table
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid|||I don't think you need to loop through anything if you just want to see if the values exist in another table. Just treat your query as a subquery and join it to your other table:

select Value1, Value2
from YourOtherTable
inner join
(select sum(iunits) as iunits, sum(ivalue) as ivalue, sum(ivolume) as ivolume, sum(ivolumeHL) as ivolumehl, periodid as periodid, productid as productid, tbstores.storeid as storeid
from tbstoredata
inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111
group by tbstores.storeid, periodid, parentid, productid) SummarySubquery
on YourOtherTable.Value1 = SummarySubquery.Value1 and YourOtherTable.Value2 = SummarySubquery.Value2

...or if you want to use the dataset multiple times, store it in a temporary table or table variable and join that instead.

blindman

Lookup Transform

Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?The lookup must select against your table with demo_id in it... We need more information here... If you can't find demo_id in your lookup table, and you need it to be there, well then you probably have the wrong table defined in your lookup.|||

Not sure what your problem is. In general, Lookup transform can be basedon a table or on a query; if you don't see an specifc column in the Column is because that column is not on that table/query.

Could you provide more details on how you have set up your package.

BTW, Have you considered to just include the lookup table as a part of the OLE DB Source query and not use the lookup transform at all?

|||Ihave a select customer.salary, customer.occupation in source DB. These should be Lookup columns matched with the id from dim_demography. How?|||The source has nothing to do with the lookup.

In the lookup, you need to specify a lookup table or query. When that's done, then you map columns from the dataflow (your source) to columns in the lookup table. You'll have to have an ID column coming from the source query though.

I'm sorry, but your response didn't say anything more clear than your original post.|||How would you do if you had a demo_id in db Destination, which should be matched with the values salary and occupation in Dim_demographic (containing columns id, salary, occupation) or de db Source sql customer.salary, customer.occupation?|||Please provide your schema:

Table1:
Column1
Column2
....

Table2:
Column1
Column2
...

We'll go from there.|||OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr

Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id

Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...
|||

curiousss wrote:

OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr
Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id
Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...

Use a lookup transform with a query like:

Take the output of the OLE DB Source component to a Lookup transform. Inside of the lookup write a query like:

Select dim_demographic_id, salary, occupation
From Dim_demographic

Then in the columns tab draw a line to join Salary and occupation; in the bottom part, choose id from the dropdown list to bring the dim_demographic_id (from dim_demographic) to the pipe line. Now when you connect the pipeline to the destination component you should have that extra column; all you have to do is to create the mapping between thedestination column demo_id and the column in the pipeline.

BTW, it is better to provide a query inside the lookup tranform than select the tablename from the list...it save resources(memory) improvng performance.

|||Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".|||The OLE DB destination preview shows you what's in the destination table, not what's in the data flow. You have nothing in your table, hence nothing shows up in the preview.|||

curiousss wrote:

Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Ok Few things here:

The preview in the OLE DB Components shows data that is already in that table; if this is your initial load, the table is empty, then the preview will show nothing.

The first message you in the execution error is warning you about duplicates in the Dim_Demographics; meaning, there is more than one row for a combination of salary and occupation. This is treated as a warning as SSIS will use any of those values; so be careful on that. Provide a query and joins that retrieves unique values.

The other 2 messages are actually errors; check the data types of the source and destination for salesperson_is column; they have to be the same.

|||Hi!
Thank you.. its getting late...wouldn't this eliminate duplicates?
SELECT DISTINCT id, salary, occupation
From Dim_demographic|||by the way.. is there any logic/rule/order when changing data types (just shows error now)

Purchase Source both salesperson_id are DT_18

Purchase Destination salesperson_id Input DT_18
Purchase Destination salesperson_id external DT_14

...Dim_salesperson salesperson_id is DT-14|||

I don't think the Distinct will eliminate the duplicates as each row (I guess) has a unique Id value.

To convert data types there is a Data conversion tranform in the toolbox of the data flow.