every row of sql is broken into 14 separate rows in the txt file so, if sql table has 5 rows, then the txt file should have 5 * 14 + 1 the 1 extra row is at the beginning of the flat file which specifies how many separate groups of 14 rows are present (in this case, 5) how do i achieve this?

what you want to do?Import or Export? you want to read data from flat file and store it in sql server table or reverse? by the way, you can do it with Asynchronous Script Component , a sample of asynchronous script component is here: http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html Also you didn't say that do you have a column value on each row of flat file? if yes, maybe you could use pivot or unpivot transformations ( but this depends on your case )http://www.rad.pasfu.com

i wish to read from an sql table and generate a flat file from it the table has 150 cols in it, which need to be broken into 14 rows in the flat file this sql table has 5 rows ... which means, my flat file should have 14 * 5 rows also, there needs to be 1 additional row at the very beginning of the file which has how many separate groups of 14 rows are present (in this case, 5) please guide

OK, did you looked at the link I provided in my previous post? you can use script component and create 5 rows per each data row from sql server source, you can manipulate all things in script then connect output into flat file destination. I strongly recommend to take a deep look at that link.http://www.rad.pasfu.com

thank you for your reply i did read that link but understood very little from it i am fairly new to SSIS can you please guide me with a solution specific to my problem? what exactly would be the code that i would write in order to generate the flat file the way i want it?

what exactly would be the code that i would write in order to generate the flat file the way i want it? for exact code you should put here these materials: exact structure of sql server table, with some sample rows and exact structure of output flat file with samples. http://www.rad.pasfu.com

table structure: http://connect.nirmalbang.net/script.txt data [delimited with commas]: http://connect.nirmalbang.net/data.txt for the sample output, just generate 14 rows for each data row ... you can keep any number of columns in each row however, in the first row, please make sure that it has a number which tells me how many separate groups of 14 rows are present (in this case, 5)

dear Reza Raad, i am in dire straits... eagerly waiting for your reply please help

sorry for late response, I have a question first: do you want to use this flat file to import anywhere else? I asked this because you have columns with different data types and put them row by row down each other force flat file to consider them all as same data type. If you just want to export them in this flat file and you don't have to do any import/export at future there will be no problem of course.http://www.rad.pasfu.com

i need to generate the flat file the way i have described it can you please help me with some code?

your solution is perfect! i just need 1 more thing that you missed out: there is 1 extra row at the beginning of the flat file which specifies how many total number of rows are transferred how do i go about doing that?

yes, this is the way: add a data flow task, add an oledb source to read data from source table, add a script component transform as Destination, connect output of oledb source to script component destination. in the "input columns" tab, check all input columns, then go back to script tab, and click edit script. define a streamwriter variable in class: System.IO.StreamWriter sw; in the PreExecute Method, open StreamWriter: public override void PreExecute() { base.PreExecute(); sw=new System.IO.StreamWriter(@"D:\flatfile.txt"); } in the Input0_ProcessInputRow method, add lines to stream writer: public override void Input0_ProcessInputRow(Input0Buffer Row) { sw.WriteLine(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}",Row.Column0,Row.Column1,Row.Column2,Row.Column3,Row.Column4,Row.Column5,Row.Column6,Row.Column7,Row.Column8,Row.Column9)); sw.WriteLine(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}",Row.Column10,Row.Column11,Row.Column12,Row.Column13,Row.Column14,Row.Column15,Row.Column16,Row.Column17,Row.Column18,Row.Column19)); //add every number of rows you want to put in output here } then close stream writer in the PostExecute method: public override void PostExecute() { base.PostExecute(); sw.Flush(); sw.Close(); } save and close script editor, and run the package.http://www.rad.pasfu.com

do you mean number of rows in sql table or number of lines in destination flat file? by the way, you can simply use single sw.WriteLine("number of rows here") right at PreExecute method, like this: public override void PreExecute() { base.PreExecute(); sw=new System.IO.StreamWriter(@"D:\flatfile.txt"); sw.WriteLine("number of rows"); } but where "number of rows" comes from is the question which you can answer, just let me know what do you mean ? row count in sql table ? or line count in destination file? and I will help you better.http://www.rad.pasfu.com

