locked
increment value in ssis RRS feed

  • Question

  • hi

    i am using script task and i need 1 variable which has value 00000

    when first time file runs it should be 00001,second time it should be 00002

    how to achieve that in variable.

    please help 

    • Changed type SSISJoostMVP Monday, February 16, 2015 8:29 PM More appropriate type
    Monday, February 16, 2015 6:37 PM

Answers

  • can u show mw mwthod how to store in table.file 

    and use it in ssis

    my output comes from stored procedure not from the table.so is thee any way i can use it from stored proc


    1) Just create a table with an integer column (is identity) and optional a string column to store the filename or a date column to store the process date.

    2) With an Execute SQL Task you can get data from this table. You could for example insert a record with the filename or a date and then retrieve the identity column with an @@IDENTITY select query.


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

    Tuesday, February 17, 2015 7:14 AM

All replies

  • Hi coool_sweet, 

    what do you mean exactly for "first time the file runs"? Does the package iterate over files? Did you mean "the first time the package executes"?

    If you are in the first scenario, you can have an integer variable in your package and change its value inside your Script Task. Something like Dts.Variables["User::VariableName"].Value = Dts.Variables["User::VariableName"].Value + 1;

    If you are in the second scenario, I'd use a configuration table in order to achieve such control or a parent package who calls the child package and stores the counter variable.

    Regards

    Pau.

    Monday, February 16, 2015 6:44 PM
  • hi

    its when first file generated i need to have that sequence number like 00001 in my footer.

    i am using script task but its in vb, how to increment it here.

    here  i declare 1 variable increment, which i am appending in my footer,

    but every time file generated its coming as 1 not incrementing

    Public Overrides Sub CreateNewOutputRows()
            Dim vars As IDTSVariables100
            Dim increament As Integer = 1



            Dim recordCount As Integer


            ' Dim recount As Integer

            '  Dim rcount As Integer = 0

            'Get the record count
            Me.VariableDispenser.LockOneForRead("RecordCount", vars)
            recordCount = CType(vars("RecordCount").Value, Integer)
            ' rdate = CType(vars("CurentDate").Value, Date)
            vars.Unlock()

          



            'Me.VariableDispenser.LockOneForRead("rcount", vars)
            'rcount = CType(vars("rcount").Value, Integer)
            'vars.Unlock()

            '  rcount = rcount + 1
            'Output one row with record count
            increament = increament + 1
            Output0Buffer.AddRow()
            Output0Buffer.FooterRow = String.Format("ABC" & recordCount & Variables.rdate & Space(1) & Variables.rcount & Space(1) & Variables.increment)




            Output0Buffer.SetEndOfRowset()


        End Sub


    • Edited by coool_sweet Monday, February 16, 2015 7:12 PM
    Monday, February 16, 2015 7:03 PM
  • You have to store it somewhere (in a file or database). If you store it in a variable it is only temporary. Next time you run the package it's gone...

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

    Monday, February 16, 2015 8:32 PM
  • can u show mw mwthod how to store in table.file 

    and use it in ssis

    my output comes from stored procedure not from the table.so is thee any way i can use it from stored proc


    • Edited by coool_sweet Tuesday, February 17, 2015 12:35 AM
    Tuesday, February 17, 2015 12:35 AM
  • can u show mw mwthod how to store in table.file 

    and use it in ssis

    my output comes from stored procedure not from the table.so is thee any way i can use it from stored proc


    1) Just create a table with an integer column (is identity) and optional a string column to store the filename or a date column to store the process date.

    2) With an Execute SQL Task you can get data from this table. You could for example insert a record with the filename or a date and then retrieve the identity column with an @@IDENTITY select query.


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

    Tuesday, February 17, 2015 7:14 AM