February 28, 2011 at 6:59 am
Hi,
I am having a problem with a task I have. I am trying to run a SSIS package as an SQL job. When I run the package through Visual studio all is going well, but when I am running the sql job it constantly crashes. Error message indicates a problem with the package's first task which is a vb script. What the script does is receive an excell file as input. The excel file posseses information in its name about the system user and the file's date. the script strips this info from the file's name and saves them as variables and renames the file to a name without these info. (e.g. if the file looks like that "filenameYYYYMMDDusername.xls it comes out as filename.xls)
so whenever I run the job I get this error message: "Executed as user: #####. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:38:08 Error: 2011-02-28 14:38:23.36 Code: 0x00000002 Source: create parameters Description: The script threw an exception: Cannot create a file when that file already exists. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:38:08 Finished: 14:38:23 Elapsed: 14.843 seconds. The package execution failed. The step failed."
The final file does not exist as the message says as it is created by the vb script when it finishes succesfully.
The even stranger thing is that if I delete the input file and try to run like that, the package fails as expected (no file to read from), but the sql job runs succesfully!
Does anyone have any idea why does that happen?
Thanks in advance!
March 1, 2011 at 2:29 am
I have an update on my problem. I discovered that the problem was not with the job itself but with the package. I tried to run it through Integration services but I recieved an error message that says my script task cannot run on this edition of Integration services and that it requires a higher version. Sql server (2005) is fully updated to service pack 4 and Visual studio is also the 2005 version.
March 1, 2011 at 2:48 am
couple of questions / comments
from what I can see, SP4 is a 'Release To Manufactures' version, so to quote MS - may not be fully ready for genereal use.
Secondly, is your VB script just stripping out the file name? If thats case, would I be correct to assume, you are doing something like, for each file in a directory, pick it up and execute the vb script...... if so could you probably acheive the same using an expression against the enumurator variable from the for each task.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 1, 2011 at 3:05 am
Hi Dave and thanks for the quick reply!
My script goes like that
For Each oFile In oFiles
inputFileName = CStr(Dts.Variables("inputFileName").Value)
If (Left(oFile.Name, Len(inputFileName)) = inputFileName) Then
oFileDate = oFile.Name.Substring(11, 8)
user = oFile.Name.Substring(19, 7)
Dts.Variables("user").Value = user
Dts.Variables("oFileDate").Value = oFileDate
oFile.MoveTo(strFolder & outputFileName)
bolFound = True
Dts.Variables("Continue").Value = True
End If
Next
I am suspecting that the problem occurs during the file move process somehow but I am not quite sure.
March 1, 2011 at 3:12 am
Plus regarding my second post the error message that I get is expected since I tried to run the package localy from my machine and not from the server directly. But since the jobb that I also created to run the package crashes something must be wrong with my script.
March 1, 2011 at 3:30 am
I can't see anything wrong with your vb script (not a VB expert mind, but it looks simple enough). have you tried to copy the file, rather than move it?
Also, tackling the same problem a different way, try using the following task to acheive what you are doing, rather than just the VB script (it's not going to change what your doing, but you may be able to see what's causing the issue easier as individual tasks would fails)
- For each loop task
- derived column task (to get just the file name)
- file system task (to move or copy the file)
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 1, 2011 at 3:35 am
Thanks for the suggestion Dave, I ll give it a try. I am not exactly an expert also so that's why I am struglling now 🙂
March 1, 2011 at 4:09 am
It finaly worked by changing move to copy and delete! Can't believe that it was something like that!
Thanks Dave!
March 1, 2011 at 4:15 am
Excellent.
I've clearly spent too long programming 😀
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply