SSIS Active X

[sendba]

Hi all,

I am trying to migrate a SQL Server 2000 DTS Package to Sql Server 2005 SSIS.
I have a DTS which contains active X script which creates a xls file with columns.

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

Function Main()
Dim oExcel
Dim oBook
Dim oSheet
Dim objg

set objg = CreateObject("Scripting.FileSystemObject")
'Set File name and Folder Information
OutFname= "C:\DTS_SSIS_OUTPUT\test.xls"
If objg.fileExists(OutFname) Then
objg.DeleteFile (OutFname)
End If

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Name = "Daily_Sales"

oSheet.Range("A1").Value = "DBA"
oSheet.Range("B1").Value = "Postal_Code"
oSheet.Range("C1").Value = "Load Count"
oSheet.Range("D1").Value = "Load Amount"
oSheet.Range("E1").Value = "Reload Count"
oSheet.Range("F1").Value = "Reload Amount"

oBook.SaveAs "C:\DTS_SSIS_OUTPUT\test.xls"

oBook.Close

Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

I am getting the following error whenever i execute the script.


SSIS package "Test.dtsx" starting.
Error: 0xC0048006 at Create Test Excel File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x032C209C.
Task failed: Create testExcel File
SSIS package "test.dtsx" finished: Success.

Please let me know if there is any way to correct this issue

Thanks

 

[MDXer]

Not sure about this issue but it may be worth rewriting the task in a Script Task rather than the Active X Task. The active X task will be removed in a not so distant future version of SQL. It was brought in to allow for an easier migration but from what I have seen on various forums I wouldn't exactly say it allows easy migration.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck