January 15, 2010 at 10:13 am
Hello,
I need to export data from SQL Server 2000 to an Excel file daily. I'd like the file to be named "file YYYYMMDD.xls".
The ActiveX script task below works the first time, but I get an error every time the job runs after that. The error is "The Microsoft Jet database engine could not find the object 'New_table$'. Make sure the object exists and that you spell its name and the path name correctly."
Can anyone please help? Thanks.
Function Main()
mydate =now()
sFilename = "file path" & Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".xls"
Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
February 1, 2012 at 10:23 am
Really no reply? I am getting the same error message. If you got this figured out how does it work?
Thanks
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
February 2, 2012 at 1:21 pm
Hi,
I did figure it out. You need to have a dynamic properties task after your activex script task. The dynamic properties task creates the table every time the job is run. Take a look at the attached schreenshot and let me know if you're still having problems.
Teddy
February 2, 2012 at 2:27 pm
Thank you so much! 🙂
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply