| Author |
Topic |
|
folettra
Starting Member
15 Posts |
 Posted - 2013-02-05 : 10:20:40
|
| The problem I have is completely random and occurs in different Data pump tasks and different DTS packages.I am using SQL 2000 and I have also used SQL 2008 SSIS.All the work flows proceede on success to the next task.I am sending data from the SQL database to an Excel worksheet and the transformation simply does not write to the Excel tab. I get no error and the DTS package continues on to the next task.I recreated the packege in SSIS and the same thing happened. In SSIS the log files shows that the data pump taks wrote 11 rows of data to the Excel tab but no data was acutally written to the tab. Again this is random and happens on different tasks and different DTS packages. The packages run all the way through with no problems in most cases.Has anyone seen this type of problem before? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 10:59:25
|
| are you sure you're using correct provider for excel connection?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-05 : 11:33:37
|
| In SQL 2000 I am using Microsoft Excel 97 - 2000. we have Excel 2003 installed on the server. This problem just started happening a while back. one thing we did was to reinstall SQL 2000 as a named instance. and then install SQL 2008.we kept the DTS package on the 2000 instance and converted all the other databases to 2008. the DTS packages run in 2000 but the trasnformations all point to the 2008 databases. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 12:02:09
|
| are you sure installation of sql 2008 didn't cause any changes in provider version for excel?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-05 : 12:17:07
|
| how would I check to see if the SQL 2008 install changed the provier version of Excel? |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-05 : 12:23:35
|
| Keep in mind that I created the same packege in 2008 SSIS and it did the same thing. I am using Microsoft Excel 97 -2003 for the connection in SSIS. the log file showed that 11 rows of data were written to excel but in fact nothing was written to excel. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 12:34:32
|
| are you sure you're looking at right excel file? Are you setting connection string by means of an expression or is it static?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-05 : 12:50:16
|
| it is a static connection. |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-06 : 09:38:39
|
| let me also state that the DTS package has several transfomations that populate different tabs in the Excel worksheet. when the job runs several tabs will get populated and one or two may not. it is not the same tabs that fail each time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 23:03:53
|
| oh...I seeare you using activex script for that? have you added code for exception handling in it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
folettra
Starting Member
15 Posts |
Posted - 2013-02-07 : 09:33:36
|
| No, I do have activex scripts in there to set date variables and such but they do not seem to be part of this problem. as for the exception handling. the job does not fail and the task reports success so it may not create an exception file. I will try it to see what happens.in the DTS package I have a server object which points to the data_warehouse, an excel objects with a connection to the excel workbook, I have Execute SQL Task objects that use the connection to the Excel workbook. And then I have transformations between the server object and the excel object.First the Execute SQL Task objects will drop the tab and the second will recreate the tab in Excel using code like this. The tab Closed_Mac_Data exists in the Excel workbook and when the drop table runs it will drop all the current data in that tab and then the next step runs to recreate the headers and the named range in the same tab.Drop TABLE `Closed_Mac_Data`CREATE TABLE `Closed_Mac_Data` (`COMPANY` VarChar (100) , `INCIDENT_NUMBER` VarChar (100) , `PRODUCT_CATEGORIZATION_TIER_1` VarChar (200) , `PRODUCT_CATEGORIZATION_TIER_2` VarChar (200) , `PRODUCT_CATEGORIZATION_TIER_3` VarChar (200) , `PRODUCT_NAME` LongText )Then the transformation will be run between the server object and the Excel object Sample SQL:Select Company, Incident_Number,Product_Categorization_Tier_1, Product_Categorization_Tier_2, Product_Categorization_Tier_3,Product_NameFrom dbo.tbl_IncidentsWhere company = ‘companynamne’And closed_date >= to getdate() -1And closed_date < getdate()Each one of these tasks has a workflow on it set to success so they will not continue the package if they fail. Sometimes the drop and crecreate steps are skipped so that when the transformation runs it will append new data to the data that is already in the tab. Other times the drop and recreate will run fine and the transformation will run but just not send any date to the tab and move on to the next transformation. There is no error and the package does not stop. The log files report success on each task.When I created this same package in SSIS the logging had more information than in SQL 2000. It even stated that 11 rows were written to the table when in fact no rows were written. The SSIS package did not fail either. |
|
|
|
|
Data pump task fails to write data to Excel