i new ssis , c#. in sql server 2008 importing data .csv file. have columns dynamic. can around 22 columns(some times more or less). created staging table 25 columns , import data it. in essence each flat file import has different number of columns. formatted only. task import rows .csv flat file including headers. want put in job can import multiple files table daily.
so inside each loop have data flow task within have script component. came up(research online) c# code below error:
index outside bounds of array.
i tried find cause using messagebox , found reading first line , index going outside bounds of array after first line.
1.) need fixing code
2.) file1conn flat file connection instead want read directly variable user::filename foreach loop keeps updating. please modifying code below.
thanks in advance.
this flat file:
https://drive.google.com/file/d/0b418obdivneirnlszfdwytrftfu/view?usp=sharing
using system; using system.data; using microsoft.sqlserver.dts.pipeline.wrapper; using microsoft.sqlserver.dts.runtime.wrapper; using system.windows.forms; using system.io; [microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute] public class scriptmain : usercomponent { private streamreader sr; private string file1; public override void acquireconnections(object transaction) { // connection file1 idtsconnectionmanager100 cm = this.connections.file1conn; file1 = (string)cm.acquireconnection(null); } public override void preexecute() { base.preexecute(); sr = new streamreader(file1); } public override void postexecute() { base.postexecute(); sr.close(); } public override void createnewoutputrows() { // declare variables string nextline; string[] columns; char[] delimiters; int col4count; string[] col4value = new string[50]; // set delimiter delimiters = ";".tochararray(); // read first line (header) nextline = sr.readline(); // split line columns columns = nextline.split(delimiters); // find out how many col3 there in file col4count = columns.length - 3; //messagebox.show(col4count.tostring()); // read second line , loop until end of file nextline = sr.readline(); while (nextline != null) { // split line columns columns = nextline.split(delimiters); { // add row file1outputbuffer.addrow(); // set values of script component output according file content file1outputbuffer.sampleid = columns[0]; file1outputbuffer.repnumber = columns[1]; file1outputbuffer.product = columns[2]; file1outputbuffer.col1 = columns[3]; file1outputbuffer.col2 = columns[4]; file1outputbuffer.col3 = columns[5]; file1outputbuffer.col4 = columns[6]; file1outputbuffer.col5 = columns[7]; file1outputbuffer.col6 = columns[8]; file1outputbuffer.col7 = columns[9]; file1outputbuffer.col8 = columns[10]; file1outputbuffer.col9 = columns[11]; file1outputbuffer.col10 = columns[12]; file1outputbuffer.col11 = columns[13]; file1outputbuffer.col12 = columns[14]; file1outputbuffer.col13 = columns[15]; file1outputbuffer.col14 = columns[16]; file1outputbuffer.col15 = columns[17]; file1outputbuffer.col16 = columns[18]; } // read next line nextline = sr.readline(); } } }
as mentioned file has dynamic amount of columns, in script component need count number of columns delimiters, redirect different outputs.
for 2nd question, can assign variable flat file connection manager connection string property. can read variable value in script directly.
except script component, can create "one column" flat file source using dummy delimiter, in data flow task, can read amount of columns variable, conditional split data flow, redirect outputs different destinations. example can found @ http://sqlcodespace.blogspot.com.au/2015/03/ssis-design-pattern-handling-flat-file.html
Comments
Post a Comment