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?
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?
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?
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Rejo
this is very good site which explains all about SSIS
http://www.sqlis.com/
from MSDN
http://msdn2.microsoft.com/en-us/library/ms141209.aspx
Some other links
http://blogs.3sharp.com/Blog/grege/archive/2006/07/17/1546.aspx
http://www.cubido.at/Blog/tabid/176/EntryID/79/Default.aspx
Rejo