I have a data pump task to transfer records from a table to a excel. I
want to automatically dump them to a second sheet, when the number of
records is greater than 64k (excel limit). Is there a way by which i
can dynamically create a new table in excel as well change my
destination to this new excel table, when number of records exceeds
64k.

Thanks
PM

One way that you can handle this is to use the DestinationObjectName,
FirstRow, and LastRow properties of the data pump task and a looping
structure. Hope this helps.

SQL Task - Get record count and store in global package variable

ActiveX Task - Use record count to determine number of Excel worksheets
needed and setup Excel file (see Example A below); set global variables for
sheet name, first row, last row

Dynamic Properties Task - Use global variables to set sheet name
(DestinationObjectName), first row (FirstRow), and last row (LastRow)
properties of data pump task

Data Pump Task - Copy data from source to destination Excel

ActiveX Task - Loop back to write data to next sheet (see Example B below)


EXAMPLE A:
Dim e_app
Dim e_wbook

Function Main()
Dim sFilename
Dim cnt
Dim loopMax

sFilename = DTSGlobalVariables("FullPath").Value

'**** Ensure output file does not exist
Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists( CStr(DTSGlobalVariables("FullPath")) ) = True Then
fso.DeleteFile CStr(DTSGlobalVariables("FullPath")), True
End If
Set fso = Nothing

DTSGlobalVariables("CurrentRow").Value=1
DTSGlobalVariables("SheetNumber").Value=1
DTSGlobalVariables("SheetName").Value = "Sheet" &
DTSGlobalVariables("SheetNumber").Value & "$"

DTSGlobalVariables("FirstRow").Value = 0
DTSGlobalVariables("LastRow").Value =
DTSGlobalVariables("ExcelMaxRows").Value

If DTSGlobalVariables("CurrentRowCount") >
DTSGlobalVariables("ExcelMaxRows") Then
loopMax=Int(DTSGlobalVariables("CurrentRowCount") /
DTSGlobalVariables("ExcelMaxRows"))
loopMax=loopMax + 1 'Add one loop to pick up the remainder rows
Else
loopMax=1
End If

DTSGlobalVariables("LoopMax")=loopMax
DTSGlobalVariables("LoopCount")=1

On Error Resume Next
' Create the Excel Object
Set e_app = CreateObject("Excel.Application")

' Open up the Excel Spreadsheet
Set e_wbook = e_app.Workbooks.Add

For cnt = 1 to CInt(DTSGlobalVariables("LoopMax"))
WriteColumnHeaders cnt
Next

'Save file
e_wbook.SaveAs sFilename

e_wbook.Close
e_app.Quit
Set e_wbook = Nothing
Set e_app = Nothing

Main = DTSTaskExecResult_Success
End Function


EXAMPLE B:
Function Main()
Dim stpEnterLoop
Dim stpDataPump
Dim stpContinue

set stpEnterLoop =
DTSGlobalVariables.Parent.Steps("DTSStep_DTSDynami cPropertiesTask_2")
set stpDataPump =
DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPu mpTask_1")
set stpContinue =
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActive ScriptTask_7")

If DTSGlobalVariables("LoopCount") = DTSGlobalVariables("LoopMax") Then
stpEnterLoop.DisableStep = True
stpDataPump.DisableStep = True

stpContinue.DisableStep = False
stpContinue.ExecutionStatus = DTSStepExecStat_Waiting
Else
stpContinue.DisableStep = True

stpDataPump.DisableStep = False
stpDataPump.ExecutionStatus = DTSStepExecStat_Waiting
stpEnterLoop.DisableStep = False
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

DTSGlobalVariables("LoopCount")=DTSGlobalVariables ("LoopCount") + 1
DTSGlobalVariables("SheetName")="Sheet" +
CStr(DTSGlobalVariables("LoopCount")) & "$"

DTSGlobalVariables("FirstRow")=DTSGlobalVariables( "LastRow") + 1
DTSGlobalVariables("LastRow")=DTSGlobalVariables(" FirstRow") +
DTSGlobalVariables("ExcelMaxRows") - 1
End If

Main = DTSTaskExecResult_Success
End Function



"pm" wrote:

Probably the easiest way to do this would be to have your
SourceSQLStatement be something like

SELECT TOP 64000 ........ FROM <TABLE>
WHERE id > (The Max id of the last export)

You keep a table which maintains the id of the last exported row at each
loop.

You will be looping through your source

At each iteration of the loop you check that there are rows to export.
If not you exit gracefully.


The Excel DestinationObjectName will need to change on each loop and you
will have to issue a CREATE TABLE statement against the Excel connection
on each iteration of the loop also.

It would look something similar to this


Are there rows to export (Beginning of loop)


Give it a try.

Allan






"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote