Hi All, I have created a package for extracting data from a table and split the value of one column into three different rows. I use execute sql task to extract all data and load into a variable. Then use a For each loop. There is another execute sql task inside the loop. There is two For each loops inside the main For each loop. I did the same job using a sql script with a cursor. The script runs in 2 minutes. But the package takes more than 20 minutes. Is it because of three for each loops? Also I set the retained the same connection property to TRUE. I am not sure its related to performance. Thanksshamen

The RetainSameConnection property should have no effect on performance. It will be easier to troubleshoot performance if you explain us exactly what you are trying to do, preferably with sample data and desired output.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

I think you can implement it better in SSIS, tell us what are you trying to do actually and we will help you to find better solution. sample of input records and output desired will be helpful alsohttp://www.rad.pasfu.com

pls add more details so that we can suggest a solution

Source table has 15,000 rows. There is one column which has special characters, letters,numbers etc. I want to split values of that column and put it in a separate table. ex; ID Date Value 999122 Jan 10 //property value//200,20//interest rate//0.12 I want to break this value and put it in a table like this ID Date Descr Amount 9991222 Jan10 property value 200.20 991222 Jan10 Interest rate 0.12 So in my package, first task is sql execute task. It extracts data from the source table and put it in a object variable. Next step is a For each Loop. Connection is ADO net. There is another execute sql task inside the for each loop. Splitting is done in two stages. First splitting is done within this execute sql task. I am using dbo.split function inside the sql task. After that variable values are passing to another object variable. So there is another for each loop at the third step. One execute sql task inside the this for each loop. Second splitting is done within this sql task. As a final step, there is another for each loop. There is another execute sql task inside this loop. This is where pivoting and inserting are done. So basically there are two for each loops inside the main for each loop.The main for each loop loops through 15,000 rows. After first splitting the second for each loop loops through nearly 45,000 rows. After the second splitting third for each loop loops through nearly 125,000 rows. I dont see any other way doing this without using for each loop. Thanks in advance for your help . shamen

I would suggest to change the design. Use ole db source to read the data and one sql script transformation component to parse the value column and add additional rows to the output buffer. So the entire logic of parsing and pivoting rows will be done in the script component. In this way your package will be much smaller/simpler and faster.

Hi Shamen, What you could do is use a data flow task. OLEDB SOurce to query the data(better segregate the Value column into separeate columns in the source query). put a multicast and add 2 derived column(if you are getting the Value column data in one column) after the multicast to manipulate the data. Now add 2 oledb destination and map the appropriate columns to the 2 tables.My Blog | Ask Me

Hi Stanhe and Sudeep, I am using dbo.split function to split the value. select * from dbosplit('//', @Value) So I am using a variable in this case. Is it possible to use only data flow taks without using for each loops in this case? or else you mean use derived column and write an expression to split the value? Thanks shamen

Hi Shamen, yes that's the idea. You can use just one data flow task to transform source data. Inside the data flow task use use OLE DB Source to select rows from the source table. Next connect a script component to the OLE DB Source. It will have inside a method called like ..._ProcessInput(InputBuffer Buffer). This Method will be executed for each data row that will be selected from ole db source. So this is the right place to do the transformations. You could use string.Split function to split the value column by '//', this function will return an array of string values. Now you can loop through array elements and create new rows for Descr/Value pairs and add them to the output buffer. See for example http://msdn.microsoft.com/en-us/library/ms136133.aspx

Thanks for your suggestion..I will try this and let you know Thanksshamen

you can write expression to split down the column with expressions, but this is much easier way to use Script Component for splitting, this is a sample: first of all add output columns as much as needed, then in script write this .net code: string[] splittedArray=Row.InputColumn.Split('\\'); Row.OutputColumn0=splittedArray[0]; Row.OutputColumn1=splittedArray[1]; Row.OutputColumn2=splittedArray[2]; ... http://www.rad.pasfu.com

Hi Reza, I am not very familiar with VB# or C#. I guess i will have to pass value as a parameter here. How do I use parameter here: string[] splittedArray=Row.InputColumn.Split('\\'); Thanks shamen

why you want to use parameters? you don't need to be very familiar with .NET , just add output columns in Output0,Output1,... and write the code from my previous into Process_InputRow method.http://www.rad.pasfu.com

ok Thanksshamen

If you donot want to go for script, just split the column in the oledb source query to different columns. After the oledb source add a Multicast, from the multicast add 2 OLEDB Destination for the 2 tables. Now for each table do the column mapping as per your need.My Blog | Ask Me

Hi Shamen, if the amount column is of numeric type you'll need to cast the string value before you can write it into the column. If you are not very familiar with .NET then this solution will be probably a little bit complex for you. Consider the suggestion from Sundeep Raj. You can use same destination table in both OLE DB Destination components. HTH

If you donot want to go for script, just split the column in the oledb source query to different columns. After the oledb source add a Multicast, from the multicast add 2 OLEDB Destination for the 2 tables. Now for each table do the column mapping as per your need.My Blog | Ask Me

Thanks again. ID is string type. Date is date type and Value is string type. Even though I was not very familiar with .NET, still I wanted to try it. I found this link where i can see a good example of scrip task. http://www.codeproject.com/KB/miscctrl/SSIS_Script_Component.aspx I was trying to do the same thing as discribed in this article. There are one OLEDB source data flow task and script component. Input columns are ID,Date and Value. Output name allias are the same and usage type is read only. SynchronousInputID property was to None . three out put columns were added as ID_O, Date_O and Value_O. But I am getting an error message here. This is the sript: /* 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 { public override void PreExecute() { base.PreExecute(); /* Add your code here for preprocessing or remove if not needed */ } public override void PostExecute() { base.PostExecute(); /* 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 Input0_ProcessInput(Input0Buffer Buffer) { while (Buffer.NextRow()) { Input0_ProcessInputRow(Buffer); } if (Buffer.EndOfRowset()) { ResultBuffer.SetEndOfRowset(); } } public override void Input0_ProcessInputRow(Input0Buffer Row) { var arr = Row.value.Split('//'); // Splitting the rows of Names column foreach (string str in arr) { ResultBuffer.AddRow(); //Adding rows to the Result Buffer //If the Names are not empty or Null, then set the values to the // corresponding Result Buffer properties if (!string.IsNullOrEmpty(Row.Value)) { ResultBuffer.lD = Row.ID_O; ResultBuffer.Value_O = str; } } } public override void CreateNewOutputRows() { /* 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". */ } } Error 1 'Input0Buffer' does not contain a definition for ''Value' and no extension method 'Value' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\shamen\Local Settings\Temp\SSIS\ce03a50afdc242a8999ad6d0fc025db1\main.cs 47 23 sc_97ef0358a68d4f06801dae6755a602e9 Error 'ResultBuffer' does not contain a definition for ;ID' and no extension method ''ID' accepting a first argument of type 'ResultBuffer' could be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\shamen\Local Settings\Temp\SSIS\ce03a50afdc242a8999ad6d0fc025db1\main.cs 57 30 sc_97ef0358a68d4f06801dae6755a602e9 I am not sure why i get this erorr. As Reza mentioned in this thread before, Do I have to use three out put columns as Value_O1,Value_O2 and Value_O3. Thanks shamen

It seems like you are writing to ResultBuffer.ID which not exists, use ResultBuffer.ID_O = Row.ID. I guess that the first error regarding Value column is because you are using Row.value.Split... note that C# is case-sensitive, so if the column name is Value use Row.Value.Split You can first try to build your script before executing it, could speed up the development a little bit... if i remember correctly you'll find the build command under Debug menu item.

Yes I tried to build it before I execute the package. I even changed the resultbuffer as you suggested. Still I am getting the same error message. Also I guess I split is a built in function in C# and I am not supposed to build it by my self before I use it in my script? Thanks shamen

Shamen, did you try the approach I suggested?My Blog | Ask Me

Sudeep.. I wondered if its easier to use multi cast and derived column method as I am not sure how to use the split fucntion in the derived column task. Actually the actual values is much more complex than the value given in my example. In my original sql script, the value was splitted twise using dbo.split function in order to get the final result. I dont think I am able to write an expression in the derive column taks without using a dbo. split function. If i use the dbo.split function, I would have to use a variable. Select * from dbo.split ('[',@value) Then again I would have to use a loop. Or is it possible for me to use derived column or multi cast without using a vairable? Thanks shamen

If you could provide a sample for us to see we could help you better. Meanwhile for Script component you could have a look at my blogs on script components for help. http://beyondrelational.com/controlpanel/blogs/postlist.aspxMy Blog | Ask Me

1|Monthly Payment Amount|81.68|0.0012][2|Performance Payment amount|83.3333|999.9996][3|Initial Amount|83.3333|999.9996] This is the original value; Splitting 1: SELECT case when CHARINDEX('[',s)=0 then s when CHARINDEX('[',s)=1 then SUBSTRING(s, 2, CHARINDEX('[', s)+150 )end AS s FROM dbo.Split ('][' ,'1|Monthly Payment Amount|81.68|0.0012][2|Performance Payment amount|83.3333|999.9996][3|Initial Amount|83.3333|999.9996]') WHERE s<>'' Results1: 1|Monthly Payment Amount|81.68|0.0012 2|Performance Payment amount|83.3333|999.9996 3|Initial Amount|83.3333|999.9996 again these rows needed to be splitted. SELECT CASE WHEN pn ='1' THEN 'TYPE_CODE' WHEN pn IN ('2','5','8') THEN 'NAME' WHEN pn IN ('3','6') THEN 'PAYMENT_AMT' WHEN pn IN ('4','8') THEN 'BALANCE_AMT' ELSE 'Others' END AS pn, s FROM ( SELECT * FROM dbo.Split ('|' ,'1|Monthly Payment Amount|81.68|0.0012') ) AS A Results2: (only the result for first row of result1 is shown) TYPE_CODE 1 NAME Monthly Payment Amount PAYMENT_AMT 81.68 BALANCE_AMT 0.0012 This is just for one ID. This has to be done for every ID. Thanks shamen

Open BufferWrapper.cs file, can you see a definition for Value or ID property inside? This autogenerated class should contain one class for each input and output and typed proprties for each column. If you cannot see the property Value in the Input0Buffer class or property ID_O in ResultBuffer class then open the script editor and configure input columns, output and output columns properly.

This is what in bufferwarpper.cs file. /* THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! * Microsoft SQL Server Integration Services buffer wrappers * This module defines classes for accessing data flow buffers * THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! */ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; public class Input0Buffer: ScriptBuffer { public Input0Buffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public String LOANNUMBER { get { return Buffer.GetString(BufferColumnIndexes[0]); } } public bool LOANNUMBER_IsNull { get { return IsNull(0); } } public DateTime REPORTINGPERIOD { get { return Buffer.GetDate(BufferColumnIndexes[1]); } } public bool REPORTINGPERIOD_IsNull { get { return IsNull(1); } } public String MONTHLYCOMPENSATIONSTRING { get { return Buffer.GetString(BufferColumnIndexes[2]); } } public bool MONTHLYCOMPENSATIONSTRING_IsNull { get { return IsNull(2); } } new public bool NextRow() { return base.NextRow(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } public class ResultBuffer: ScriptBuffer { public ResultBuffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public DateTime DateO { set { this[0] = value; } } public bool DateO_IsNull { set { if (value) { SetNull(0); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String IDO { set { this[1] = value; } } public bool IDO_IsNull { set { if (value) { SetNull(1); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String ValueO { set { this[2] = value; } } public bool valueO_IsNull { set { if (value) { SetNull(2); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } new public void AddRow() { base.AddRow(); } new public void SetEndOfRowset() { base.SetEndOfRowset(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } Thanksshamen

This is what in bufferwarpper.cs file. /* THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! * Microsoft SQL Server Integration Services buffer wrappers * This module defines classes for accessing data flow buffers * THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! */ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; public class Input0Buffer: ScriptBuffer { public Input0Buffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public String ID { get { return Buffer.GetString(BufferColumnIndexes[0]); } } public bool ID_IsNull { get { return IsNull(0); } } public DateTime Date { get { return Buffer.GetDate(BufferColumnIndexes[1]); } } public bool Date_IsNull { get { return IsNull(1); } } public String Value { get { return Buffer.GetString(BufferColumnIndexes[2]); } } public bool Value_IsNull { get { return IsNull(2); } } new public bool NextRow() { return base.NextRow(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } public class ResultBuffer: ScriptBuffer { public ResultBuffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public DateTime DateO { set { this[0] = value; } } public bool DateO_IsNull { set { if (value) { SetNull(0); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String IDO { set { this[1] = value; } } public bool IDO_IsNull { set { if (value) { SetNull(1); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String ValueO { set { this[2] = value; } } public bool valueO_IsNull { set { if (value) { SetNull(2); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } new public void AddRow() { base.AddRow(); } new public void SetEndOfRowset() { base.SetEndOfRowset(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } Thanks shamen

This is what in bufferwarpper.cs file. /* THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! * Microsoft SQL Server Integration Services buffer wrappers * This module defines classes for accessing data flow buffers * THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! */ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; public class Input0Buffer: ScriptBuffer { public Input0Buffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public String ID { get { return Buffer.GetString(BufferColumnIndexes[0]); } } public bool ID_IsNull { get { return IsNull(0); } } public DateTime Date { get { return Buffer.GetDate(BufferColumnIndexes[1]); } } public bool Date_IsNull { get { return IsNull(1); } } public String Value { get { return Buffer.GetString(BufferColumnIndexes[2]); } } public bool Value_IsNull { get { return IsNull(2); } } new public bool NextRow() { return base.NextRow(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } public class ResultBuffer: ScriptBuffer { public ResultBuffer(PipelineBuffer Buffer, int[] BufferColumnIndexes) : base(Buffer, BufferColumnIndexes) { } public DateTime DateO { set { this[0] = value; } } public bool DateO_IsNull { set { if (value) { SetNull(0); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String IDO { set { this[1] = value; } } public bool IDO_IsNull { set { if (value) { SetNull(1); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } public String ValueO { set { this[2] = value; } } public bool valueO_IsNull { set { if (value) { SetNull(2); } else { throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead."); } } } new public void AddRow() { base.AddRow(); } new public void SetEndOfRowset() { base.SetEndOfRowset(); } new public bool EndOfRowset() { return base.EndOfRowset(); } } Thanks shamen

Hi Shamen, as you can see there are no properties for ID and Value in the Input0Buffer class. This means that input columns are not properly configured. Open Script Transformation Editor go to Input Columns and for Input 0 select ID and Value from available Input Columns. On the ResultBuffer the available Columns are IDO and ValueO. So use these properties: ResultBuffer.IDO = Row.ID; ResultBuffer.ValueO = Row.Value;

Hi Stanhe, I am sorry I did a mistake earlier. I was supposed to replace original column names with names in this example. I made the correction in my previous post. I have configured input and out put columns as described in this article. http://www.codeproject.com/KB/miscctrl/SSIS_Script_Component.aspx I did exactly the same way. But I am not sure why i am getting the error. Thanks shamen

Hi Stanhe, I am sorry I did a mistake earlier. I was supposed to replace original column names with names in this example. I made the correction in my previous post. I have configured input and out put columns as described in this article. http://www.codeproject.com/KB/miscctrl/SSIS_Script_Component.aspx I did exactly the same way. But I am not sure why i am getting the error. Thanks shamen

Hi Shamen, it's hard to figure out what is going wrong with your pakage without to look into it. The article you've mentioned contains sample packages. Have you tried to execute them? You could do a cross check of the script component in the sample and the script component in your package. This way you could figure out the reason.

Hi Shamen, it's hard to figure out what is going wrong with your pakage without to look into it. The article you've mentioned contains sample packages. Have you tried to execute them? You could do a cross check of the script component in the sample and the script component in your package. This way you could figure out the reason.

Yes..I will try that first. Thank you all for your help shamen

Yes..I will try that first. Thank you all for your help shamen