Hi, I want to read variable into SSIS Script Component Task and generate a column based on that value. I am not able to read value from variable in my case. Thanks Please vote for answer if it helps you.

That is possible: Variables in a Script Component: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html Example of a row id in a Script Component: http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html But if you only want to use the variable without any adjustments, you could also use a Derived Column to generate a new column with a value based on an integer variable. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

1) You could mention the variable in the ReadVariables tab of the script component and read inside the script using Dts.Variables["test"].Value.Happy to help! Thanks. Regards and good Wishes, Deepak.

Great blog articles, Is that possible to convert C# to VB.NET? :-))))Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Great blog articles, Is that possible to convert C# to VB.NET? :-)))) Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/ That's on my todo list... but I can give one priority if you're interested in a particular article. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi, Thanks for replies! But I am using exact code from link :http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html Still it is not able to pick value from variable. Package is running without any errors or warnings. I have put MsgBox, SCript variable does not get variable value at all. (rowCounter)Please vote for answer if it helps you.

Hi, Thanks for replies! But I am using exact code from link :http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html Still it is not able to pick value from variable. Package is running without any errors or warnings. I have put MsgBox, SCript variable does not get variable value at all. (rowCounter) Please vote for answer if it helps you. Let's eliminate some causes... 1) make sure the scope of the variable is on the package level and not on a single task. If not recreate variable on the right level and check again. 2) Disable the Execute SQL Task and fill the integer variable with a default value. Now check with a messagebox if you can read the value 3) Use the other method to read variable in a Script Component: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html select the variable as readwrite variable and use code like this instead: public override void PostExecute() { base.PostExecute(); this.Variables.Counter = rowCounter; } public override void Input0_ProcessInputRow(Input0Buffer Row) { Counter++; Row.Row_id = rowCounter; } public override void PreExecute() { base.PreExecute(); // Fill the internal variable with the value of the SSIS variable rowCounter = this.Variables.Counter; } Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi, Thanks for replies! But I am using exact code from link :http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html Still it is not able to pick value from variable. Package is running without any errors or warnings. I have put MsgBox, SCript variable does not get variable value at all. (rowCounter) Please vote for answer if it helps you. Let's eliminate some causes... 1) make sure the scope of the variable is on the package level and not on a single task. If not recreate variable on the right level and check again. 2) Disable the Execute SQL Task and fill the integer variable with a default value. Now check with a messagebox if you can read the value 3) Use the other method to read variable in a Script Component: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html select the variable as readwrite variable and use code like this instead: public override void PostExecute() { base.PostExecute(); this.Variables.Counter = Counter; } public override void Input0_ProcessInputRow(Input0Buffer Row) { Counter++; Row.Row_id = Counter; } public override void PreExecute() { base.PreExecute(); // Fill the internal variable with the value of the SSIS variable rowCounter = this.Variables.Counter; } Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Ok , I will let you know, added your web site to my SSIS library,.... keep blogingBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

SSISJoost, I used your code. While running package from studio I get popup of Error saying :The collection of variables locked for read and write access is not available outside of PostExecute. Not sure what to do at this point, any ideas? I removed postExecute part and got component working. Used following: System.Windows.Forms.MessageBox.Show(this.Variables.Cntr.ToString()); ---> Though value is 10000 it is showing 0 in popup box. Same when I used rowCounter for display. System.Windows.Forms.MessageBox.Show(rowCounter.ToString());Please vote for answer if it helps you.

You can't use the variable in the method Input0_ProcessInputRow. It's only available in the PreExecute to get the variable (and optional in the PostExecute to set it for the next task) So if you want to use it in the Input0_ProcessInputRow you will first have to put the value in a .NET variable (not ssis variable). See rowCounter variable in the script a post above. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

You can't use the variable in the method Input0_ProcessInputRow. It's only available in the PreExecute to get the variable (and optional in the PostExecute to set it for the next task) So if you want to use it in the Input0_ProcessInputRow you will first have to put the value in a .NET variable (not ssis variable).Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Yeah, I have used variable in PreExecute only and used .NET variable in Input0_ProcessInputRow. Ok, I did little change: I just changed datatype of variable to String and code worked fine till reading the value.I could read value from variable. Still have issues with assigning rowCounter value back to variable. One update :If I declare another variable to capture return value it works perfectly fine. I think it has to do with some locking issues. /* 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 { private int rowCounter; public override void PreExecute() { base.PreExecute(); rowCounter = Convert.ToInt32(this.Variables.Variable); System.Windows.Forms.MessageBox.Show(this.Variables.Variable); System.Windows.Forms.MessageBox.Show(rowCounter.ToString()); } public override void PostExecute() { base.PostExecute(); this.Variables.Variable = rowCounter.ToString(); System.Windows.Forms.MessageBox.Show(this.Variables.Variable.ToString()); } public override void Input0_ProcessInputRow(Input0Buffer Row) { rowCounter++; Row.OColumn = rowCounter; } } my code: Please vote for answer if it helps you.

Just checking... so if you leave/comment out the PostExecute method there are no errors and if you add the method it returns an error message: The collection of variables locked for read and write access is not available outside of PostExecute.?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Yes, You are right there.Please vote for answer if it helps you.

Yes, You are right there. Please vote for answer if it helps you. That sounds strange... and what about commenting out the PreExecute instead of the PostExecute? (fill your rowCounter variable a default value in the script).Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com