Dear All, Please suggest me i have a data in text which is not in a statnadard format . data format is like given below PERF PROV SERV DATE POS NOS PROC MODS BILLED ALLOWED DEDUCT COINS GRP/RC-AMT PROV PD ----------------------------------------------------------------------------------------------------------------------------------- APPLETON ,OTIS HIC 311407311M ACNT 10407241SLR412139 ASG Y ICN 1112018178030 MOA MA01 MA07 1043241904 0106 010612 21 001 36569 800.00 101.66 61.61 8.01 CO-45 698.34 32.04 REM: N535 PR-1 61.61 PR-2 8.01 1043241904 0106 010612 21 001 77001 26 65.00 20.57 .00 4.11 CO-45 44.43 16.46 REM: N264 N265 PR-2 4.11 1043241904 0106 010612 21 001 76937 26 45.00 16.49 .00 3.30 CO-45 28.51 13.19 REM: N264 N265 PR-2 3.30 PT RESP 77.03 CLAIM TOTALS: 910.00 138.72 61.61 15.42 848.31 61.69 ADJ TO TOTALS: INTEREST .00 LATE FILING CHARGE .00 NET 61.69 *** NOTE: CLAIM FORWARDED TO COMPLIMENTARY INSURER: NYS DEPARTMENT OF HEALTH ----------------------------------------------------------------------------------------------------------------------------------- BLACK ,SHAWN HIC 117561814A ACNT 10403543SLR412201 ASG Y ICN 1112018178330 MOA MA01 MA07 1881848687 0109 010912 21 001 36569 800.00 101.66 .00 20.33 CO-45 698.34 81.33 REM: N535 PR-2 20.33 1881848687 0109 010912 21 001 77001 26 65.00 20.57 .00 4.11 CO-45 44.43 16.46 PR-2 4.11 1881848687 0109 010912 21 001 76937 26 45.00 16.49 .00 3.30 CO-45 28.51 13.19 PR-2 3.30 PT RESP 27.74 CLAIM TOTALS: 910.00 138.72 .00 27.74 799.02 110.98 ADJ TO TOTALS: INTEREST .00 LATE FILING CHARGE .00 NET 110.98 *** NOTE: CLAIM FORWARDED TO COMPLIMENTARY INSURER: NYS DEPARTMENT OF HEALTH ----------------------------------------------------------------------------------------------------------------------------------- BURTON ,DOLORES HIC 050285607B ACNT 10267199SLR410028 ASG Y ICN 1112018680480 MOA MA01 MA07 1578593893 0112 011212 21 001 74000 26 25.00 9.71 .00 1.94 CO-45 15.29 7.77 PR-2 1.94 PT RESP 1.94 CLAIM TOTALS: 25.00 9.71 .00 1.94 17.23 7.77 ADJ TO TOTALS: INTEREST .00 LATE FILING CHARGE .00 NET 7.77 *** NOTE: CLAIM FORWARDED TO COMPLIMENTARY INSURER: NYS DEPARTMENT OF HEALTH ----------------------------------------------------------------------------------------------------------------------------------- CARTHENS ,WILLIE HIC 230589885A ACNT 10399864SLR409793 ASG Y ICN 1112018682000 MOA MA01 MA18 1295948529 0111 011112 22 001 G0204 26 185.00 47.45 38.99 1.69 CO-45 137.55 6.77 PR-1 38.99 PR-2 1.69 1295948529 0111 011112 22 001 77051 26 20.00 3.39 .00 .68 CO-45 16.61 2.71 REM: N264 N265 PR-2 .68 PT RESP 41.36 CLAIM TOTALS: 205.00 50.84 38.99 2.37 195.52 9.48 ADJ TO TOTALS: INTEREST .00 LATE FILING CHARGE .00 NET 9.48 *** NOTE: CLAIM FORWARDED TO COMPLIMENTARY INSURER: UNITED AMERICAN INSURANCE CO ----------------------------------------------------------------------------------------------------------------------------------- CHOW ,PO HIC 119781413M ACNT 10393893SLR412004 ASG Y ICN 1112018177290 MOA MA01 1811928427 0101 010112 21 001 47490 1470.00 387.67 .00 77.53 CO-45 1082.33 310.14 PR-2 77.53 PT RESP 77.53 CLAIM TOTALS: 1470.00 387.67 .00 77.53 1159.86 310.14 ADJ TO TOTALS: INTEREST .00 LATE FILING CHARGE .00 NET 310.14 plesae suggest how can i export data like this in sql table using SSIS Regards, Vipin jha

Are there records divided over multiple rows?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

therecords are same in all text file yes these records devided over multiple rows

therecords are same in all text file yes these records devided over multiple rows Can you identify a row delimiter or column delimiter?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Looks like you will need to use a script source to manually parse out the sections of the report file you are importing. You'll need to use find methods to find the location of the header and footer rows in each block you are processing One simple example - yours is a bit more complicated: http://www.sqlservercentral.com/Forums/Topic909640-364-1.aspx#bm911221 Chuck

