I am trying to import a directory of 200 text files which have the 1st 40 columns the same and then some of remaining 60 columns have quite a few missing or not present due to the way they are produced . I am only interested in bringing in the first 40 columns . DATA FLOW So I thought into the whole line in one go and transform . Tasks are Flat file source going to data conversion , as data type D_NTEXT not supported , after data conversion its my script componect then an OLE DATABASE Destination . SCRIPT COMPONENT CODE using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.IO; [Microsoft.SqlServer.Dts.Pipeline. SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { private StreamReader _reader; private string _dataFile; public override void AcquireConnections(object Transaction) { IDTSConnectionManager100 connMgr = this.Connections.FF; _dataFile = ( string)connMgr.AcquireConnection(null); } public override void PreExecute() { base.PreExecute(); _reader = new StreamReader(_dataFile); } public override void PostExecute() { base.PostExecute(); _reader.Close(); } public override void CreateNewOutputRows() { string _nextLine; string[] _columns; char[] delimiters; delimiters = "|".ToCharArray(); _nextLine = _reader.ReadLine(); while (_nextLine != null) { _columns = _nextLine.Split(delimiters); { JazzORBuffer.AddRow(); JazzORBuffer.Server = _columns[0]; JazzORBuffer.Country = _columns[1]; JazzORBuffer.QuoteNumber = _columns[2]; } _nextLine = _reader.ReadLine(); } } } ERROR Information: 0x402090DC at Alternate way of importing WFiles, Flat File Source [1]: The processing of file "C:\P\DataSource2_W\TextFiles\Batch1\1.txt" has started. Information: 0x4004300C at Alternate way of importing WFiles, SSIS.Pipeline: Execute phase is beginning. Error: 0xC0047062 at Alternate way of importing WFiles, Script Component [18]: System.IndexOutOfRangeException: Index was outside the bounds of the array. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket) Error: 0xC0047038 at Alternate way of importing Watson Files, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Script Component" (18) returned error code 0x80131508. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Alternate way of importing Watson Files, SSIS.Pipeline: Post Execute phase is beginning.

Check what the length of _columns is. something like (not tested): if (_columns.length < 3) { messagebox.show("length: " + _columns.length) } Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Added this and it failed after giving me 1 in the message Box Code added : (_columns.Length < 15) { MessageBox.Show("length: " + _columns.Length); } ERROR after code change**** Index was outside the Bounds of the array ERROR WIndow at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Added this and it failed after giving me 1 in the message Box Code added : (_columns.Length < 15) { MessageBox.Show("length: " + _columns.Length); } ERROR after code change**** Index was outside the Bounds of the array ERROR WIndow at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Added this and it failed after giving me 1 in the message Box Code added : (_columns.Length < 15)MessageBox.Show("length: " + _columns.Length); { } ERROR after code change**** Index was outside the Bounds of the array ERROR WIndow at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) It will not solve the error, but now you now that some of your lines don't have the expected number of columns. Is that expected? Do you want to ignore these lines? Of do you want to fail the complete file? Or....? For ignore you could do something like: { if (_columns.Length == 3) { JazzORBuffer.AddRow(); JazzORBuffer.Server = _columns[0]; JazzORBuffer.Country = _columns[1]; JazzORBuffer.QuoteNumber = _columns[2]; } } And in the else you could add some logging: http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

I expect some rows to have more than the expected number of columns . The reason it comes out at 1 is that I am feeding in one column to be split out to 3 columns . So what happens to the rest of the columns. Is that why it is failing? Understand that this would give me some more info and not neccessarily resole my problem.

this code expects that there are at least three columns (2 pipelines): _columns[2]; If there are less it will throw an index out of range error. You could add an if around each column like in this example: http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

You are right . It has the problem with the Header and Footer . *Header PRODUCTS created betwen x and y Column 1 Header | Columns 2 Header | Column 3 Header | Column 4 Header | Wendy Z| Dave | John | Steve | *Footer Wendy Z| Dave | John | Steve | Total number of prods| 49545 Now I need my code to start at Line 4 & somehow cope with this footer ? Maybe I could clean the files before using these scripts, deleting the header and footer somhow . The header is always the 1st 3 lines but the footer is a lot more tricky to do .

Depending on the size of your file, you could ignore the header and footer with a trick like this: http://stackoverflow.com/questions/10585264/ignore-header-and-footer-lines-when-reading-a-text-file But that doesn't work with very large files, because it first reads all lines in memory. Or (a little dirty) just check if the lines start with "PRODUCTS created between " or "Total number of prods| ". Something like: if (_nextLine.StartsWith("PRODUCTS created between") || _nextLine.StartsWith("Total number of prods| ")) { JazzORBuffer.AddRow(); JazzORBuffer.Server = _columns[0]; JazzORBuffer.Country = _columns[1]; JazzORBuffer.QuoteNumber = _columns[2]; } Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter