November 21, 2006 at 4:37 pm
Hi,
I have a DTS package wherein, ActiveX will generate a unique MS-Execel file name everyday. When I use transformation, I am getting Error Msg:
The Microsfot Jet database engine could not find the object "IneffectiveWeekly$". Make sure the object exists and that you spell its name and the path name correctly.
When I go to Destination @ Transform Data Task Properties and after creating table name, DTS package works fine.
Here I have to create different MS-Execel data file everyday and store data into this file.
How can I solve this problem? Any idea in this direction is also helpful to me. Thanks in advance.
November 22, 2006 at 3:22 am
You could export the info into a spreadsheet then use an ActiveX script to rename the file. This means that the file is always available for the package.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
November 22, 2006 at 7:00 am
I created a second DTS package to do this. I had to because of a bug in Excel where the spreadsheet wouldn't save if I put this step into the data collection package.
In short, I dropped an activex object with the following code. You can modify to meet your needs.
The data is written to a temporary workbook and then opened here. Once opened, I save it to the local drive and name it todays date.xls. I also define the date without the /'s in it so that I don't get any file errors. This works well for the two or three daily/weekly/monthly management reports I have to generate.
'**********************************************************************
' Visual Basic ActiveX Script
'***********************************************************************
Function Main()
Dim xlApp
sDate = Replace(Date(), "/", "")
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open "I:\Templates\temp.xls"
xlApp.ActiveWorkBook.SaveAs "I:\Public\Reports\" & sDate & ".xls"
xlApp.ActiveWorkbook.Close True
xlApp.Quit
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
November 22, 2006 at 7:50 am
You don't need a second package.
I solved this kind of problem by creating in the working folder a sample/source/structure excel file (i.e. _sample_file.xls) that has the workbook created with the column names in place but with no data. Every time the package is run an active x takes this file and copies it with the new name that is generated using a timestamp (i.e. file_2006-11-22.xls). You have to use a Dynamic Properties task to assigend the new name to the output excel connection.
The difference between running the dts in the designer in EM and in a job is that in the designer the excel file is created on the spot when you define the transformation while in the job the file has to be already created. The above procedure solves this.
You have to think about the excel file as a database and about the workbook as the table. You cannot use a database in the DTS if it doesn't exist already. In the case of excel the DTS can create it in design mode but you can't do this with an SQL database for example. In the running mod (job) neither can be created on the spot.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
November 22, 2006 at 8:14 pm
Thanks a lot for your valuable input. It is working now. Thanks once again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply