Export a series of queries to separate excel files using DTS

• Chris Horsley

  SSC Journeyman

  Points: 95

  More actions

  August 19, 2004 at 1:51 pm

  #181495

  I'm trying to find away to a export a series of queries to separate excel files. The only difference between the queries is an EmployeeID number. I've set up a DTS package that contains each query that I can run individually, but I'd like to run one DTS package that saves each excel file separately based on the EmployeeID.

  Here is my current Query:  'E705327' is the EmployeeID

  SELECT    

  Sales.Quarter02.[ProjectName ],

  Sales.Quarter02.[ReportNo ],

  Sales.Quarter02.[Valuation ],

  Sales.Quarter02.[Stage ],

  Sales.Quarter02.[ProjectType ],

  Sales.Quarter02.[Address ],

  Sales.Quarter02.[City ],

  Sales.Quarter02.[County ],

  Sales.Quarter02.[State ],

  Sales.Quarter02.[ZipCode ],

  Sales.Quarter02.[GC ],

  Sales.Quarter02.[Architect ],

  Sales.Quarter02.[Consultant ],

  Sales.Quarter02.[ConsultingEngineer ],

  Sales.Quarter02.[ElectricalEngineer ],

  Sales.Quarter02.[MechanicalEngineer ],

  Sales.Quarter02.[Owner ]

  FROM Sales.SalesRep INNER JOIN

  Sales.CountyEID INNER JOIN

  Sales.Quarter02 ON Sales.CountyEID.State = Sales.Quarter02.[State ] AND  Sales.CountyEID.County = Sales.Quarter02.[County ] OR

  Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[City ] ON                    

  Sales.SalesRep.EID = Sales.CountyEID.Rep1

  WHERE     (Sales.SalesRep.EID = 'E705327')

  ORDER BY Sales.Quarter02.[Valuation ] DESC

• Corie Curcillo

  SSC-Addicted

  Points: 418

  More actions

  August 20, 2004 at 8:40 am

  #520000

  Using either a WHILE LOOP or CURSOR...

  build your list of unique EmployeeIDs.

  Looping/Cursoring through your list of EmployeeIDs, Updating value of @EmployeeID and @MyExcelFileName

  bcp " SELECT    

  Sales.Quarter02.[ProjectName ],

  Sales.Quarter02.[ReportNo ],

  Sales.Quarter02.[Valuation ],

  Sales.Quarter02.[Stage ],

  Sales.Quarter02.[ProjectType ],

  Sales.Quarter02.[Address ],

  Sales.Quarter02.[City ],

  Sales.Quarter02.[County ],

  Sales.Quarter02.[State ],

  Sales.Quarter02.[ZipCode ],

  Sales.Quarter02.[GC ],

  Sales.Quarter02.[Architect ],

  Sales.Quarter02.[Consultant ],

  Sales.Quarter02.[ConsultingEngineer ],

  Sales.Quarter02.[ElectricalEngineer ],

  Sales.Quarter02.[MechanicalEngineer ],

  Sales.Quarter02.[Owner ]

  FROM Sales.SalesRep INNER JOIN

  Sales.CountyEID INNER JOIN

  Sales.Quarter02 ON Sales.CountyEID.State = Sales.Quarter02.[State ] AND  Sales.CountyEID.County = Sales.Quarter02.[County ] OR

  Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[City ] ON                    

  Sales.SalesRep.EID = Sales.CountyEID.Rep1

  WHERE     (Sales.SalesRep.EID = @EmployeeID)

  ORDER BY Sales.Quarter02.[Valuation ] DESC

  " queryout @MyExcelFileName

  Corie Curcillo
  MCT, MCDBA, MCSD