Post reply

How to loop the values in the package?

  • February 2, 2012 at 8:36 am

    #250122

    Hi Friends,

    i have a variable using inside the package and getting the value for that variable from a table which consists of single column and single record...

    i am getting the value using an Execute SQL Task....

    now i need to pass four values to the table which needs to loop the package.

    So how to loop the package with all the values?

    Gimme suggestion friends

    Thanks,
    Charmer

  • February 3, 2012 at 12:40 am

    #1442224

    What do you mean with "now i need to pass four values to the table which needs to loop the package."

    Do you need to insert data into the table?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 3, 2012 at 3:51 am

    #1442271

    Koen Verbeeck (2/3/2012)

    What do you mean with "now i need to pass four values to the table which needs to loop the package."

    Do you need to insert data into the table?

    no pal...i get the values from a table which will be used in the data flows one by one....

    i hope record set can be used along with For each loop container right?

    Thanks,
    Charmer

  • February 3, 2012 at 4:02 am

    #1442281

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    What do you mean with "now i need to pass four values to the table which needs to loop the package."

    Do you need to insert data into the table?

    no pal...i get the values from a table which will be used in the data flows one by one....

    i hope record set can be used along with For each loop container right?

    Ah ok. You meant "to pass four values from the table " 🙂

    Retrieve them with the Execute SQL Task and set the ResultSet to Full.

    Link it to an object variable.

    Now you can loop over this object variable using the For Each Loop container using the ADO iterator.

    Example:

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 3, 2012 at 4:14 am

    #1442288

    Koen Verbeeck (2/3/2012)

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    What do you mean with "now i need to pass four values to the table which needs to loop the package."

    Do you need to insert data into the table?

    no pal...i get the values from a table which will be used in the data flows one by one....

    i hope record set can be used along with For each loop container right?

    Ah ok. You meant "to pass four values from the table " 🙂

    Retrieve them with the Execute SQL Task and set the ResultSet to Full.

    Link it to an object variable.

    Now you can loop over this object variable using the For Each Loop container using the ADO iterator.

    Example:

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/

    Thanks Friend, Why am i not able to use the object variable in conditional split? it says the data type is not supported in expression....

    is there any reason behind this? or any other solution for this? help me friend..

    Thanks,
    Charmer

  • February 3, 2012 at 4:45 am

    #1442318

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 3, 2012 at 4:55 am

    #1442323

    Koen Verbeeck (2/3/2012)

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    could you temme how to map the object variable to a string variable in the for loop?

    Thanks,
    Charmer

  • February 3, 2012 at 5:51 am

    #1442355

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    could you temme how to map the object variable to a string variable in the for loop?

    That's explained in the article I linked to.

    Read all the way to the bottom.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 3, 2012 at 7:39 am

    #1442436

    Koen Verbeeck (2/3/2012)

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    could you temme how to map the object variable to a string variable in the for loop?

    That's explained in the article I linked to.

    Read all the way to the bottom.

    Pal, I could not find assigning object variable to a string variable or something in that article...

    Could you help me...

    Thanks,
    Charmer

  • February 3, 2012 at 2:08 pm

    #1442644

    Dim sLastName As String = Dts.Variables("LastName").Value.ToString

  • February 5, 2012 at 6:01 am

    #1442821

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    could you temme how to map the object variable to a string variable in the for loop?

    That's explained in the article I linked to.

    Read all the way to the bottom.

    Pal, I could not find assigning object variable to a string variable or something in that article...

    Could you help me...

    You loop over the object variable using the For Each Loop, and in each iteration you assign the current value to the string variable.

    It's in the article, right under picture 14.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 7, 2012 at 2:41 am

    #1443600

    Koen Verbeeck (2/5/2012)

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    Charmer (2/3/2012)

    Koen Verbeeck (2/3/2012)

    You can't use the object variable in a conditional split, as the component expects scalar values.

    In the For Each Loop, you need to map the current value of the object variable to a variable with a normal data type, such as string or integer. You can use this new variable in the conditional split.

    could you temme how to map the object variable to a string variable in the for loop?

    That's explained in the article I linked to.

    Read all the way to the bottom.

    Pal, I could not find assigning object variable to a string variable or something in that article...

    Could you help me...

    You loop over the object variable using the For Each Loop, and in each iteration you assign the current value to the string variable.

    It's in the article, right under picture 14.

    Thank you Friend.....and i have a problem with configuration...Object variable is not supporting while doing configuration....it says object variable can not be exported to the config file...Why is this happening?

    Thanks,
    Charmer

  • February 7, 2012 at 2:47 am

    #1443604

    Because an object variable is a .NET object. It is like a ADO.NET rowset.

    A configuration file is an XML file, it contains strings.

    object variable <> string

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 7, 2012 at 2:49 am

    #1443608

    Koen Verbeeck (2/7/2012)

    Because an object variable is a .NET object. It is like a ADO.NET rowset.

    A configuration file is an XML file, it contains strings.

    object variable <> string

    so how can i get the config file then? any other way?

    Thanks,
    Charmer

  • February 7, 2012 at 2:57 am

    #1443614

    Charmer (2/7/2012)

    Koen Verbeeck (2/7/2012)

    Because an object variable is a .NET object. It is like a ADO.NET rowset.

    A configuration file is an XML file, it contains strings.

    object variable <> string

    so how can i get the config file then? any other way?

    You can't. It is an object variable. As in object. You cannot store in object in an XML file.

    You populate your object variable with an Execute SQL Task, so I don't really see the need for a configuration.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply