SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server


How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server

Author
Message
Simon E Doubt
Simon E Doubt
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 895
Terrific article. I used it with some modifications to realize a huge time-saving project.
Many thanks!

- Simon D
mdeschen1
mdeschen1
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 40
Glad it was useful for you!
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5789 Visits: 1619
Excellent and informative article. I, though using SSIS for last couple of years but still love the simple DTS designer tool of SQL Server 2000 version than the complicated SSIS of 2005/2008. I agree, with SSIS we can accomplish more than what one can with DTS. But still, DTS is DTS.

Thanks for the article.

SQL DBA.
blandry
blandry
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 Visits: 723
Good, well-written article and having worked with SSIS, not too hard to understand and follow. I suppose if you had to generate this kind of Excel output on a regular basis, this code would be most helpful.

However, it also highlights the often painful complexity of SSIS to the uninitiated and one must consider whether simply running a bunch of queries then copying and pasting the results - or - simply running the queries from the excel side are not more overall time-efficient, less tedious and complex approaches.

Still, excellent and creative work, nicely documented.

There's no such thing as dumb questions, only poorly thought-out answers...
macrochaotic
macrochaotic
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 15
Nice article. Does this work with the 64 bit Excel driver?
quayludious
quayludious
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 63
nice ressurection of one of my more favorite bookmarked discussions. Smile
shekhardiptiman
shekhardiptiman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 27
Hi,

Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in article
shekhardiptiman
shekhardiptiman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 27
Hi,

Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in article
shekhardiptiman
shekhardiptiman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 27
Hello to all Gurus,

I am a newbie and need a step by step help. I am attaching herewith sample .dtsx and .bak ( both zipped separately) from my environment.

The scenario i was trying is given below :
Tables in focus dbo.CustTable & dbo.CustGroup
both tables are linked with the field CustGroup.
There are different working company for an application instance which is defined as "dataareaId" in each table.

I would try to create one file dynamically for each distinct dataareaid in CustTable and within a workbook create worksheets for a distinct CustGroup and each worksheet will then have the CustTable data for that CustGroup.

I have tried to follow the article but i am stuck at the excel destination . It says "the excel file name is not provided".

Help me
There would be a need to change the connection to use the attached the database instead of the ince in the .dtsx file.

I have now attached the updated files for your review. Pls help
Attachments
ExcelPush.zip (12 views, 482.00 KB)
ExcelTest_Revised.zip (19 views, 7.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search