Script Component throws error

Hi,

I have three script component A,B, C. A reads certain data from a table. B inserts records into a .txt file when the recordtype = 1. C inserts records into same .txt file when the recordtype = 0. Now I receive a script comp error for C as follows.

[Error Desc]

************************************************************

The process cannot access the file 'D:\Documents and Settings\Administrator\Desktop\AuditLog.txt' because it is being used by another process.

************************************************************

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

at System.IO.StreamWriter.CreateFile(String path, Boolean append)

at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

at System.IO.StreamWriter..ctor(String path, Boolean append)

at ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54.ScriptMain.PreExecute() in dts://Scripts/ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54/ScriptMain:line 19

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

[/Error Desc]

Can anyone throw some light on it.

Thanks

If you have two script conponents trying to write to the same file at the same time, then this would seem a reasonable error. You will need to serialise the two write operations, as only one process can have a file open at a given time.|||

Hi,

Thanks for your reply. After completion of first script component only next gets executed.

In my example after B completes C starts. Also I have closed and disposed the stream writer object in the post execute of the script component B

How I can get rid of this. Also suggest any alternative solutions?

|||

Are all the script components in the same data-flow? If so then there is nothing to stop them trying to access the same file at the same time given that each component operates on one data buffer at a time. Each data buffer is a subset of all the data in the pipeline.

If you want to ensure that the script components are not accessing the same file concurrently put them in seperate, sequential, data flows. Or put them in 3 seperate files and then put them all together into a single file using UNION ALL component in a seperate data-flow.

-Jamie

Script component as Destination - HasSideEffects?

How to set 'HasSideEffects' property for any input column of a script component used as destination?

My Script component reads input, generates SQL update statements dynamically and fires them. There is no output. When executing, I get this error (which is self-explanatory).

"Script Component" (1573) has been removed from the Data Flow task because its output is not used and its inputs have no side effects. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something.

How do I get around this?

thanks,
Nitesh

Which build do you have and how do you create the components?

I've tested the recent build, when I create a script component
or destination, the input is marked as HasSideEffect = true
(it is property of the input, not particular column AFAIK).

You can check this in Input and Outputs page in component
editor. Select the input, it has property HasSideEffect = true.
It is read-only though, but should have the correct value.|||I had the same problem running on the september CTP.

The "HasSideEffects" property of the input to the script component was not enabled, so I couldn't change it.

A workaround is to create a dummy output for the script component. This output will automatically get HasSideEffects set to True and the component will not be removed.|||I have June CTP.
But, HasSideEffects is a readonly attribute of Input and it is set to false.
I can't change it.

Yeah, for now I am using the work around of using a dummy output.

thanks,
Nitesh|||I tested this in the current build and if you select that the script task is a destination then the input automatically gets HasSideEffects set to true. When you say you had the same problem on the Sept CTP did you create a new script task as a destination or did you open an already existing script task (it won't change since the property is persisted).

Two workarounds:
1. Set the RunInOptimizedMode property on the dataflow task to false. This should stop it from removing component but could impact performance if some other pruning made your package run faster. If nothing else was being pruned then you will notice no perf impact at all.
2. Give your input a strange name (e.g. Fix hassideefects on this input) and then edit the xml directly and search for the strange name and change the HasSideEffects on that input to true. I don't recommended this since editing the xml directly can have serious consequences if done incorrectly but it can be done as a last resort if using option 1 affects your perf too much.

HTH,
Matt