developer.* Blogs - SSIS Bulk Insert Task Error: "system cannot find the file specified" - Comments

Detecting Empty Input File

Daniel Read — Tue, 20 Jun 2006 07:40:46 -0700

There might be an explicit setting that I don't know about, and if we were speaking in person I might ask what downstream response you would like to engineer when you've encountered an empty file. What I mean is, do you want to do something procedurally in your Control of Flow based on that information, or do you want to respond somehow within a Data Flow?

One idea is to use a Script Task to use System.IO.File to determine the size of the file. If it's zero, it's empty, but that won't help you if an empty file can contain a header row. You might need to do something more fancy in the Script Task, such as perhaps opening the file.

Also, I'm not sure how useful it would be in your situation, but there is also a Row Count Transformation in the Data Flow toolbox that will count the rows that flow through it and store the result in a package variable.

HTH,
Dan

Missing file notification?

Guest — Tue, 20 Jun 2006 06:54:45 -0700

Is there anyway to get some type of notification from the import package when it encounters an empty file?

Use an Empty File

Daniel Read — Mon, 19 Jun 2006 09:53:16 -0700

We solved a similar situation by using a dummy empty file, which seems to work fine. If there's a better way, I don't know about it.

Best of luck,
Dan

Dealing with missing import file(s)

Guest — Mon, 19 Jun 2006 08:40:28 -0700

I'm really new to SSIS and I've been working on a package to import flat files. Everything works fine unless the import file is missing of course. What I haven't been able to figure out is how to get the package to run inspite of a missing file, for example in the event that there is no data to be imported on any given day. I've thought about just placing an empty file, but there has got to be a better way.

Thanks for any suggestions/help

SSIS Feedback

Daniel Read — Mon, 06 Feb 2006 13:42:47 -0800

Hi Jamie,

Thanks for the comments. I hope the items on my wish list don't come across as too negative to the SSIS team. Many of them were composed in heated moments when I was feeling that particular frustration. :-)

Re: the Framework folder at runtime: yes, I had seen something on that, thanks. It's apparent that this DLL/GAC reference issue is not really an SSIS issue but more of a Visual Studio for Applications issue.

Re: setting the connection manager property with an expression, pulling from a variable: I had gone away from that because I experienced some issues with *when* the expression is evaluated. It was not working the way I thought it should, so I did a little research and found out that the expression is evaluated on Validation. In this particular case, the tasks were inside of a ForEach container, and the Validation was not happening on each iteration--or something like that. So I put in a script task that will move the value from the variable to the connection manager on each iteration.

Thanks again!

Dan

Idle comment

Jamie Thomson — Mon, 06 Feb 2006 12:59:04 -0800

Hi Dan,
Apologies if I'm posting this in the wrong place...

great post on the SSIS Requests thread today (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89737&SiteID=1&Pag...)

Just a couple of points.
You know that DLLs don't have to be in the C:\...Framework\... folder at runtime right?

You can store connection strings in variables and use expressions to assign them to a connection manaager. i talk about this a bit here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

Other than that I agree with jsut about everything you say. One other similar developer productivity type thing I'd like to see is to drop a task onto a precedence constraint (or a component onto a data path) and have it automatically placed intbetween the 2 tasks/components that the precedence constraint/data path is between. This is similar to your request to be able to drag a precedence constraint from one task to another.

Anyway, great post. Have you thought about logging these things at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)

-Jamie

SSIS Bulk Insert Task Error: "system cannot find the file specified"

Daniel Read — Tue, 31 Jan 2006 07:41:19 -0800

This one might be obvious, I suppose, to people with experience with the Bulk Insert task from previous SQL Server versions, but this information it was new to me.