Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 2:55 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:15 PM
Points: 292, Visits: 767
Terrific article. I used it with some modifications to realize a huge time-saving project.
Many thanks!

- Simon D
Post #674745
Posted Thursday, March 12, 2009 5:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 30, 2009 8:07 PM
Points: 15, Visits: 40
Glad it was useful for you!
Post #674853
Posted Friday, October 16, 2009 9:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #804317
Posted Friday, October 16, 2009 10:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 7, 2012 9:23 AM
Points: 304, Visits: 716
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...
Post #804359
Posted Friday, October 16, 2009 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 11:59 AM
Points: 1, Visits: 1
Nice article. Does this work with the 64 bit Excel driver?
Post #804371
Posted Friday, October 16, 2009 11:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 30, 2010 6:33 AM
Points: 17, Visits: 63
nice ressurection of one of my more favorite bookmarked discussions. :)
Post #804394
Posted Sunday, October 25, 2009 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 30, 2009 9:55 PM
Points: 3, Visits: 27
Hi,

Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in article
Post #808488
Posted Sunday, October 25, 2009 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 30, 2009 9:55 PM
Points: 3, Visits: 27
Hi,

Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in article
Post #808491
Posted Tuesday, October 27, 2009 3:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 30, 2009 9:55 PM
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


  Post Attachments 
ExcelPush.zip (3 views, 482.10 KB)
ExcelTest_Revised.zip (2 views, 7.61 KB)
Post #809166
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse