February 20, 2018 at 9:10 am
i have an SSIS package that takes a image in my database and uploads it into a network location that is called by a SQL Agent Job and it seems to crash out when ever the user supplies a file name with a space in it. I am not sure why the job would crash with a space when the network location accepts files with spaces. Below is the error that I am getting and if I rerun the process and replace "test test test" with "testtesttest" it works as expected. Is there something that I am missing or doing wrong ?
Message
Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:29:24 AM Error: 2018-02-20 09:29:24.49 Code: 0xC020207F Source: Data Flow Task Export Column [2]
Description: The file name "\\HYPERV-APPSVR1\NFWImages\Service\300_497292\test test test.jpg" is not valid. The file name is a device or contains invalid characters.
End Error Error: 2018-02-20 09:29:24.49 Code: 0xC0209029 Source: Data Flow Task Export Column [2]
Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Export Column" failed because error code 0xC020207F occurred, and the error row disposition on "Export Column.Inputs[Export Column Input].Columns[Path]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. End Error Error: 2018-02-20 09:29:24.49 Code: 0xC0047022 Source: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (2) failed with error code 0xC0209029 while processing input "Export Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:29:24 AM Finished: 9:29:24 AM Elapsed: 0.109 seconds. The package execution failed. The step failed.
February 20, 2018 at 10:41 am
as an update I took the original file deleted it from my database and inserted a new jpg file with the exact same name and it ran successfully the second time via the SQL Agent job. There doesn't appear to be any differences between the failed job and the one that ran successfully.
February 20, 2018 at 10:52 am
lucaskhall - Tuesday, February 20, 2018 9:10 AMi have an SSIS package that takes a image in my database and uploads it into a network location that is called by a SQL Agent Job and it seems to crash out when ever the user supplies a file name with a space in it. I am not sure why the job would crash with a space when the network location accepts files with spaces. Below is the error that I am getting and if I rerun the process and replace "test test test" with "testtesttest" it works as expected. Is there something that I am missing or doing wrong ?Message
Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:29:24 AM Error: 2018-02-20 09:29:24.49 Code: 0xC020207F Source: Data Flow Task Export Column [2]Description: The file name "\\HYPERV-APPSVR1\NFWImages\Service\300_497292\test test test.jpg" is not valid. The file name is a device or contains invalid characters.
End Error Error: 2018-02-20 09:29:24.49 Code: 0xC0209029 Source: Data Flow Task Export Column [2]Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Export Column" failed because error code 0xC020207F occurred, and the error row disposition on "Export Column.Inputs[Export Column Input].Columns[Path]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. End Error Error: 2018-02-20 09:29:24.49 Code: 0xC0047022 Source: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (2) failed with error code 0xC0209029 while processing input "Export Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:29:24 AM Finished: 9:29:24 AM Elapsed: 0.109 seconds. The package execution failed. The step failed.
I don't think that this error was generated from the network file system. Notice this error in particular:
... and the error row disposition on "Export Column.Inputs[Export Column Input].Columns[Path]" specifies failure on error.
This is an error thrown by the Export Column component, as the data flows into it (and not outwards). Not sure whether this is of any help though!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2018 at 12:22 pm
Thanks for the input I guess now I am curious why it only seems to fail sporadically and often times if I delete the image retake it and use the exact same file name it works. Do you happen to have any suggestions on troubleshooting as I am building the path dynamically using the below script.
declare @path varchar(100) = '\\HYPERV-APPSVR1\NFWImages\Service\'
select Image1
,@path + [WO_Branch] + '_' + [WONo] + '\' + [Description] + '.jpg' as Path
FROM Catavolt.dbo.cv_wo_xtd_imgs
WHERE pk_woxi = (select max(pk_woxi) from Catavolt.dbo.cv_wo_xtd_imgs)
February 20, 2018 at 12:36 pm
lucaskhall - Tuesday, February 20, 2018 12:22 PMThanks for the input I guess now I am curious why it only seems to fail sporadically and often times if I delete the image retake it and use the exact same file name it works. Do you happen to have any suggestions on troubleshooting as I am building the path dynamically using the below script.
declare @path varchar(100) = '\\HYPERV-APPSVR1\NFWImages\Service\'select Image1
,@path + [WO_Branch] + '_' + [WONo] + '\' + [Description] + '.jpg' as Path
FROM Catavolt.dbo.cv_wo_xtd_imgs
WHERE pk_woxi = (select max(pk_woxi) from Catavolt.dbo.cv_wo_xtd_imgs)
Is this doing just one file at a time?
You could add a Dts.Events.FireInformation in a script task immediately preceding the column export, to log the exact path, before the error occurs.
Are the spaces coming from the [Description] column? Is there a chance that this column contains some other 'dodgy' characters?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2018 at 12:42 pm
the cv_wo_xtd_imgs tables holds photos that are uploaded and I have an on insert trigger that calls a sql server job that in turn calls the SSIS package to export the raw data into a jpg. The description column is what ever the user enters as a description on the device i had originally maybe thought it was due to leading or trailing spaces so I added a LTRIM and RTRIM to the stored procedure that inserts the photo record but that didn't fix it. I am able to go back and look at the description that was entered and no dodgy characters have been entered so for everything I can tell the UNC path is correct and there is nothing crazy going on with the description.
I don't even think its spaces in the file name causing the issue since I was able to get files to successfully export even with spaces in the description to the same UNC path that failed on the previous run.
February 20, 2018 at 12:52 pm
lucaskhall - Tuesday, February 20, 2018 12:42 PMthe cv_wo_xtd_imgs tables holds photos that are uploaded and I have an on insert trigger that calls a sql server job that in turn calls the SSIS package to export the raw data into a jpg. The description column is what ever the user enters as a description on the device i had originally maybe thought it was due to leading or trailing spaces so I added a LTRIM and RTRIM to the stored procedure that inserts the photo record but that didn't fix it. I am able to go back and look at the description that was entered and no dodgy characters have been entered so for everything I can tell the UNC path is correct and there is nothing crazy going on with the description.I don't even think its spaces in the file name causing the issue since I was able to get files to successfully export even with spaces in the description to the same UNC path that failed on the previous run.
That sounds like an annoying problem, I sympathise.
Personally, I'd set up the job to run every x minutes & export anything 'new' which it finds, rather than using a trigger. Your architecture is somewhat more tightly coupled than I like.
If you run the package in VS, does it ever fail? You might get more diagnostics if it fails while you're in debug mode in VS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2018 at 1:00 pm
I guess at this point I will have to wait until a user enters a record that it doesn't agree with and I will take that record over to my test environment and try to run the package manually against it again to see if I can isolate the issue or get more detailed logs. I agree that perhaps modifying it to run every x minutes vs a trigger might be a better solution but either way there has to be a an underlying issue and I hate the not know the real root cause so I will continue troubleshooting and see what I can find. I have yet to have it fail for me running it in VS and 50% of the time in the SQL agent job it runs fine the rest it crashes with the same errors.
February 20, 2018 at 1:16 pm
was able to get a user to insert a picture that cause the job to crash and was then able to go back in and run the package in VB it crashed there as well and I got the following output but still shows its an issue with either the UNC path or the file name but I don't see any issues and if I put that UNC path into run its valid.
this is the exact UNC path out of my file system
\\HYPERV-APPSVR1\NFWImages\Service\300_512935
February 20, 2018 at 1:35 pm
lucaskhall - Tuesday, February 20, 2018 1:16 PMwas able to get a user to insert a picture that cause the job to crash and was then able to go back in and run the package in VB it crashed there as well and I got the following output but still shows its an issue with either the UNC path or the file name but I don't see any issues and if I put that UNC path into run its valid.this is the exact UNC path out of my file system
\\HYPERV-APPSVR1\NFWImages\Service\300_512935
At what stage of the process is the target folder created?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2018 at 1:39 pm
the target folder is already created and will always be created prior to the export.
February 20, 2018 at 1:46 pm
just reran the exact same process and it completed successfully in VS and uploaded the photo correctly. no idea what is causing it to crash first time through and I validated that folder existed prior to running the first time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply