SQL Server Integration Services and Flat File Output
-
You are a SQL Server Integration Services guru. I need help with what appears to be a simple problem with a flat file connection manager. Observe the following snippet of an SSIS package: http://i.imgur.com/1q4Le.png Both of the Flat File Destination tasks on the right are configured to write to the same Flat File Connection Manager, because I want all the failing records to be redirected to the same file regardless of which task caused them to fail. SSIS is complaining on the second Flat File Destination, saying that the output file is in use by another process. Is my stated goal simply impossible, or is there a way to redirect all failing records to the same output file?
-
Answer:
I would say that yes, the way you currently do it is probably not going to work. I would personally output the results in 2 separate files and then add a step to concatenate those files in a third one. That should resolve the issue and depending on the size of those files, should not impact performance too much.
DWRoelands at Ask.Metafilter.Com Visit the source
Other answers
If I recall my SSIS correctly: The flat file output tasks retain a lock on the log file being written to so that it's contents can't be commingled with other output. Since batches of rows can move between tasks (i.e the tasks do operate in parallel, given a large enough data set) it is necessary for both tasks to retain a lock on the file simultaneously which, as you've discovered, is a problem. (This also has the effect that two instances of the package cannot be run simultaneously without having the error output directed to different files for both instances.) As McSly suggests, it is necessary to use two separate files for output or a different destination that deals with simultaneity better (e.g. a database output).
Five O'Clock
Thank you both. My final resolution was to create Derived Column tasks that populate a "Failure Reason" column, attach one of these tasks to each potential failure point, and then have a UNION ALL task that combines them to one output. Thank you!
DWRoelands
Related Q & A:
- How to control the excessive use of ram by SQL Server?Best solution by Database Administrators
- Can I create a second filestream container on an existing SQL Server 2008 database without going offline?Best solution by Database Administrators
- How to connect Sql Server Database from android app?Best solution by Stack Overflow
- How to upgrade sql server express 2005 to 2008R2?Best solution by Server Fault
- Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008?Best solution by Database Administrators
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.