How do I append records to a table via SSIS?

  • tripower

    SSCrazy

    Points: 2035

    Basically, I just need to know how to append records to a table via SSIS?

  • John Rowan

    SSC Guru

    Points: 56440

    That's a pretty broad question. With no detail on your part to tell us where the data is comming from or what you have to do to it, the only answer I can give you is:

    use a data flow task with an OLE DB Destination to insert the rows in your table.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • J Livingston SQL

    SSC Guru

    Points: 51272

    Hi

    Please dont think I am being rude....but perhaps if you gave us a little more information on ......

    the data source and type

    the data destination and type (I assume its SQL 2005 db?)

    volume of data to append

    whether the source data require any transformations

    do you want to update exg records

    etc.

    ...then I am sure that there are many experienced people here who will be very willing to help you.

    Have you looked at the SSIS tutorials that comes in a standatd install?

    roundel1900 (8/10/2009)


    Basically, I just need to know how to append records to a table via SSIS?

    basically your question is similar to me asking you ..."how do I cook dinner ?"..... I am sure you would have a host of questions for me 😀

    Kind regards gah

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • tripower

    SSCrazy

    Points: 2035

    SQL 2005 / OLE DB I have a few existing rows in a table and i want to be able to append a few rows to the existing table.

  • John Rowan

    SSC Guru

    Points: 56440

    Than you can do exactly what I said. Create a data flow. Add a task for to get your source rows, add an OLE DB Destination to insert the source rows into your table.

    Have you tried anything yet? What challenges did you come accross?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • J Livingston SQL

    SSC Guru

    Points: 51272

    Have you tried the SQL Import and Export Wizard?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Phil Parkin

    SSC Guru

    Points: 244656

    Appending rows is the default behaviour. Any other requirement (UPDATE or replace, for example) would require additional steps.

    Unless you are prepared to take the time to provide a few more details along the lines suggested by gah, you will continue to get these general responses.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • tripower

    SSCrazy

    Points: 2035

    Maybe I am not explaining this correctly. Yes it appends the rows but without regards to the keys. So as it stands now I can continually append the exact same rows (with the same keys) over and over. And when I re-add the keys the package errors out due to constraint violations on the data migration.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    In Control Flow create an "Execute SQL Task" ...In the task editor set the SQL Statement to TRUNCATE TABLE "yourtable"

    run this before your dataflow task

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Phil Parkin

    SSC Guru

    Points: 244656

    roundel1900 (8/11/2009)


    Maybe I am not explaining this correctly. Yes it appends the rows but without regards to the keys. So as it stands now I can continually append the exact same rows (with the same keys) over and over. And when I re-add the keys the package errors out due to constraint violations on the data migration.

    There is no question here, it's just a statement. What do you want to happen?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • John Rowan

    SSC Guru

    Points: 56440

    roundel1900 (8/11/2009)


    Maybe I am not explaining this correctly. Yes it appends the rows but without regards to the keys. So as it stands now I can continually append the exact same rows (with the same keys) over and over. And when I re-add the keys the package errors out due to constraint violations on the data migration.

    Please tell me that you are not removing the table constraints and keys prior to your SSIS run and then recreating them after you load the data.

    Like Phil said, explain what you are trying to accomplish, how you have attempted to do this, and the challenges that you've had that you need help with.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • tripower

    SSCrazy

    Points: 2035

    gah (8/11/2009)


    In Control Flow create an "Execute SQL Task" ...In the task editor set the SQL Statement to TRUNCATE TABLE "yourtable"

    run this before your dataflow task

    I don't want to truncate the table. I want to retain the data that is there and add onto it.

  • Phil Parkin

    SSC Guru

    Points: 244656

    roundel1900 (8/11/2009)


    gah (8/11/2009)


    In Control Flow create an "Execute SQL Task" ...In the task editor set the SQL Statement to TRUNCATE TABLE "yourtable"

    run this before your dataflow task

    I don't want to truncate the table. I want to retain the data that is there and add onto it.

    Anyone else puzzled?

    How do you want to avoid the key violations? There's only so much you can do:

    1) Update records with matching keys

    2) Ignore matching keys (ie if the record already exists, do nothing and move to the next one)

    3) Delete and recreate records where there are matching keys

    4) If a key match is found, create a new unique key and insert that with the record

    You could have got this question answered a whole lot quicker if you'd provided some sample data, showing what you start with, what you end up with and what you want to end up with. Here[/url] is an article that gives a good indication of how to post your forum questions to get a good response.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    roundel1900 (8/11/2009)


    gah (8/11/2009)


    In Control Flow create an "Execute SQL Task" ...In the task editor set the SQL Statement to TRUNCATE TABLE "yourtable"

    run this before your dataflow task

    I don't want to truncate the table. I want to retain the data that is there and add onto it.

    Ok..we may be at last getting there (slowly) with some understanding of what you are attempting.

    it seems from what you are saying that when you add your data it fails because of the constraints on the destination table...I assume you must have a unique or PK index on this table??

    If that is correct (and you may tell us later that it isnt !!)...and if you are sure that you all you want to want to do is add new data then...a few ideas that just may work for you are:

    either alter the source data to only provide new records

    or

    load the source into a staging table in SQL and then perform some TSQL to filter only the new data and then insert those records to your main table.

    If you are going to ask us later on "how do I update existing records?."

    ...then I must really point you back to some of the earlier replies that you have already received that have requested that you provide more details and facts to help us help you.

    Your first post in this thread said:

    "Basically, I just need to know how to append records to a table via SSIS? "

    I hope that you now appreciate that your question was somewhat short on detail 🙂

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL

    SSC Guru

    Points: 51272

    You have another active thread here:

    http://www.sqlservercentral.com/Forums/Topic768074-148-1.aspx

    is this referring to the same process you are trying to develop in this thread?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

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

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