none
HOW TO ASSIGN A VALUE TO A VARIABLE IN SSIS DATAFLOW TASK

    Question

  • I have a flatfile called customer.txt which has only one line

    0000000000777770000000000000

    I need to extract '77777' from the flat file ,which I have done. Now I wanna assign this value to a variable called ' @RecCount'

    How do I do this.Please help me out, need this for my ongoing project.

    Regards

    Praveen...

    Thursday, November 22, 2012 11:59 AM

Answers

  • Right it in Global Temp Table. with your database, make sure that in proterties you have retain same connection ' true' and delayed Validation True..

    In your database create a same temp table which will hold your value to be extracted. just to debug your ssis . before running your package, drop that table from database.

    Now

    Before your data flow, In your control flow, drag ExecuteSql task

    set Result set to single row. connection to your database Sql statement .. depending on your query, lets say, seletct top 1 from ##temptable

    Now go to results and add a result.  The variable should be the one you want to assign value to now. and name of variable ... i usually have 0.

    Hope it helps.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    • Marked as answer by Eileen Zhao Thursday, November 29, 2012 7:52 AM
    Friday, November 23, 2012 4:41 AM
  •  For you C# code.  In Script task , your variable which will give the value will be Read For Example @ Cust

    Then In Script task Of C#

    After Public Main ()

    and between { }

    Dts.Variables ["cust"].value= Dts.Variables["infile"].Value.tostring();

    Below this just for debugging add this

    Messagebox.Show(Dts.Variables["infile"].Value.Tostring());

    I guess so , not very good at C#


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .


    • Edited by Dia.Agha Friday, November 23, 2012 5:50 AM
    • Marked as answer by Eileen Zhao Thursday, November 29, 2012 7:52 AM
    Friday, November 23, 2012 5:30 AM

All replies

  • You can use a script component for that.

    ps: your caps lock is broken


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, November 22, 2012 12:15 PM
  • Is there a way to do this without script component.
    Thursday, November 22, 2012 12:22 PM
  • Sure. Write it to a table. Then use an Execute SQL Task to retrieve the value and store it in a variable.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, November 22, 2012 12:24 PM
  • The easiest way is the Script Component like Koen mentioned. The Script Component and the Row count are the only two components that can write to a variable. The only other easy way is to write the value to a table and use the Execute SQL Task to store it in a variable...


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, November 22, 2012 1:01 PM
  • Can some one help me with the code for  a script task which takes the no: of rows of from a flat file 'customer.txt' and assigns it to a variable say @cust

    Path of customer is E:\Input\customer.txt which is assigned to a variable @infile

    and also it should read 'custcheck.txt '(has only one line)  , and extract 77777 from 0000000000000000000000000000000000+000077777720120705151348 and should assign the value to a variable say @custcheck.

    Path of 'custcheck.txt' is E:\Input\custcheck.txt which is assigned to a variable @check

    Note: The string given above (0000000000000000000000000000000000+000077777720120705151348) is the exact string Iam working with.

    Hope this info is enough..let me know if Iam missing something...appreciate all the help!

    Regards

    Praveen...

    Thursday, November 22, 2012 2:28 PM
  • Right it in Global Temp Table. with your database, make sure that in proterties you have retain same connection ' true' and delayed Validation True..

    In your database create a same temp table which will hold your value to be extracted. just to debug your ssis . before running your package, drop that table from database.

    Now

    Before your data flow, In your control flow, drag ExecuteSql task

    set Result set to single row. connection to your database Sql statement .. depending on your query, lets say, seletct top 1 from ##temptable

    Now go to results and add a result.  The variable should be the one you want to assign value to now. and name of variable ... i usually have 0.

    Hope it helps.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    • Marked as answer by Eileen Zhao Thursday, November 29, 2012 7:52 AM
    Friday, November 23, 2012 4:41 AM
  •  For you C# code.  In Script task , your variable which will give the value will be Read For Example @ Cust

    Then In Script task Of C#

    After Public Main ()

    and between { }

    Dts.Variables ["cust"].value= Dts.Variables["infile"].Value.tostring();

    Below this just for debugging add this

    Messagebox.Show(Dts.Variables["infile"].Value.Tostring());

    I guess so , not very good at C#


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .


    • Edited by Dia.Agha Friday, November 23, 2012 5:50 AM
    • Marked as answer by Eileen Zhao Thursday, November 29, 2012 7:52 AM
    Friday, November 23, 2012 5:30 AM