SSIS Bulk Insert Task Error: "system cannot find the file specified"
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. I have now learned the cause of this error with the SSIS Bulk Insert task reading from a flat file connection manager:
Cannot bulk load because the file "C:\XYZ.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).
This little item from the Bulk Insert task documentation tells the tale:
The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.
This creates some design pains in the butt that I was not anticipating.
Dan
SSIS Feedback
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
Dealing with missing import file(s)
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
Use an Empty File
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
Missing file notification?
Is there anyway to get some type of notification from the import package when it encounters an empty file?
Detecting Empty Input File
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
Idle comment
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