Link to home
Start Free TrialLog in
Avatar of ewesley
ewesley

asked on

load bulk data to excel using vb.net

Hi

I am using VS2005 with asp.net and vb.net for back end code. I need to export huge datasets of upto 3000 lines from SQL2005 to Excel2003 to create reports. I have a method that is working now but because its writing the data set to excel row-by-row it takes quite a bit of time. Is there a way i can load it faster instead of going row-by-row?
Avatar of Rejojohny
Rejojohny
Flag of United States of America image

Avatar of ewesley
ewesley

ASKER

Thanks Rejo,

I have looked through this stuff and have been able to test an SSIS project when loading data from excel to sql2005 successfully,

However when moving the data from sql2005 to excel i have a couple of problems, (forgive me if they are dumb as i am new to this)

1. What exactly is supposed to be set as the string value in the RemoteFileUrl Variable?
2. If this works successfully, how would i link it to my asp.net web project from the intergration services project?
Glad that you got that working .. what you might have noticed is the speed at which your export works now ..
1. I am not sure about what exactly you mean here
2. You can trigger SSIS objects from ASP.net .. i am sure you will find samples in the SQLis.com site that I have given above .. do let me know if you are able to get to any samples and I will try and find them for you ..

Rejo
Avatar of ewesley

ASKER

Rejo,

WHile the SSIS stuff is very intresting i have not been able to find a way to use it in my solution.

I am using dynamic SQL and my select statements have both the columns and tablenames as parameters.

select [column names as arraylist]
from @schema.@tablename

Is there a way to use SSIS in this manner?

I have also not see how i can use the SSIS project from my vs2005 application though i have not done much research on this as yet.
ASKER CERTIFIED SOLUTION
Avatar of Rejojohny
Rejojohny
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It could be done in SQL Server 2000...???
Urbizagastegui, yes this can be done in SQL 2000 too .. its called DTS .. try sqldts.com for more info ..

Rejo