SSIS setting a variable in script componet and reading it at control flow.

Posted by Skraghava under Sql Server on 10/5/2013 | Points: 10 | Views : 2734 | Status : [Member] | Replies : 5
Hi Sir,
I have new to SSIS and doing some implementations. First I have a dataflow task, which runs some business logic to decide if record has to be migrated or not. From the data flow task I have to give back a flag/(set some user variable @varCheckCustomer=1 / 0 based on if need to be migrated or not, with the help of script component in postexecute event) and based on that I have to call the Migrate Customer task. However this logic is not working. Please let me know if you have any suggestion for this.

Thanks and regards,
Raghavendra SK



Responses

Posted by: Bandi on: 10/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer
http://hernandezpaul.wordpress.com/2012/12/14/read-and-write-variables-in-a-script-component-in-ssis-sql-server-integration-services-using-c/
http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
http://dwbi1.wordpress.com/2011/06/07/ssis-update-a-variable-based-on-a-file-script-component/

http://www.programmersedge.com/post/2009/07/29/SSIS-Reading-and-Writing-to-Variables-in-Script-Task.aspx#.UlDUaV18uo8

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Skraghava on: 10/5/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu,

Thanks for the answer and my code is as below.
VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
variableDispenser.LockForWrite("User::varCheckCustomer");
IDTSVariables100 vars;
variableDispenser.GetVariables(out vars);
vars["User::varCheckCustomer"].Value = 0;
vars.Unlock();

However still when i come out of the data flow, i have condition saying varCheckCustomer ==1 to continue. its coninueing with out any issue.

Thanks and regards,
Raghavendra SK

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
May be try this?
Check whether varCheckCustomer variable getting assigned or not?
VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
variableDispenser.LockForWrite("User::varCheckCustomer");
IDTSVariables100 vars;
variableDispenser.GetVariables(out vars);
try
{
vars["User::varCheckCustomer"].Value = 0;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
vars.Unlock();
}



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link to get know about variables scope in SSIS tasks
http://stackoverflow.com/questions/13180475/how-can-i-access-a-variable-across-script-tasks-within-a-package
https://www.simple-talk.com/sql/ssis/working-with-variables-in-sql-server-integration-services/

EDIT: To debug Script component
http://beyondrelational.com/modules/12/tutorials/24/tutorials/9086/getting-started-with-ssis-part-9-debugging-ssis-packages.aspx
http://www.sqlservercentral.com/Forums/Topic462532-148-1.aspx
http://www.youtube.com/watch?v=Saxh04VgmqQ

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Skraghava, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response