Export to dynamicly created excel file?

[BuGlen]

I have a DTS package that uses a query to export data to an Excel file. Everything works fine as long as the Excel file exists and the column names are pre-defined. What I would like to do is be able to create the Excel file during the DTS execution and define the columns based on the columns returned by the source query.

Has anybody done this?

I've created an ActiveX Script task to set the Excel file path and name based on the date the package is executed, and set the destination connection accordingly. (ex. 'Export 2004.03.30') Now I just need to know how to programatically do what the DTS designer does as your defining your destination connection file (via the create table query).

Any help on this would be greatly appreciated.

- Glen

 

[BuGlen]

Maybe there's another approach.

When you visually create the Transform Data task between your data source and your destination (Excel), if the file does not exist, DTS will create the Excel file with a worksheet and the appropriate columns for you.

Does anyone know if there is a way to envoke this same method via ActiveX script or other methods?

- Glen

 

[gordonsj]

Sorry can't answer your own query but I am interested to know how you used an ActiveX script to dynamically name the export file based on the datetime of execution of the DTS package.

Cheers :O)

 

[BuGlen]

Hi Gordon,

Well, first you create a global variable in the package properties to hold the full path of the excel file. I created a global variable called 'XLFullPath', and another to hold the actual path of the export files called 'XLPath'.

Package Globals:
XLFullPath = ''
XLPath = '\\MyServer\MyPath\'

Then you create an ActiveX Script task to set the XLFullPath global variable with the appropriate file name, and copy the template to the new file name:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
	
	Dim strFileName, strTemplateFile
	Dim fso

	' Set the template file and file object references
	strTemplateFile = DTSGlobalVariables("XLPath").Value & "DTS_Template.xls"
	set fso = CreateObject("Scripting.FileSystemObject")	

	' Build the report filename based on the current date
	strFileName = "DTS_Export - "
	strFileName = strFileName & year(now()) & "-"
	if month(now()) < 10 then strFileName = strFileName & "0"
	strFileName = strFileName & month(now()) & "-"
	if day(now()) < 10 then strFileName = strFileName & "0"
	strFileName = strFileName & day(now()) & ".xls"

	DTSGlobalVariables("XLFullPath").Value = _
		DTSGlobalVariables("XLPath").Value & strFileName

	' Copy the template to the new filename
	fso.CopyFile strTemplateFile, DTSGlobalVariables("XLFullPath").Value, True

	' Clear memory and return
	set fso = nothing
	Main = DTSTaskExecResult_Success

End Function

Finally, you create a Dynamic Properties Task to assign the XLFullPath global variable to the Excel connection Data Source property. The workflow of all the tasks is set like this:

ActiveX Script Task -> [on success] -> Dynamic Properties Task -> [on success] -> Source Data Connection -> Transform Data Task -> Destination Connection (Excel)

Hope this helps.

- Glen

Know thy data.

 

[gordonsj]

Hi Glen,

Worked a treat thanks very much !!

Simon )

 

[SBendBuckeye]

Hello,

I'm brand new to DTS so I didn't follow much of this thread but at least here is a simpler way to create your file name:

' Build the report filename based on the current date
strFileName = "DTS_Export - "
strFileName = strFileName & Format(Now(), "yyyy-mm-dd")
strFileName = strFileName & ".xls"


Have a great day!

j2consulting@yahoo.com