i need the number of rows in the flat file using WriteLine, i have divided my 1 row in 14 rows so how many different sets of 14 rows are written in the file that is what i want

first you need to fetch number of rows in the sql table, you can get it by row count transformation. but before that create new variable in ssis , of type INT, set default as 0, name it for example RowCount the add a row count transform right after oledb source, and connect data path ( green arrow ) from oledb source to this row count transform, in the row count transform editor, set RowCount variable there, then connect green arrow from row count to script component. in the script component editor, set RowCount as ReadOnlyVariables. in the script, make this change: public override void PreExecute() { base.PreExecute(); sw=new System.IO.StreamWriter(@"D:\flatfile.txt"); sw.WriteLine(String.Format("{0}",Variables.RowCount*14)); }http://www.rad.pasfu.com

i did exactly as you said but, in the script component i don't see the variable that i defined earlier maybe i am making a mistake while defining the variable? where should i exactly define the variable?

the error is not related to script component. Row count transform caused this error, could you take screen shot of your whole data flow schema, and row count transform editor window?http://www.rad.pasfu.com

im not sure what you're asking for here are 2 screenshots: http://connect.nirmalbang.net/2.png http://connect.nirmalbang.net/3.png

seems strange. everything is correct. could you mail your whole package to me with sample database and I try to troubleshoot it. ( a dot raad dot g at gmail dot com ) http://www.rad.pasfu.com

Could you do one thing: Have an execute SQL task in the control flow to take the rowcount and save it to the varaible RowCount. Now have your data flow task as it is and remove the row count transform from there. This should solve the issue.

did you add variable to the script component editor, script tab, ReadOnlyVariables?http://www.rad.pasfu.com

i had actually missed that step when i did do it, it is now giving me error "Error trying to write read only variable" upon recieving this error, i made the variable readwrite and i recd the same error!!

what is your SSIS version? and could you put whole your script here?http://www.rad.pasfu.com

