Change in SSIS treatment of NUL device after SQL Server 2008 Service Pack 3?
-
I have an SSIS system which loads a set of text files, using a Flat File Source component for each file in the set. The file names to be loaded are set by a previous script component. For some sets, some of the files don't exist, so I set the file name for those to NUL.
This has worked fine for about a year; those data flows simply read no records. But it has now stopped working, and I get an error message saying "[font="Courier New"]The file name is a device or contains invalid characters.[/font]".
The only thing that I know has changed is that I let Microsoft Windows Update install SQL Server 2008 Service Pack 3 yesterday - and I've had this problem since.
Does anyone know whether Microsoft has changed the way the Flat File Source component treats the NUL device, and / or how to fix it?
-
Not too sure, but for a list of bug fixes in SP3 you can have a look here: http://support.microsoft.com/kb/2546945
-
Thanks Martin - but having looked there, I can't see any changes that affect how SSIS deals with the NUL device.
-
David Data (11/17/2011)
Thanks Martin - but having looked there, I can't see any changes that affect how SSIS deals with the NUL device.We'll need more information to help you then. Are you 100% sure that nothing in the package has changed? Or sheduled job for that matter?
Can you post your package, or a similar test package that will illustrate what is happening? The problem with these types of errors is that there are so many things to look at. If we can get a test package, at least we can help you come up with a solution irrespective of why it's failing now vs. not failing before...
-
I am sure it's not due to one of my changes. Because 1) I reverted to an earlier version this morning, from when it all worked, and got the same error on my development server (Windows 7/64), and 2) it's still working fine on our production server, (Windows Server 2008/64), which hasn't had SP3 applied to SQL Server 2008 yet.
I can't post the whole package as it's too big. It loads about 100 files, in many different formats. Here is a small screenshot of the whole thing:
And here's one of the data flows that now fails:
This loads 8 files, but some are only present some of the time. A script task finds out which files exist and sets a variable like User:INcust to the file name for each of the Get threads - or to NUL if that particular file isn't there. This is used to set ConnectionString = @[User:INcust] etc.. If one of the files is missing ConnectionString is set to NUL and so loads no data. Or did; now it gets a fatal error message, e.g.
[font="Courier New"][Get CH Lookup [8767]] Error: The file name is not valid. The file name is a device or contains invalid characters.[/font]
I'd like to fix it so the NUL device is allowed again, or failing that find another way to let a thread in the data flow do nothing when there is no data for it, while the other threads load their data normally. (I tried setting the file name to "" or to an empty file, but the first caused a fatal error and the second warnings about missing header records.)
-
Unfortunately I don't have the environments available to test this further, but here are a few suggestions to eliminate the error. All of these would require some work on your part...
1. You could play with the MaximumErrorCount and FailPackageOnFailure properties, but I don't think that would be ideal because you may miss a real error...unless you make some other provision for it somewhere else.
2. Split every file extract into its own transformation. This would give you more control and you would be able to control the execution conditionally from the control flow.
3. Create an empty "template" file for each of the files. Set the path of your connection to this empty file when the source file is not available, as opposed to setting it to NULL.
Personally I would go with option 2. I would typically create separate transformations for each file import, and rather group them logically in the control flow. That gives you ultimate control and flexibility.
Hope this helps.
Martin.
-
Thanks Martin - but as you'll realise those are not ideal solutions!
I had not thought of 1), but I don't want to do that as I don't think it's good practice for a run that works to produce errors - especially as this system sends an email to my customer when it runs, reporting every successful load, warning and error!
I REALLY don't want to put each thread in a separate sequence container. I've just tried splitting out a single thread and it's already a mess; if I did it to all of them I'd need a 60" screen just to see half of them :crying:
Of course you can shrink the task box - but then you have to enlarge it again to make any changes and at that point I find the boxes go enormous and spread all over the other task boxes. (I REALLY wish the SSIS graphics tool had an UNDO ... or even worked properly to unshrink a box to its original placing not something that spreads everywhere.)
Having a separate empty (headings only) file for each possible real file would work, but would be difficult to manage - did I mention there are about 100 different files, and they come in from different organisations at different times? Though I could do that.
The fourth option is to write a script component to get any file that may not always be present. I've already had to do that for files that break the Flat File Source (e.g. have incomplete rows), and in that code I simply added an If to decide whether to read the file or not. Would be a lot of extra work though.
OK all you clever people out there - can you come up with a fifth option? 🙂
-
Yeah...tough situation.
I think no. 3 would be your most sustainable option in the current setup. Sure, creating a hundred empty files is a lot of work but not as much as redesigning (i.e. splitting up) the whole package.
Unfortunately I do not have any other suggestions at this point in time...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply