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 : 3390 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

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

0	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

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

Latest Posts