How to do batch insertion by reading data from excel and insert into a OLE DB Data source

Question

How to do batch insertion by reading data from excel and insert into a OLE DB Data source

navenis4u

SSC-Addicted

Points: 453
More actions
July 29, 2009 at 5:56 am

#134631

Hi,
I have a excel file with multiple data sheets . Each data sheet represents a table of data. each tables in the data sheet is related by the integrity constraint.
How can i insert data in batch...from the excel ?
I have two cases
-----------------
CASE 1
i need to read and insert say 100 records from excel to oledb destination for 1 table
ie data sheet 1..and process the next 100 records after inserting the first 100 until the last data.
then insert the data from the next sheet in batch size say 100 records itself...and so on..
CASE 2
I need to process the first 100 data from sheet1 ie table1...insert the 100 data into ole db destination.
read the corresponding 100 records to the data ( ie corresponding to the id ) from sheet 2. and then insert it...
Then process the next 100 records from sheet1 and after completing it reading from sheet 2 and so on until all data is fetched and inserted..
Thanks,
Naveen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

binko Default port Points: 1408 More actions · Answer 1

navenis4u (7/29/2009)
Hi,
I have a excel file with multiple data sheets . Each data sheet represents a table of data. each tables in the data sheet is related by the integrity constraint.
How can i insert data in batch...from the excel ?
I have two cases
-----------------
CASE 1
i need to read and insert say 100 records from excel to oledb destination for 1 table
ie data sheet 1..and process the next 100 records after inserting the first 100 until the last data.
then insert the data from the next sheet in batch size say 100 records itself...and so on..
CASE 2
I need to process the first 100 data from sheet1 ie table1...insert the 100 data into ole db destination.
read the corresponding 100 records to the data ( ie corresponding to the id ) from sheet 2. and then insert it...
Then process the next 100 records from sheet1 and after completing it reading from sheet 2 and so on until all data is fetched and inserted..
Thanks,
Naveen

Do you need to insert data in batches or to read data in batches? to insert data in batches, you can just specify batch size in ole-db destination.

Reading data in batches would be more involved. there are several ways of doing this, here's one:

set up 4 variables, 3 integer and 1 string.

step 1: set up variables

variable 1: your string variable will be expression holding an excel range (something like "a$A" + (DT_STR,2,1252) @[User::start] + ":E" + (DT_STR,2,1252)( @[User::start]+ @[User::batch_size]-1) )

variable 2: int, holds row counts in a batch ( initial value anything greater than 0 )

variable 3: your main loop variable

variable 4: your batch size.

step 2: set up loop container

initexpression : variable 1 = 1

evalexpression : variable 2 !=0

assignexpression : variable 1 + variable 4

step 3: set up dataflow task

inside the loop, you set up excel source with data access mode from table name or view name variable, multicast it into rowcount transformation ( in which you'll set up your variable 2 ) and your destination.

for your case two, you can just add left join merge transformation.

navenis4u SSC-Addicted Points: 453 More actions · Answer 2

Thanks for the quick reply...

one issue that i have is that....my primary key column supplied in the excel file for each table is not real....the real ie will be generated while inserting into the table in the db...they passed that unreal value in order for to understand and relate the data.

ill explain with example...

i have 2 tables tbluser and tblUserCompany

data of tbluser will be as below..

userid username createddate

------ --------- ------------

1000 test 10/10/08

1001 test1 11/10/08

1002 test2 11/10/08

userid is the PK but its not a real value to insert...

the userid in a identity column so the real value will generate

while inserting to the table.

data for tblUserCompany is as below

CompanyId UserId CompanyName

---------- ------ --------------

100 1000 testcompany

101 1001 testcompany1

102 1002 testcompany2

here CompanyId in the PK(Identity column) and the UserId is the

foreignKey to the table tblUser. The real value for the user column should be taken from the tblUser userid....

So i need to make batch insertion by encoperating all these things..

ie i need to insert data of tblUser first to the table and then fetch its userid and combine it for the tblUserCompany UserId....and then insert that table..

Thanks,

Naveen

binko Default port Points: 1408 More actions · Answer 3

probably should've asked from the beginning: why are you trying to read data in batches of 100?

does the tblUser have a column to hold "unreal" PK from the spreadsheet (please post ddl or table structure for tbluser in database.).