/* 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; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { System.IO.StreamWriter sw; System.IO.StreamWriter temp; int i = 0; public override void PreExecute() { base.PreExecute(); sw = new System.IO.StreamWriter(@"D:\flatfile.txt"); sw.WriteLine(String.Format("{0}", Variables.RowCount)); } public override void PostExecute() { base.PostExecute(); sw.Flush(); sw.Close(); //string count = i.ToString(); //string holder = ""; //int countint = 0; //if (count.Length < 5) //{ // countint = (5 - count.Length); // for (i = 1; i <= countint; i++) // { // holder = "0" + holder; // } // count = holder + count; //} //temp = new System.IO.StreamWriter(@"D:\tempfile.txt"); //System.IO.StreamReader reader = new System.IO.StreamReader(@"D:\flatfile.txt"); //temp.WriteLine(String.Format("00|{0}", count)); //while (!reader.EndOfStream) //{ temp.WriteLine(reader.ReadLine()); } //temp.Flush(); //temp.Close(); //reader.Close(); //System.IO.File.Copy(@"D:\tempfile.txt", @"D:\flatfile.txt", true); //System.IO.File.Delete(@"D:\tempfile.txt"); } public override void Input0_ProcessInputRow(Input0Buffer Row) { sw.WriteLine(String.Format("01|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}", Row.clientcode, Row.clientname, Row.branchcode, Row.terminalcode, Row.familycode, Row.dealercode, Row.teamleadercode, Row.introducercode)); sw.WriteLine(String.Format("02|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}", Row.activeflag, Row.onesidebrokerage, Row.deliverybrokerage, Row.internettradingclient, Row.servicetax, Row.spanmargin, Row.mtmmargin, Row.chargeturnovertax, Row.chargeothercharges, Row.chargestampcharges, Row.chargeturnoverbrokerage, Row.chargeinterest, Row.chargevolatilitymargin, Row.enableexchangemarginrules, Row.derivativesnetrate4digit, Row.econfirmation)); sw.WriteLine(String.Format("03|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}", Row.accountopeningdate, Row.dpcode1, Row.dpid1, Row.beneficiaryname1, Row.powerofattorney1, Row.marginbenefit1, Row.dpcode2, Row.dpid2, Row.beneficiaryname2, Row.powerofattorney2, Row.marginbenefit2, Row.dpcode3, Row.dpid3, Row.beneficiaryname3, Row.powerofattorney3, Row.marginbenefit3)); sw.WriteLine(String.Format("04|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}|{16}|{17}|{18}|{19}|{20}|{21}", Row.prefix, Row.addressline1, Row.addressline2, Row.addressline3, Row.city, Row.pincode, Row.country, Row.telephoneno1, Row.telephoneno2, Row.telephoneno3, Row.faxno, Row.mobileno, Row.pager, Row.referredby, Row.state, Row.stdcode, Row.contractdeliverystatus, Row.sharedeliverystatus, Row.billdeliverystatus, Row.panno, Row.sebiregno, Row.mapinid)); sw.WriteLine(String.Format("05|{0}|{1}|{2}|{3}|{4}|{5}", Row.email, Row.emailcc, Row.emailbcc, Row.authorizationtype, Row.authorizationdate, Row.clientstatus)); sw.WriteLine(String.Format("06|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}|{16}|{17}|{18}|{19}|{20}|{21}|{22}", Row.passportno, Row.passportissueplace, Row.passportissuedate, Row.drivinglicenseno, Row.drivinglicissueplace, Row.drivinglicissuedate, Row.voteridnumber, Row.registrationnumber, Row.registrationauthority, Row.registrationplace, Row.registrationdate, Row.voteridissueplace, Row.voteridissuedate, Row.rationcardno, Row.rationcardissueplace, Row.rationcardissuedate, Row.drivinglicexpirydate, Row.passportexpirydate, Row.uccclientcategory, Row.nameofnonindclient, Row.contactperson, Row.agreementdate, Row.dateofbirth)); sw.WriteLine(String.Format("07|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}", Row.printcontract, Row.printbill, Row.printcontractatho, Row.printbillatho, Row.payoutstatusquo, Row.dematgrade, Row.lienapplicable, Row.sebimtf)); sw.WriteLine(String.Format("08|{0}|{1}|{2}|{3}|{4}|{5}", Row.bankcode, Row.bankname, Row.bankbranchcode, Row.accountnumber, Row.accounttype, Row.chequeprintname)); sw.WriteLine(String.Format("09|{0}|{1}|{2}|{3}|{4}", Row.jobbingbrokeragecode1, Row.deliverybrokeragecode1, Row.carryforwardbrokeragecode1, Row.turnoverbrokeragecode1, Row.extrachargescode1)); sw.WriteLine(String.Format("10|{0}|{1}|{2}|{3}|{4}", Row.jobbingbrokeragecode2, Row.deliverybrokeragecode2, Row.carryforwardbrokeragecode2, Row.turnoverbrokeragecode2, Row.extrachargescode2)); sw.WriteLine(String.Format("11|{0}|{1}|{2}|{3}|{4}", Row.contractheaderline1, Row.contractheaderline2, Row.contractheaderline3, Row.contractheaderline4, Row.contractheaderline5)); sw.WriteLine(String.Format("12|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}", Row.userdefinedfield1, Row.userdefinedfield2, Row.userdefinedfield3, Row.userdefinedfield4, Row.userdefinedfield5, Row.userdefinedfield6, Row.userdefinedfield7, Row.userdefinedfield8, Row.userdefinedfield9)); sw.WriteLine(String.Format("13|{0}|{1}|{2}|{3}|{4}|{5}", Row.clientcashmarginaccount, Row.clientfomarginaccount, Row.cashbaseorfloorslabs, Row.futuresbaseorfloorslabs, Row.optionsbaseorfloorslabs, Row.riskprofilecode)); sw.WriteLine(String.Format("14|{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}|{16}|{17}|{18}|{19}|{20}|{21}|{22}|{23}|{24}|{25}|{26}|{27}|{28}|{29}|{30}|{31}|{32}|{33}|{34}|{35}", Row.firstremissiercode, Row.firstjobbingbrokerage, Row.firstjobbingbrokerageinrsorpercent, Row.firstdeliverybrokerage, Row.firstdeliverybrokerageinrsorpercent, Row.firstfuturesbrokerage, Row.firstfuturesbrokerageinrsorpercent, Row.firstoptionsbrokerage, Row.firstoptionsbrokerageinrsorpercent, Row.secondremissiercode, Row.secondjobbingbrokerage, Row.secondjobbingbrokerageinrsorpercent, Row.seconddeliverybrokerage, Row.seconddeliverybrokerageinrsorpercent, Row.secondfuturesbrokerage, Row.secondfuturesbrokerageinrsorpercent, Row.secondoptionsbrokerage, Row.secondoptionsbrokerageinrsorpercent, Row.thirdremissiercode, Row.thirdjobbingbrokerage, Row.thirdjobbingbrokerageinrsorpercent, Row.thirddeliverybrokerage, Row.thirddeliverybrokerageinrsorpercent, Row.thirdfuturesbrokerage, Row.thirdfuturesbrokerageinrsorpercent, Row.thirdoptionsbrokerage, Row.thirdboptionsbrokerageinrsorpercent, Row.fourthremissiercode, Row.fourthjobbingbrokerage, Row.fourthjobbingbrokerageinrsorpercent, Row.fourthdeliverybrokerage, Row.fourthdeliverybrokerageinrsorpercent, Row.fourthfuturesbrokerage, Row.fourthfuturesbrokerageinrsorpercent, Row.fourthoptionsbrokerage, Row.fourthoptionsbrokerageinrsorpercent)); //i++; } }

have mailed you with subject as DTSX Package

this seems OK, what is exact error you received ?http://www.rad.pasfu.com

here;s the screenshot : http://connect.nirmalbang.net/1.png

OK, I checked it. seems that this is a bug in SSIS, because RowCount variable , was changed the ReadOnly property to true ! change the ReadOnly property of RowCount variable to false, and then try again.http://www.rad.pasfu.com

ok... i changed that now i have a new error ! :( http://connect.nirmalbang.net/untitled.PNG

ok... i changed that now i have a new error ! :( http://connect.nirmalbang.net/untitled.PNG did you add this variable to the ReadOnlyVariables list of script component? ( after this change always check the ReadOnly property of variable to be false )http://www.rad.pasfu.com

Glad to solve:) that was interesting question in experts-exchange, I missed it there, I always check SSIS zone of experts-exchange for new questions. but I didn't see this one! Regards, http://www.rad.pasfu.com

yes... the var is readonly in script component and readonly property is false in the var's properties

thanks!! that totally worked now can you please copy paste your soln here so that i may award you points: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26765008.html

OK, seems there are weird problems with row count transform in this case. so I changed way a bit: remove row count transform from data flow task, connect data path directly form oledb source to the script component destination. remove @[User::RowCount] variable from ReadOnlyVariables list of script component. remove RowCount Variable from list of package variable. go back to control flow. create new package variable of Int type, name it as TotalRows then add an execute sql task, set connection , then write this sql query there: select count(*) as Cnt from myTable instead of myTable use your table name then set ResultSet property in general tab as single row. then go to result set tab, set result name as Cnt , and set variable name as User::TotalRows add a script task in the control flow right after execute sql task, connect green arrow ( precedence constraint) from execute sql task to this script task. in script task editor, set User::TotalRows as ReadOnlyVariable, write this script in the main() method: public void Main() { System.IO.StreamWriter sw = new System.IO.StreamWriter(@"d:\testToday.txt",false); sw.WriteLine(Dts.Variables["User::RowCount"].Value.ToString()); sw.Flush(); sw.Close(); // TODO: Add your code here Dts.TaskResult = (int)ScriptResults.Success; } then save it and close editor, connect precedence constraint from script task to data flow task go in data flow task and open script component editor, change the PreExecute method as below: public override void PreExecute() { base.PreExecute(); System.IO.StreamReader sr = new System.IO.StreamReader(@"d:\testToday.txt"); string firstRow = sr.ReadLine(); sr.Close(); sw = new System.IO.StreamWriter(@"d:\testToday.txt"); sw.WriteLine(firstRow); } save it and close it. now run, you wouldn't got problem again. http://www.rad.pasfu.com