DTS Global Variable for Export to Excel

[JefB]

I have a DTS set up that exports a SQL table to an Excel file.

By itself it works fine, however I am trying to use a Global Variable to change the name of the '.xls' table.

This is my code (in a stored procedure):
exec master..xp_cmdshell 'DTSRUN /S ServName /E /N ENN_Var_DTS /A DstTbl:8 = \\ServName\XFER\Test2.xls"'

The Error Msg I get (via Query Analyzer) is:
...
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
...
Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01)
Error string: 'C:\Program Files\Common Files\System\Mapi\1033\NT\ \ServName\XFER\Test2.xls"' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

The DstTbl is set on the DTSRun "Microsoft Excel 97-2000" connection DataSource Property Name. I have also tried pointing the variable to Property Name objects in the Task and Step areas, with no success.

Am I placing the variable in the correct place or am I missing something?

JefB

 

[jabrony76]

JefB,

Not sure why your particular package is failing but I can offer some advice on how i've gotten around this in the past.

I would have the DTS export to a standard file name "Template.xls" and after the transform data task, I run an ActiveX script that renames the file to the name passed in in the Global Variable.

The code below does this and adds a date stamp to the file name.

Hope this helps,
Andy

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

Function Main()

	On Error Resume Next
	Dim fso, filespec, baseDir, inFile, outFile, today, pname

	today = now()
	pname = DTSGlobalVariables("gvPayorName").Value
	baseDir = "c:\"
        inFile = "Template.txt"

	' Build target filename
	outFile = pname & "_" & datepart("yyyy", today) & right("00" & datepart("m", today), 2) & right("00" & datepart("d", today), 2)  & ".xls"

	filespec = baseDir & inFile
	Set fso = CreateObject("Scripting.FileSystemObject")
	If (fso.FileExists(filespec)) Then
		' Source file exists, check if target file already exists
		Set MyFile = fso.GetFile(filespec)
		MyFile.Copy (baseDir & outFile)

		' Check if Copy went OK
		If Err = 0 Then
			' File Copied OK, so delete original
			MyFile.Delete
			Main = DTSTaskExecResult_Success
		Else
			' Unable to copy source file to target
			Main = DTSTaskExecResult_Failure
		End If
	Else
		Main = DTSTaskExecResult_Failure
	End If


	Main = DTSTaskExecResult_Success
End Function

 

[JefB]

Thanks, I'll give it a try.

JefB