Export queries to individual Excel files
-
I set up a DTS package using the export wizard to perform a multi table query that I am then exporting to Excel. The query is being sorted by the EmployeeID. . I would like to find a way to replace the reference to a specific EmployeeID (inside of the query) with a global variable and automatically export using the EmployeeID(global variable) as the name of the Excel file.
I set up a global variable named gEID and am populating the global variable with a query that extracts just the EmployeeIDs from the employees table. Can someone please suggest a way to loop through each EmployeeID as each query is run and Excel file is exported?
Have seen several articles concerning looping but they usually applied to importing data not exporting to individual files.
-
See this article for how to get a loop in a dts package:
http://www.sqljunkies.com/How%20To/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk
You need to find a way of bringing each id from the table into a variable to get your situation to work. Once you do that, you can save the filename with the name of the employee+id...
Sorry, can't help you further. I always use visual basic to do jobs such as these.
------------------------------
The Users are always right - when I'm not wrong! -
Chris.
Let me know if you still want to do this. I have found a way and have it working on my server for my own purposes.
It is a bit copmplicated to set up so I will not bother typing an instruction unless you are really serious.
Basically, you need to use global variables to capture the column data in a recordset to use in the sql where clause, then cycle through all the records and:
1. Create the spreadsheet and save it with the unique name (table.column.value)
2. Run the data pump task to export the data into the spreadsheet required.
I then get the above two tasks to loop until the end of the recordset returned.
------------------------------
The Users are always right - when I'm not wrong! -
You're a star mate. Yes I do still need to do it. If you can list the steps etc. I would be forever greatful.
-
Here goes. I will try to keep it as brief as possible. Assuming you have knowledge of creating input and output variables in dts packages. if not see here for reference - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_task_722b.asp
1. Create a connection to the db. Name does not matter.
2. Create global vriables
gvID integer
gvLoopCount integer
fileName string
3.Create an active x task called 'Loop'. Code is
------------------------------------------------
'**********************************************************************
' Visual Basic ActiveX Script
' Author: Jon Stokes
' Created: 10th Sep 2004
' Purpose: To loop through each record and call the excel creation task followed by the data pump task
' Revision History:
'************************************************************************
Function Main()
Dim pkg
Dim stpbegin
Dim LoopCount
Dim stpNext
Dim cnter
Dim RS
Set RS = CreateObject("ADODB.Recordset")
Set RS = DTSGlobalVariables("ID").value
'set the id for the where clause in the select statement
If DTSGlobalVariables("gvLoopCount").value < RS.RecordCount Then 'loop until all records cycled through
DTSGlobalVariables("gvID").value = RS.Fields("id").value 'Set where clause for each record
DTSGlobalVariables("gvLoopCount").value = DTSGlobalVariables("gvLoopCount").value + 1 ' Increment count by one ach time
' Execute package steps in turn
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSDataPumpTask_1") 'Create separate excel files
set stpNext = pkg.Steps("DTSStep_DTSActiveScriptTask_3") 'Export data to each
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
stpNext.ExecutionStatus = DTSStepExecStat_Waiting
RS.MoveNext 'Move to the next record
End If
Main = DTSTaskExecResult_Success 'End on success
End Function
--------------------------------------------------------------------
4.Create an active x task called 'Reset Counters'. Code is
'**********************************************************************
' Visual Basic ActiveX Script
' Author: Jon Stokes
' Created: 10th Sep 2004
' Purpose: To reset global variables before start. Effectively clears out last run.
' Revision History:
'************************************************************************
Function Main()
'Set global variaboles to zero
DTSGlobalVariables("gvLoopCount").value = 0
DTSGlobalVariables("gvID").value = 0
Main = DTSTaskExecResult_Success 'End on success
End Function
--------------------------------------------------------------------
5. Create an active x task called 'Create Excel'. Code is
--------------------------------------------------------------------
'**********************************************************************
' Visual Basic ActiveX Script
' Author: Jon Stokes
' Created: 10th Sep 2004
' Purpose: To create a separate excel file for each record named by column used to cycle through records
' Revision History:
'************************************************************************
Function Main()
' Initialise dimensions
Dim appExcel
Dim newBook
Dim oSheet
Dim oPackage
Dim oConn
'Set excel objects
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
' Populate heading columns for mapping purposes and display in excel
oSheet.Range("A1").Value = "Col1"
oSheet.Range("B1").Value = "Col2"
oSheet.Range("C1").Value = "Col3"
oSheet.Range("D1").Value = "Col4"
'Dynamically specify the name of the new Excel file to be created and exported to
DTSGlobalVariables("fileName").Value = "C:\ignore\" & DTSGlobalVariables("gvID").value & ".xls"
With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
set oPackage = DTSGlobalVariables.parent
'connection 2 is to the Excel file
set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("fileName").Value
'Destroy objects
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success 'End on success
End Function
--------------------------------------------------------------------
6. Create an execute sql task called 'Where Clause'. This is used to bring the recordset id into the output variable so that you can loop through each sql statement in the where clause and use the id as the name of the excel file. Code is -
/* Define where clause for recordset to be exported and the table
column to be used to step through each record */
select ID from tbl where id < 10 order by id --- test 10 rows only
7. In the same execute sql task create an output variable called 'ID' and assign it to the id. See instructions at top hyperlink for step by step.
8. Create the destination link to the excel file. Attach it to a blank file called test.xls in the same directory that you are looking to create the new excel files in.
Now for the steps. From left to right.
Where Clause - On completion -
Reset Counters - On completion -
Create Excel - On Success -
DB
9. Now we have to link this to the excel spreadsheet destination. Create a transfor data task between the two.
10.Type in the sql query. This will be used to return all the cloumns that you want in the excel files. Code is -
----------------------------------------
select * from tbl where id = ?
----------------------------------------
Click on parameters to populate the ? mark. Select the variable gvID
11. Click on transformation task tab and then select edit followed by properties. Will bring up an activex task script. Type in code -
----------------------------------------------------------
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
' SAVE DESTINATION XCEL FILE
'DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000").DataSource = "C:\" & DTSGlobalVariables("count").value & ".xls"
' PERFORM THE COPY
DTSDestination("Col1") = DTSSource("ID")
DTSDestination("Col2") = DTSSource("nothercolumn1")
DTSDestination("Col3") = DTSSource("nothercolumn2")
DTSDestination("Col4") = DTSSource("nothercolumn3")
Main = DTSTransformStat_OK
End Function
----------------------------------------------------------
Just map the destination columns in the excel files to the db columns. Note that I name the columns when I create the excel file in the code. I named mine "Col1" etc.
12. Last link is from Create Excel to Loop on success.
That's it. Stores it in folder "c:\ignore".
Screenshot and good luck. Any probs, come back to this post.
------------------------------
The Users are always right - when I'm not wrong!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply