Insert Value list doest not match column list

HI...

I need to do a simple task but it's difficult to a newbie on ssis..

i have two tables...

first one has an identity column and the second has fk to the first...

to each dataset row i need to do an insert on the first table, get the @.@.Identity and insert it on the second table !!

i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"

here is the script

INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@.@.Identity,
?,
?,
?
)

what's the problem ?

Is that a cut and paste of your query?

There is a missing space between "Reference)" & "Values" in the first insert statement.|||Yes...it's a copy past....

I did what you ask and the problem remains the same|||Then you must not have all of the parameters mapped. Looks like 9 parameters.|||But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!|||

Alexandre Martins wrote:

But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!

When you click on the Column Mappings tab, you can't map the columns accordingly?|||

No! it's showing the warning "Insert Value list doest not match column list" and not mapping...

The funny thing is.....this way don't works

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID,CustomerID,AddressTypeID,TypeDescription)

VALUES(@.@.Identity,?,?,?)

but this way

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID)

VALUES(@.@.Identity)

works perfect.....but i need the other fields....

i changed the table too to test....and with one field works....two or more "Insert Value list doest not match column list" and not mapping"

i don't know what to do....

Insert Problem

I am fairly new to SQL and have been given a horrible task. I have 2 tables, table 1 is eng_routing and table 2 routing the rows in table 1 are
engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 30 now machine here
1 40 end

the rows in table 2 are

part opnumber rdesc
1 10 goodsin
1 20 treat
1 30 final

the task I have been given is to insert the ops from table 2 into table 1 where engpart=part and where table 1 has a treatments, but the data from table 2 needs to be inserted at the next number after treatments (in this example opnumber 21 and so on) for all opnumber and rdesc,so the outcome would look like

engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 21 goodsin
1 22 treat
1 23 final
1 30 now machine here
1 40 end

I have been given the following code but an error occurs on line 7 on keyword 'where'

use efacdb
begin tran
INSERT INTO eng_routing
SELECT routing.part, TBL3.engopnumber + (SELECT count(opnumber) FROM routing T1
Where T1.part = T.Part AND T1.opnumber<=T.opnumber ) opnumber ,routing.rdesc FROM routing T
INNER JOIN (SELECT engpart,engopnumber,engrdesc FROM eng_routing
WHERE eng_routing.engrdesc= 'treatments') TBL3 where T.part=TBL3.engpart

select * from eng_routing
--commit tran
--rollback tran

Any help would be gratefully recieved--hope this is what you are after, if not my appologies

--first select distinct opnumber into temp table
select distinct opnumber into #c from #b

--now you should be able to do your insert from a select statement like
--this
SELECT b.part, a.engopnumber + (SELECT count(c.opnumber) FROM #c c
Where b.opnumber>=c.opnumber ) opnumber ,b.rdesc FROM eng_routing a, routing b
where a.engrdesc= 'treatments'
order by opnumber|||Thanks for your quick reply. The code falls over on line 1 Invalid object name #b. Thanks for your help|||if you cant figure to replace #b with routing

insert or update data records

I'd like to do the following thing with a data flow task

Get all the records from a source (for example customers from a textfile, flat file source) Then check for each record if the customer already exists in a table, for example with a customerID. If not, insert the record in the table (ole db destination), else copy the customer thats already in the table to another table (history table) and update the record with the customer from the textfile.

Is this possible?, and what kind of data flow transformation do I need?

Use a lookup to check if the customer exists. Use error output from the lookup to insert new customers, OLE-DB Destination. Use the normal output from the lookup to feed an OLE-DB Command to do the move and update. That may be easier if wrapped in a stored procedure.