Here's an example from a POC project I did a while back, which pulled apart a trade report /* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.IO; using System.Diagnostics; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void PreExecute() { base.PreExecute(); } public override void PostExecute() { base.PostExecute(); } public override void CreateNewOutputRows() { //name of the source file from a variable string fileLoc = Variables.UnstructuredFileLoc; //Or get it from a connection object string fileLoc2 = Connections.FileConn.ConnectionString; StreamReader sr = new StreamReader(fileLoc); string strInd = string.Empty; string strStlDate = string.Empty; string strCusip = string.Empty; string strRRGross = string.Empty; string strCommission = string.Empty; string strFees = string.Empty; string strNet = string.Empty; string strTrailer = string.Empty; string strContra = string.Empty; string strTrdNbr = string.Empty; string strAcct = string.Empty; string strAcctType = string.Empty; string strType = string.Empty; string strQty = string.Empty; string strPrice = string.Empty ; string strPrincipal = string.Empty ; int parseResult; string strTrdDate = string.Empty; //Read the first line of text string line = sr.ReadLine(); //Do header check and populate tradeDate if (line != null) { line = line.PadRight(130); if (line.Substring(0, 6) == "RPT002") { strTrdDate = line.Substring(111, 8); } else { throw new Exception("File header did not match expected value", null); } } line = sr.ReadLine(); //Run through the file and process one data group at a time while (line != null) { line = line.PadRight(130);//pad the line to a designated width. string.Substring does not allow you to substring beyond the end of the buffer strInd = line.Substring(0, 5).Trim();// Trim(Mid(line, 1, 5)) switch (strInd) { case "RPT00": case "TRADE": case "NBR": break; case "": case "AS OF": if (line.Trim() == string.Empty) break; else if (int.TryParse(line.Substring(6, 2), out parseResult)) //isnumeric { //Pulls the 1st line strStlDate = line.Substring(6, 5).Trim();// Trim(Mid(line, 7, 5)) strCusip = line.Substring(23, 9).Trim(); //Trim(Mid(line, 24, 9)) strRRGross = line.Substring(80, 17).Trim(); //Trim(Mid(line, 81, 17)) strCommission = line.Substring(97, 13).Trim();//Trim(Mid(line, 98, 13)) strFees = line.Substring(110, 10).Trim();//Trim(Mid(line, 111, 10)) } else if (int.TryParse(line.Substring(112, 4), out parseResult))//ElseIf IsNumeric(Mid(line, 113, 4)) Then { //"Pulls the 2nd line //"Looking for Net value strNet = line.Substring(104, 17).Trim();// Trim(Mid(line, 105, 17)) } else if (int.TryParse(line.Substring(121, 9), out parseResult))//ElseIf IsNumeric(Mid(line, 122, 9)) Then { //"Looking for Contra details strTrailer = line.Substring(72, 30).Trim();// Trim(Mid(line, 73, 30)) strContra = line.Substring(121, 9);//Mid(line, 122, 9) //Call WriteMain this.rpt002Buffer.AddRow(); this.rpt002Buffer.BuySell = strType; this.rpt002Buffer.TradeDate = strTrdDate; this.rpt002Buffer.TradeNbr = strTrdNbr; this.rpt002Buffer.SettleDate = strStlDate; this.rpt002Buffer.P3AcctNbr = strAcct; this.rpt002Buffer.P3AcctType = strAcctType; this.rpt002Buffer.Cusip = strCusip; this.rpt002Buffer.Quantity = strQty; this.rpt002Buffer.RRGross = strRRGross; this.rpt002Buffer.Price = strPrice; this.rpt002Buffer.Commission = strCommission; this.rpt002Buffer.Fees = strFees; this.rpt002Buffer.Principal = strPrincipal; this.rpt002Buffer.Net = strNet; this.rpt002Buffer.ContraBkr = strContra; this.rpt002Buffer.TrailerDesc = strTrailer; //Call Init strInd = string.Empty; strStlDate = string.Empty; strCusip = string.Empty; strRRGross = string.Empty; strCommission = string.Empty; strFees = string.Empty; strNet = string.Empty; strTrailer = string.Empty; strContra = string.Empty; strTrdNbr = string.Empty; strAcct = string.Empty; strAcctType = string.Empty; strType = string.Empty; strQty = string.Empty; strPrice = string.Empty; strPrincipal = string.Empty; } break; default: if (int.TryParse(line.Substring(12, 8), out parseResult))//If IsNumeric(Mid(line, 13, 8)) Then { strTrdNbr = line.Substring(0, 5);//Mid(line, 1, 5) strAcct = line.Substring(12, 8);//Mid(line, 13, 8) strAcctType = line.Substring(20, 1);//Mid(line, 21, 1) strType = line.Substring(23, 6).Trim();//Trim(Mid(line, 24, 7)) strQty = line.Substring(59, 19).Trim();//Trim(Mid(line, 60, 19)) strPrice = line.Substring(78, 20).Trim();//Trim(Mid(line, 79, 20)) strPrincipal = line.Substring(99, 22).Trim();//Trim(Mid(line, 100, 22)) } break; } line = sr.ReadLine(); } //Call this when you are done outputting to a particular output this.rpt002Buffer.SetEndOfRowset(); } } Chuck