Hi, I have a ado .net datasource reader connecting to a progress database thru ODBC that connects to a transformation script. The data comes as follow year-mont and dayli values: "2010-01" "10;10,10...10" So in this example I`d like to create for january 2010, 31 rows with the correct values. Now, I can create an array with split(dayli-values,";"), than I can make a While with Ubound(array) But how can I add each row to the output? I tried the outputbuffer.addrow, but I could not find the asyn option. what am I doing wrong? Thanks

Not sure if I understood what you need exactly, but it seems that you need a Derived Column Transformation. Please take a look at http://sqlblog.com/blogs/andy_leonard/archive/2010/11/10/11628.aspx it is a short how to using expressions, which I think is what you need.Arthur My Blog

have a look at my post. its not the same but may be you could get something here. Meanwhile I did not understand ur requirement well. Could you provide sample input record and the expected output record? Tweet me..

Not sure if I understood what you need exactly, but it seems that you need a Derived Column Transformation. Please take a look at http://sqlblog.com/blogs/andy_leonard/archive/2010/11/10/11628.aspx it is a short how to using expressions, which I think is what you need. Going by the post title, you can not have multiple rows as output for one row as the input. Tweet me..

Samples row (YearMonth, Values) "201001","1;3;4;2" Output (Day,Dayli Euro Cotation): 2010-01-01, 1 2010-01-02, 3 2010-01-03, 4 2010-01-02, 2 So, my euro cotation is stored in a char field with the amount for each day separated by semi-colom I tryed with pivot tool, but I had a problem because months have a diffente amoutn of days and some days are invalid and become error rows (like february 31)

Use the below code: using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; //Add this Namespace for IO Operations using System.IO; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { //StramReader to read the input file stream private StreamReader textReader; //String to save the source file path private string srcFilePath; public override void PreExecute() { base.PreExecute(); srcFilePath = @"I:\SSIS\BLOGS\One2Many\Input\one2many.txt"; //Set the textReader at the PreExecute Phase so that we donot initialize it for each record. textReader = new StreamReader(srcFilePath); } public override void CreateNewOutputRows() { string nextLine; string[] columns; string[] col2; string day; char[] colDelimiters; char[] rowDelimiters; colDelimiters = ",".ToCharArray(); rowDelimiters = ";".ToCharArray(); //Read next line from the file to the string variable nextLine = textReader.ReadLine(); //Read the file till nextLine variable is not NULL ie. EOF while (nextLine != null && nextLine.Length>0) { //Split the records by commas to later extract the data and not the column Name in each record. columns = nextLine.Split(colDelimiters); col2 = columns[1].Substring(1,columns[1].Length-2).Split(rowDelimiters); day = columns[0].Substring(1, 4) + "-" + columns[0].Substring(5, 2) + "-"; foreach (string str in col2) { string str1 = "0"+str; //Add output this.OutputBuffer.AddRow(); //Add the column values for the row added above. this.OutputBuffer.Day = day+ str1; this.OutputBuffer.DayliEuroCotation = str1.Substring(str1.Length-2); } //Read the next line nextLine = textReader.ReadLine(); } } public override void PostExecute() { base.PostExecute(); //Close the Text reader once the file has been read in the PostExecute Phase. textReader.Close(); } } Tweet me..

Hi, This works if I am using a Text file as source, however, I am using a ADO .NET that points to a ODBC. My query is SELECT YearMonth, Values from MOV.CotationData The part I am very interested in is the ADDROW on outputbuffer but it doesnt seem available on the sub Input0_ProcessInputRow . when I write outputbuffer.addrow() it says outputbuffer is not declared

You won't have this capability if you configured the Script as a Transformation when you dropped it on the data flow. You'll have to select "Source" as the type of Script when you drop it. Talk to me now on

OK, it makes sense.. even the format of the script component sub's are like in the example. Now, how do I reade from my ADO .NET data source? I have never made a script like this before. Can you guys help me?

Well... I found many sample scripts everywhere... and create one of my own. I still have error. I cannot use my query because it should come from a Variable and I cannot write to the output buffer because I dont know how to make type casts. So I get error at these 4 lines: SqlCommand cmd = new SqlCommand(vars["QUERY"].Value, sqlConn); this.Output0Buffer.CotationDate = day + str1; this.Output0Buffer.CotationValue = str1.Substring(str1.Length - 2); this.Output0Buffer.CotationCurrency = sqlReader.GetInt32(0); Here is the full script so far... /* 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.Data.SqlClient; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { IDTSConnectionManager100 connMgr; SqlConnection sqlConn; SqlDataReader sqlReader; public override void AcquireConnections(object Transaction) { connMgr = this.Connections.Connection; sqlConn = (SqlConnection)connMgr.AcquireConnection(null); } public override void PreExecute() { Variables vars; vars = this.Variables; base.PreExecute(); SqlCommand cmd = new SqlCommand(vars["QUERY"].Value, sqlConn); sqlReader = cmd.ExecuteReader(); /* Add your code here for preprocessing or remove if not needed */ } public override void PostExecute() { base.PostExecute(); sqlReader.Close(); /* Add your code here for postprocessing or remove if not needed You can set read/write variables here, for example: Variables.MyIntVar = 100 */ } public override void CreateNewOutputRows() { string[] col2; string day; string YearMonth; string ValueArray; char[] rowDelimiters; rowDelimiters = ";".ToCharArray(); while (sqlReader.Read()) { YearMonth = sqlReader.GetString(0); ValueArray = sqlReader.GetString(3); col2 = ValueArray.Substring(1, ValueArray.Length - 2).Split(rowDelimiters); day = YearMonth.Substring(1, 4) + "-" + YearMonth.Substring(5, 2) + "-"; foreach (string str in col2) { string str1 = "0" + str; //Add output this.Output0Buffer.AddRow(); //Add the column values for the row added above. this.Output0Buffer.CotationDate = day + str1; this.Output0Buffer.CotationValue = str1.Substring(str1.Length - 2); this.Output0Buffer.CotationCurrency = sqlReader.GetInt32(0); } /* Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". */ } } }

Well... I found many sample scripts everywhere... and created one of my own. It still has errors. I cannot use my query because it should come from a Variable and I cannot write to the output buffer because I dont know how to make type casts. So I get error at these 4 lines: SqlCommand cmd = new SqlCommand(vars["QUERY"].Value, sqlConn); this.Output0Buffer.CotationDate = day + str1; this.Output0Buffer.CotationValue = str1.Substring(str1.Length - 2); this.Output0Buffer.CotationCurrency = sqlReader.GetInt32(0); See full script below using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Data.SqlClient; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { IDTSConnectionManager100 connMgr; SqlConnection sqlConn; SqlDataReader sqlReader; public override void AcquireConnections(object Transaction) { connMgr = this.Connections.Connection; sqlConn = (SqlConnection)connMgr.AcquireConnection(null); } public override void PreExecute() { Variables vars; vars = this.Variables; base.PreExecute(); SqlCommand cmd = new SqlCommand(vars["QUERY"].Value, sqlConn); sqlReader = cmd.ExecuteReader(); /* Add your code here for preprocessing or remove if not needed */ } public override void PostExecute() { base.PostExecute(); sqlReader.Close(); /* Add your code here for postprocessing or remove if not needed You can set read/write variables here, for example: Variables.MyIntVar = 100 */ } public override void CreateNewOutputRows() { string[] col2; string day; string YearMonth; string ValueArray; char[] rowDelimiters; rowDelimiters = ";".ToCharArray(); while (sqlReader.Read()) { YearMonth = sqlReader.GetString(0); ValueArray = sqlReader.GetString(3); col2 = ValueArray.Substring(1, ValueArray.Length - 2).Split(rowDelimiters); day = YearMonth.Substring(1, 4) + "-" + YearMonth.Substring(5, 2) + "-"; foreach (string str in col2) { string str1 = "0" + str; //Add output this.Output0Buffer.AddRow(); //Add the column values for the row added above. this.Output0Buffer.CotationDate = day + str1; this.Output0Buffer.CotationValue = str1.Substring(str1.Length - 2); this.Output0Buffer.CotationCurrency = sqlReader.GetInt32(0); } /* Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". */ } } }

Another change, this time I am trying to fix previou issues and to make the connection work. I fixed the conversion problem and I guess I also fixed the way I get variables. I am trying to connect to a Progress database thru ODBC but I get the error: Error 1 Validation error. LOAD DAYS: LOAD DAYS: System.InvalidCastException: Unable to cast object of type 'System.Data.Odbc.OdbcConnection' to type 'System.Data.SqlClient.SqlConnection'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) SA_Dayli_Cotation.dtsx 0 0 Changes: using //using System.Data.SqlClient; OdbcConnection sqlConn; //SqlConnection sqlConn; // SqlDataReader sqlReader; this.Connections.Connection; sqlConn = ( OdbcConnection)connMgr.AcquireConnection(null); OdbcCommand cmd = new OdbcCommand(vars.QUERY , sqlConn); //SqlCommand cmd = new SqlCommand(vars.QUERY , sqlConn); //sqlConn = (SqlConnection)connMgr.AcquireConnection(null);OdbcDataReader sqlReader; System.Data.Odbc;

Lets see if I can help a bit. In "AcquireConnection", use the "Transaction" variable instead of "null" in your call to AcquireConnection. In PreExecute - Yes, your connection appears to be ODBC, not SQL, so use the ODBC namespaces instead (as you've done). Keep up the good work and rename all your variables so you don't confuse me ;) You need to override ReleaseConnections in order to call ReleaseConnection on your connection manager (to pair it up with the AcquireConnection call you make in the AcquireConnections override). In order to debug more/better, you need to provide us with data types of your output columns, as well as the exact error it's reporting - otherwise, I'm just guessing, so here are some guesses: CotationDate is a date type - so you can't assign it a string. You need to use something like: Output0Buffer.CotationDate = Convert.ToDateTime(day + str1); Assuming CotationValue is a number type of some sort, you'll have to do a similar thing, because you can't assign a string to a number type. The exact type is important! I bet this won't work, but it might give you someplace to start: Output0Buffer.CotationValue = Convert.ToInt32(str1.Substring(str1.Length - 2)); Again, CotationCurrency is probably a currency or other non-integral numeric type, and exact type is important, so something like: Output0Buffer.CotationCurrency = Convert.ToCurrency(sqlReader.GetInt32(0)); If any of that helps a little, that's good. If you're still stuck, post your revised code, then describe each error/problem - including the error description reported by the compiler and/or the runtime (if you get that far). Talk to me now on

OK I got it to work. Only I do not know what to do about the releaseconnection. I yelds an error. /* 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.Data.Odbc; using System.Windows.Forms; using System.Collections.Generic; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { IDTSConnectionManager100 connMgr; OdbcConnection OdbcConn; OdbcDataReader OdbcReader; public override void AcquireConnections(object Transaction) { connMgr = this.Connections.Connection; OdbcConn = (OdbcConnection)connMgr.AcquireConnection(Transaction); } // public override void ReleaseConnections(object Transaction) // { // connMgr.ReleaseConnection(Transaction); // base.ReleaseConnections(); // } public override void PreExecute() { Variables vars; vars = this.Variables; base.PreExecute(); OdbcCommand cmd = new OdbcCommand(vars.QUERY , OdbcConn); OdbcReader = cmd.ExecuteReader(); } public override void PostExecute() { base.PostExecute(); OdbcReader.Close(); } public override void CreateNewOutputRows() { string day; string YearMonth; string ValueArray; //while there is data in the dataset while (OdbcReader.Read()) { YearMonth = Convert.ToString(OdbcReader.GetString(2)); ValueArray = OdbcReader.GetString(3); day = YearMonth.Substring(0, 4) + "-" + YearMonth.Substring(4, 2) + "-"; string[] pedacinhos = ValueArray.Split(new char[] { ';' }); List<KeyValuePair<string, string>> Dias = new List<KeyValuePair<string, string>>(); //criar o array contendo os dias for (int i = 0; i < pedacinhos.Length; i++) { day = YearMonth.Substring(0, 4) + "-" + YearMonth.Substring(4, 2) + "-" +(i + 1).ToString(); KeyValuePair<string, string> value = new KeyValuePair<string, string>(day, pedacinhos[i]); Dias.Add(value); } foreach (KeyValuePair<string, string> item in Dias) { //while there are more itens in the array to become days // MessageBox.Show(item.Key); // MessageBox.Show(item.Value.Replace('.',',') ); // MessageBox.Show(Convert.ToString(Convert.ToDecimal(item.Value.Replace('.', ',')))); //Add output DateTime ret = new DateTime(); string date = item.Key; if (DateTime.TryParse(date, out ret)) { // data this.Output0Buffer.AddRow(); //Add the column values for the row added above. this.Output0Buffer.CotationDate = Convert.ToDateTime(item.Key); this.Output0Buffer.CotationValue = Convert.ToDouble(item.Value.Replace('.', ',')); this.Output0Buffer.CotationCurrency = Convert.ToInt32(OdbcReader.GetInt32(0)); } } } } }

What error does it throw? I would imagine it could be having a problem because you've already closed the connection in your PostExecute. Don't do that. Talk to me now on