Importing/Exporting Data Errors

Question

I have few tables that have previous 2 years of records in it. I want to load that data to my existing database that has all of those table existed, in other words i just want to override this 2 years of historical data in my existing tables in data base. I have SQL 2008R2 and done following steps to import but not successful. Please help me to find a better solution so i can import all data to my existing tables. Thanks in advance.

1. Import/Export Data wizard but i got following errors

- Validating (Error)
    Messages
    * Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "HMY".
     (SQL Server Import and Export Wizard)
    
    * Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
     (SQL Server Import and Export Wizard)
    
    * Error 0xc004706b: Data Flow Task 1: "component "Destination - ACCT" (226)" failed validation and returned validation status "VS_ISBROKEN".
     (SQL Server Import and Export Wizard)
    
    * Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
     (SQL Server Import and Export Wizard)
    
    * Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
     (SQL Server Import and Export Wizard)

2. If i ignore the identity columns then i get following error.

- Copying to [dbo].[ACCT] (Error)
    Messages
    * Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert duplicate key row in object 'dbo.ACCT' with unique index 'I_ACCT_1'. The duplicate key value is (0, 10000000        ).".
     (SQL Server Import and Export Wizard)
    
    * Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (239)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (239)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
    
    * Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - ACCT" (226) failed with error code 0xC0209029 while processing input "Destination Input" (239). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
 

---

HAYAT

Answer 1

You cannot do what you want to do with the import export wizard unless you first delete the data that you are intending to overwrite.

You could also create a custom SSIS package or custom SQL to use a MERGE statement to UPDATE/Insert the new data from your source. 

---

Chuck

Answer 2

Try the "Edit Mappings" button when selecting the tables and destination in the Import Wizard, then select "Delete rows in destination table"; and select "Enable Identity Inserts". 

Answer 3

Try the "Edit Mappings" button when selecting the tables and destination in the Import Wizard, then select "Delete rows in destination table"; and select "Enable Identity Inserts". 

Using that delete option will delete all of the rows in the destination - it soundsed like he is trying to replace only 2 years worth of data in the destination.

---

Chuck

Answer 4

 Thanks oldjeep, Wasn't clear for me, if more than 2 yrs data exists; Perhaps a script is better;

DELETE FROM TABLE_A WHERE DATECOLUMN >= 'WHATEVERDATE'
SET IDENTITY_INSERT TABLE_A ON
INSERT INTO TABLE_B 
SELECT ?? FROM TABLE_A
SET IDENTITY_INSERT TABLE_A OFF

@Hayat, where are you getting your source data from? What are you trying to do with the identity columns? Merge/Update/Delete? As oldjeep mentioned a SSIS package maybe suitable as you can include all the wizard steps and add custom scripts in the package. 

Answer 5

I really don't need identity columns, I just want to copy all these tables to my existing data base and i know all of these records exists in my destination database but i just want to overwrite 2 yrs of data as Chuck mentioned above. I think if I take of PK/FK relationship from tables then it will be no problem to export/import data because my source tables do not have any pk/fk relationship and i can import data in those tables. Suggestion Please? Please let me know how to take off these PK/FK relationship from table and what would be the side effects. Thanks

---

HAYAT

Answer 6

You will have issues when adding back the constraints if the data is not correct, but to answer your question; 

in SSMS, you can click the + next to the table, then

1)select constraints and then drop them

2)select columns and right click the primarykey and click modify, then right click the column and "remove primary"

---

-- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

Answer 7

I really don't need identity columns, I just want to copy all these tables to my existing data base and i know all of these records exists in my destination database but i just want to overwrite 2 yrs of data as Chuck mentioned above. I think if I take of PK/FK relationship from tables then it will be no problem to export/import data because my source tables do not have any pk/fk relationship and i can import data in those tables. Suggestion Please? Please let me know how to take off these PK/FK relationship from table and what would be the side effects. Thanks

---

HAYAT

Not a good idea to remove the PK/FK to import the data.  If you just remove the contraints and import you will have duplicates.

What I would do:

Save your import/expot wizard package as an SSIS package

Edit the ssis package to include a set of execute SQL tasks that delete the 2 years worth of old data before reinserting it

---

Chuck

Answer 8

Good Idea Chuck, Let me try this and will let you know. Thanks

---

HAYAT