| Author |
Topic |
|
bernie.mac
Starting Member
6 Posts |
 Posted - 2008-01-11 : 18:44:42
|
| There is a DTS package that fails to run for one user. The DTS runs for other users. The DTS even runs when the user signs onto another Server. The error message that the user gets is the following:The number of failing rows exceeds the maximum specified.TransformCopy 'DTSTransformation_1' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'F1' (DBTYPE_WSTR), destination column 'PostDt'(DBTYPE_DBTIMESTAMP))As previously stated, if the user signs onto another Server with SQL Server on it, the DTS performs a successful transformation.Has anyone seen this error. I've been looking over all roles and security and have not been able to find any differences between user setup.Thanks in advance. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-01-11 : 19:07:11
|
That error is specific to the data transformation task.If it works on another server, you have to remember that the DTS package is stored on the server...it is possible that the package on the server producing the error doesn't have the same transformation.Specifically, that error is trying to put a non-date string into a datetime field.I would be looking at the DTS package on the failing server, not the users. It is not a user specific error, it is a DTS package/DTS Transformation specific error. Specifically, the job executes fine for the user, however, it stops running due to the error.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
bernie.mac
Starting Member
6 Posts |
Posted - 2008-01-14 : 15:28:14
|
| Thanks for the insight dataguru1971.I took screenshots of all the tabs of the package and compared it to the machine that is remotely connecting to the server. I thought that we were successfully running the package on another server but I was mistaken. The package resides on one server. The package runs successfully when logged on to the server or when one user connects to SQL server through Enterprise Manager. However, if I attempt to connect to SQL server through Enterprise Manager, I get the error. At first we thought it was MSOffice because the source file is a XLS-2003 file and I have office 2007. However, we installed 2007 on the other user's workstation and they were still able to connect to the server and run the DTS. I'm still seeking more help. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-01-14 : 16:41:43
|
As I said, this doesn't seem at all like a user issue. Double check the package transformation task SOURCR file location. it is important to make sure it is pointed to the correct file.If you execute the job and get that error, it is possible it is NOT looking at the correct file location.Make sure you have access to the directory (from your local PC) in exactly the letter and path identified in the transformation task properties.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
bernie.mac
Starting Member
6 Posts |
Posted - 2008-01-14 : 16:49:55
|
| The source file is reached on all machines. I can reach the file from the server, my workstation or another workstation. I can open the file, modify it, and save/close the file.I think that I would get an error trying to open the file rather on the transformation - but I'm still new to this.Any other suggestions.? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-01-14 : 17:55:50
|
The error is identifying a problem in transforming a column...it is in the transformation. i have seen the error many times.It looks like the 1st column transformation is failing, and that the task was set up using the wizard which would not necessarily handle exceptions. In many of mine, I transform using the VBScript and validate values on the way in using isDate() or isNumeric() functions around the date columns to account for nulls and/or invalid dates that may occur on the import.This ALSO happens if the file it is reading is not the correct source file. In EM, open the transformation task..preview the source file etc. Go to the transformation task and test it...I would bet the problem is there somewhere
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
bernie.mac
Starting Member
6 Posts |
Posted - 2008-01-14 : 18:34:49
|
| I have narrrowed it down further with dataguru1971's help. When I preview the transformation on my workstation or the server - we get a text value in the f1 field - which is supposed to be transformed to a datefield - thus the error.However, when I preview the source file on the workstations that the DTS runs successfully on, it is blank. We checked the mappings and that is the same on all workstations. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-01-14 : 21:05:20
|
So previewing the exact same source file, with the exact same DTS package on multiple computers produces different previews?It still sounds like there is an issue with the file location to me. I have never had a file produce a different preview simply by accessing it from a different machine. Theoretically the DTS package is identical in all instances of Enterprise Manager, so the only variable would be the source file itself.Is the file on a network directory where all machines have direct access to it? Is the file location variable--does it change?If the location or file name changes, you have to change the source file in TWO locations to make sure it works properly (especially if the "old" location is still valid).You have to change it in the source connection AND in the DTS Transform task by selecting the proper file name (if you are not assigning the location using the Dynamic Properties Task...). I know I have said this before...but this sounds to me like two different files.I highly doubt that it would be two different versions of DTS packages....if the source is Excel 2003, is excel 2003 (or higher) installed on all machines
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
DTS Package Fails for only user