none
How to add columns to an existing table

    Question

  • I have over 300 tables from manufacturers that need to be added to a table where the columns aready exist.  I am having problems with a column in the table I am adding to, it has a not null.  I have to add this column to my manufacturer tables and that makes it null.  I can not change it there.

     

    I am getting the following in trying to import my table because the PRODUCT-PKEY in the table I am copying to has does not except nulls.  In the table I am moving it will acept nulls and I have set the value to 1.

    Operation stopped...

    - Initializing Data Flow Task (Success)

    - Initializing Connections (Success)

    - Setting SQL Command (Success)

    - Setting Source Connection (Success)

    - Setting Destination Connection (Success)

    - Validating (Success)

    - Prepare for Execute (Success)

    - Pre-execute (Success)

    - Executing (Success)

    - Copying to [dbo].[Master Table] (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: "Unspecified error".
      (SQL Server Import and Export Wizard)
     
     * Error 0xc020901c: Data Flow Task 1: There was an error with input column "PRODUCT_PKEY" (123) on input "Destination Input" (47). The column status returned was: "The value violated the integrity constraints for the column.".
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (47)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (47)" 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 - Master Table" (34) failed with error code 0xC0209029 while processing input "Destination Input" (47). 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)
     

    - Post-execute (Success)
     Messages
     * Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - Master Table" (34)" has started.
      (SQL Server Import and Export Wizard)
     
     * Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "component "Destination - Master Table" (34)" has ended.
      (SQL Server Import and Export Wizard)
     
     * Information 0x4004300b: Data Flow Task 1: "component "Destination - Master Table" (34)" wrote 0 rows.
      (SQL Server Import and Export Wizard)
     

     

    Can someone please hlep.

    LadyDee

    Thursday, September 11, 2008 6:32 PM

All replies

  • Does anyone have a clue of what is happening here?  Is there anything I can try that someone feels may help?

     

    Thank you

    Ladydee

     

    Friday, September 12, 2008 6:42 PM
  • I'm a touch confused by your post relative to the information SSIS generated.  At any rate, my reading of the error message is that whatever data you are putting into [dbo].[Master Table] PRODUCT-PKEY has some integrity constraint on it that you are violating.  Without seeing the DDL, I really couldn't say what the issue is.  My guess based on your information about the non-null/null column is that this field isn't getting the value of 1 you have specified for it.

     

    *shrug*

    Friday, September 12, 2008 7:58 PM
  • This is the table I am trying to import to:

    USE [test]

    GO

    /****** Object: Table [dbo].[PRODUCT] Script Date: 09/09/2008 15:25:59 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Master Table](

    [PRODUCT_PKEY] [bigint] IDENTITY(1,1) NOT NULL,

    [PRODUCTS_FKEY] [bigint] NOT NULL,

    [added_at] [datetime] NOT NULL,

    [updated_at] [datetime] NOT NULL,

    [code] [nvarchar](300) NOT NULL,

    [name] [nvarchar](255) NULL,

    [active] [bit] NULL,

    [DATECREATED] [nvarchar](255) NULL,

    [DATEMODIFIED] [nvarchar](255) NULL,

    [keywords] [varchar](4000) NULL,

    [chargeShipping] [bit] NULL,

    [taxable] [bit] NULL,

    [Manufacturer] [varchar](300) NULL,

    [Manufacturer_ID] [bigint] NULL,

    [deleted] [bit] NULL,

    [discountable] [bit] NULL,

    [onSale] [bit] NULL,

    [wasIs] [bit] NULL,

    [suppressSwatches] [bit] NULL,

    [salesRank] [bit] NULL,

    [CATEGORYCODE] [varchar](50) NULL,

    [ChangeDateTime] [datetime] NULL,

    [DateDeactivated] [datetime] NULL,

    [MPN] [varchar](255) NULL,

    [Yahoo_Name] [varchar](500) NULL,

    [price] [numeric](18, 2) NULL,

    [cost] [numeric](18, 2) NULL,

    [Yahoo_Code] [varchar](255) NULL,

    [ship_weight] [numeric](18, 2) NULL,

    [carton_dimensions] [varchar](255) NULL,

    [shipping_cost] [numeric](18, 2) NULL,

    [shipping_price] [numeric](18, 2) NULL,

    [shipping_rate] [numeric](18, 2) NULL,

    [availability] [varchar](255) NULL,

    [depth] [numeric](18, 2) NULL,

    [height] [numeric](18, 2) NULL,

    [length] [numeric](18, 2) NULL,

    [weight] [numeric](18, 2) NULL,

    [overall_dimensions] [varchar](255) NULL,

    [out_of_stock] [bit] NULL,

    [UPC] [varchar](255) NULL,

    [Materials] [varchar](255) NULL,

    [gender_id] [int] NULL,

    [age_range_id] [int] NULL,

    [artist_designer] [varchar](255) NULL,

    [green_certified] [bit] NULL,

    [made_in_id] [varchar](50) NULL,

    [quick_ship] [bit] NULL,

    [department_id] [int] NULL,

    [product_type_id] [int] NULL,

    [ISBN] [varchar](50) NULL,

    [ASIN_Amazon_Number] [varchar](255) NULL,

    [Collection] [varchar](255) NULL,

    [Changed_By] [varchar](255) NULL,)

     

     

    This is the tabe lI am trying to import:

     

    USE [4DConcepts]

    GO

    /****** Object: Table [dbo].[Master Table] Script Date: 09/12/2008 17:59:27 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Master Table](

    [PRODUCT_PKEY] [bigint] IDENTITY(1,1) NOT NULL,

    [Manufacturer] [nvarchar](300) NULL,

    [name] [nvarchar](255) NULL,

    [MPN] [nvarchar](255) NULL,

    [UPC] [nvarchar](255) NULL,

    [cost] [numeric](18, 2) NULL,

    [price] [numeric](18, 2) NULL,

    [materials] [nvarchar](255) NULL,

    [depth] [numeric](18, 2) NULL,

    [height] [numeric](18, 2) NULL,

    [weight] [numeric](18, 2) NULL,

    [ship_weight] [numeric](18, 2) NULL

    ) ON [PRIMARY]

    GO

     

     

    Soem of my tables may be different, but they all have the PRODUCT_PKEY.  The firont end of our web site is written in VB.

     

    Hope this information helps.

    Ladydee

    Friday, September 12, 2008 10:03 PM
  • Both tables use IDENTITY for the key. How can you tell that the same key does not already exist in Master table when you try to insert the table from 4D Concept? The violation likely happen here.

    How badly do you need to keep the 4D Concept key? Since you use Identity, you could simply ommit this column from the import and let the Product Master Identity take care of assigning the key to the inserted rows.

    Saturday, September 13, 2008 6:21 AM
  • I tried leaving the PRODUCT_PKEY out of the 4DCocept table that I want to insert and still got errors.  I tried that before I did the post,  I need to keep the master table as it is.  This is the error I am getting from that.

     

     

    Operation stopped...

    - Initializing Data Flow Task (Success)

    - Initializing Connections (Success)

    - Setting SQL Command (Success)

    - Setting Source Connection (Success)

    - Setting Destination Connection (Success)

    - Validating (Success)

    - Prepare for Execute (Success)

    - Pre-execute (Success)

    - Executing (Success)

    - Copying to [dbo].[Master Table] (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 the value NULL into column 'PRODUCTS_FKEY', table 'Manufacturers.dbo.Master Table'; column does not allow nulls. INSERT fails.".
      (SQL Server Import and Export Wizard)
     
     * Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (62)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (62)" 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 - Master Table" (49) failed with error code 0xC0209029 while processing input "Destination Input" (62). 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)
     

    - Post-execute (Success)
     Messages
     * Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - Master Table" (49)" has started.
      (SQL Server Import and Export Wizard)
     
     * Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "component "Destination - Master Table" (49)" has ended.
      (SQL Server Import and Export Wizard)
     
     * Information 0x4004300b: Data Flow Task 1: "component "Destination - Master Table" (49)" wrote 111 rows.
      (SQL Server Import and Export Wizard)

     

    Ladydee

    Saturday, September 13, 2008 4:58 PM
  • As you can see I am still getting:

     

     

    "Cannot insert the value NULL into column 'PRODUCTS_FKEY', table 'Manufacturers.dbo.Master Table'; column does not allow nulls. INSERT fails.".

     

    What wuld be causing all the DTS and SSIS errors?

     

    LadyDee

    Monday, September 15, 2008 11:42 AM
  • Did you disable Keep Identity?

    Monday, September 15, 2008 3:41 PM
  • I do not know how to disable it, please tell me how to do this.  If you are talking about in the DTS there is not a check in this box.

     

    Thank you, It will greatly help.

     

    LadyDee

     

    Monday, September 15, 2008 4:55 PM
  • In SSIS data flow, Oledb destination, you can choose Data Access mode to be Table or view Fast load, then you get the check boxes Keep identity, Check Constraints, Table Lock and Keep nulls.

    You can also set a custom value for Rows per Batch and Max insert commit size.

    Make sure you do not have Keep Identity checked and that the table has Identity enabled fro that column. This should remove the Null contraint error. New rows will be numbered by the Identity property of the destination table column.

     

    On the other hand, if you wanted to keep the original ID, then you would check "Keep Identity" which would temporarilly lift the identity property, however then you still have the not Null constraint. From that aspect, it broils down to data quality.

    Monday, September 15, 2008 5:39 PM
  • I basically am using DTS to transfere the data from one database (My sever) to the master table which is on our web server.

     

    LadyDee

     

    Monday, September 15, 2008 6:40 PM
  • I do not remember much about DTS, I will leave that to someone who recalls how DTS works.

    Monday, September 15, 2008 11:27 PM
  • Can you point me to instructions on how to do this in SSIS?  I will use either that will help.

     

    Thank you

    LadyDee

     

    Tuesday, September 16, 2008 11:44 AM
  • I still have not gotten an answer to solve this problem?  Can someone please help.

     

    Thank you

    LadyDee

     

    Thursday, September 18, 2008 12:31 PM
  •  ladydee wrote:

    I still have not gotten an answer to solve this problem? 

    For SSIS it is too broad, I thought you were using it already.

    For DTS, I have no idea but if there are no keys in your source records and you get the null violation constraint because of this, then a quick fix is to create a script that removes the null constraint and identity from the destination table, import the data, then recreate the null constraint and identity, which would renumber the nulls.

     

    Why would you have source records sometimes with keys and sometimes not. Could not you fix this at the source?

    Thursday, September 18, 2008 12:51 PM