locked
OleDB Destination Values in one row not in many RRS feed

  • Question

  • Hi Everybody,

    i am not sure about the Topic. Perhaps you have another idea.

    I have 5 OleDB Sources with different Selection Criterias

    I have a table it looks so:

    ID, Value, Value 2, Value 3, Value 4, Value 5

    In the end i have an Union All Transformation and an oleDB Destination.

    When i make a run, the insert writes the data into the table.
    But not in the format which i want to have.

    The result is the folowing:

    ID, Value, Value 2, Value 3, Value 4, Value 5

    1     2

    2                  5

    3                                7

    But i want to have the result in one row:

    ID, Value, Value 2, Value 3, Value 4, Value 5

    1       2           5            7

    Could you help me in this case.
    I am new in SSIS and i don't understand why SSIS Inserts the value as it does.

    Best regards,
    Sebastian

    Wednesday, March 25, 2015 5:03 PM

All replies

  • Hi Sebastian,

    you need to use the Join instead looks like not the union, and a cascading approach so you join a pair a tables at a time which all ends with a destination like


    Arthur

    MyBlog


    Twitter

    Wednesday, March 25, 2015 6:25 PM
  • Hi Arthur,

    thanks for your help.

    I have now the joins. In my case Full outer Join becuase i need all values from right and left table.

    When the insert starts, i have the same result as before.

    I get 5 rows back and not 1 row.

    Best regards,
    Sebastian

    Edit: I have no Join Keys because the 5 Source Values are single values.

    Edit2: The Source values come from one Temp Table. So the values in one column and in different rows.

    • Edited by Basti087 Thursday, March 26, 2015 1:03 PM
    Thursday, March 26, 2015 10:23 AM
  • I get 5 rows back and not 1 row.


    It depends upon the data. You may have to use for example DISTINCT to get back unique records. But again it depends on the requirement. If you can post the sample DDL and sample data, it will help to under requirement.

    Thanks, hsbal

    Thursday, March 26, 2015 5:05 PM
  • Hi Harry,

    why do you mena it depends on the data?

    I have a Temp Table in which my excel file has been loaded.
    There is a column. The data i need is in 5 rows.

    But i need this data in the 5 rows in 1 row and in columns.

    Temp Table:

    Column
    Data1
    Data2
    Data3

    Taget Table:
    Data 1, Data 2, Data3

    I select the values from the Table in 5 singe OleDB Sources like
    SELECT column FROM TempTable WHERE SortColumn LIKE 'Keyword'

    Then i hvae tried to pivot the data, have tried a union, a Join Merge

    but all the time when i write this data back into the Target table i get a result like this

    Column1, Column2, Column3

    Data1

                      Data2

                                          Data3

    But this is not the result which i want to have.
    I want that all in one row, inserted to the columns.

    I can't understand why this happens.
    Perhaps i am new in SSIS.

    In code i would know what i have to do...
    But we want to use the Toolset without Script on the most position.

    Best regards,
    Sebastian

    Thursday, March 26, 2015 11:40 PM
  • ok. So you want to pivot the data i.e. transform rows into coluns. Please check T-SQL PIVOT clause for this purpose.

    Thanks, hsbal

    Friday, March 27, 2015 2:40 PM