August 10, 2009 at 9:10 am
Basically, I just need to know how to append records to a table via SSIS?
August 10, 2009 at 11:09 am
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.
August 10, 2009 at 11:13 am
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
August 10, 2009 at 12:06 pm
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.
August 10, 2009 at 12:13 pm
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?
August 10, 2009 at 12:17 pm
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
August 11, 2009 at 2:28 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2009 at 6:05 am
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.
August 11, 2009 at 6:13 am
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
August 11, 2009 at 6:20 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2009 at 9:55 am
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.
August 11, 2009 at 11:14 am
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.
August 11, 2009 at 11:51 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 11, 2009 at 12:24 pm
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
August 11, 2009 at 12:31 pm
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 18 total)
You must be logged in to reply to this topic. Login to reply