sql 2000 activeX script
-
Hi There
I am new database programming, what I want to do is to create a DTS that runs a stored procedure, which returns records that are more than the 65536 excel row limit.I will then use ADO recordset andIwill then write the data to an excel workbook.
-
the easiest solution would be to convert your data to XML that is readable by Excel: http://www.microsoft.com/office/xml/default.mspx
CSV data elements can contain commas if the data is quoted:
item1,item2,"lastname, firstname",item4
-Mike Gercevich
-
Hi Mike
Thanks for your response, but it does not help me much as the data is held in a recordset, therefore I cannot quote it. The xml proposal you are making, I would rather not confuse myself any further as the article you have pointed me to does not shed a substantial amount of light either.
Thanks, I will inform you of my final solution!
-
here is the way i would implement it:
on point 1:
1. Create two connection one for the server , one for the XLS file
2. Create as many tables as workbook needed using the XLS connection and an execute sql task .
3. Use transformation task to query the maximum number of handled data from Connection 1 and set the destination to one of the tables ( workbook) created
if the number of tables is fixed and not high , you can "hard code" them by design within your package otherwise use a loop with an incremental Global Variable.
The same applies for the transformation tasks.
On point 2:
This prevent you from browsing a recordset and quoting your data. if transformation is needed then use the transformation tab from the transformation task component
On point 3:
Dig into it , it's the only way you can learn it . and have a look to http://www.sqldts.com
Happy pogramming,
PS: Beware of the large size of such files , especially when you export large record
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply