September 16, 2008 at 11:07 am
I am getting the following error:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
Messages
* Warning 0x80047076: Data Flow Task 1: The output column "Product_ID" (23) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "creationdate" (29) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "lastupdated" (32) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "kind of code" (35) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "long" (44) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "short" (47) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "basic" (50) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "image" (53) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
* Warning 0x80047076: Data Flow Task 1: The output column "accessoryproductid" (56) on output "OLE DB Source Output" (11) and component "Source - Abco" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)
- 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 "code" (156) on input "Destination Input" (71). 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" (71)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (71)" 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" (58) failed with error code 0xC0209029 while processing input "Destination Input" (71). 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" (58)" 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" (58)" has ended.
(SQL Server Import and Export Wizard)
* Information 0x4004300b: Data Flow Task 1: "component "Destination - Master Table" (58)" wrote 0 rows.
(SQL Server Import and Export Wizard)
I keep trying to post this and it is not working?
LadyDee
September 16, 2008 at 11:15 am
Table to insert from:
USE [Darlene]
GO
/****** Object: Table [dbo].[Abco] Script Date: 09/16/2008 13:13:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Abco](
[Manufacturer_ID] [int] NOT NULL,
[name] [varchar](60) NOT NULL,
[Product_ID] [int] NOT NULL,
[MPN] [int] NOT NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[kind of code] [varchar](60) NULL,
[varchar](60) NULL,
[keywords] [varchar](900) NOT NULL,
[long] [varchar](1000) NOT NULL,
[short] [varchar](1000) NOT NULL,
[basic] [varchar](1000) NOT NULL,
[image] [varchar](60) NULL,
[accessoryproductid] [int] NULL
) ON [PRIMARY]
Table to insert 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,
[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,)
Sorry, I did not go to the second page. Now I see that it was posting.
LadyDee
September 16, 2008 at 12:19 pm
Is this a DTS package or an SSIS package?
It appears that we've been going down the wrong path as far as the indentity column is concerned. You aren't trying to inset a value into [Master Table].PRODUCT_PKEY from a column in Abco, are you? If not, you don't need to set the "Insert Identity" property on.
The warnings you received are all for column in Abco that are not being inserted into Master Table. You received the error because the column code in Abco contains a value that is not allowed in the code column of Master Table, possibly because the column is a foreign key between Master Table and another table.
Every column in Master Table that does not allow nulls has to receive a value from Abco.
Greg
September 17, 2008 at 6:16 am
When trying to set these not null columns in the table is am using, I use the alter function and it will not let me make these columns null.
Thank you
LadyDee
September 17, 2008 at 7:24 am
The ALTER would fail unless you also specified the code needed to tell it not to check the existing values. However, that wouldn't really solve the problem, as those existing NULL values would still be around to cause the problem during the INSERT process. Until you either SELECT only records from Abco that don't contain NULL values for the "code" field, or delete such records from Abco, or UPDATE those NULL values to valid data for that field, you'll continue to have a problem.
Thus you either need to "scrub" your data in the Abco table, or use a limited SELECT to limit the INSERT to only records that meet the criteria for being inserted.
Steve
(aka smunson)
:):):)
deemurphy_us (9/17/2008)
When trying to set these not null columns in the table is am using, I use the alter function and it will not let me make these columns null.Thank you
LadyDee
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2008 at 7:12 am
No matter what I have done, I can not get this process to work, could you please write an example of what I should be doing based off of the two tables I sent in.
Thank you
LadyDee
September 22, 2008 at 7:24 am
If you're unable to change the table to allow null values for a given column, it's likely that there's an index that requires unique values for that column. You may need to drop that index and then recreate it after changing the column. However, be aware that the index cannot be re-created exactly as it was, as the column that becomes nullable can no longer be guaranteed to be unique.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2008 at 8:26 am
LadyDee,
First things first.... I suspect you are using SSIS and not DTS to do this. As per the other posters, please advise if this is the case as the solution really hinges on choice of technology.
Secondly, if this is SSIS, the reason you are getting all the issues with warnings where performance can be increased is because you are selecting fields in your select statement which you are not using in your insert statement. For example if you have field1, field2, field3 in your source select, but you are only inserting field1 and field3, then field2 is not being used. Its easy to disable this, in the metadata section, just disable the fields not being used, and they will not be used in the dataflow, and the warning will disappear.
Thirdly, for the identity insert, simply have nothing mapped from your source. The RDBMS should handle your autonumbers automatically for you. That is, unless you implicitly want to use the autonumbers, in which case you would first have to disable identity insert on the field, and enable identity insert when you are done.
How about posting the .dtsx file if it is not sensitive info, and we will see if we can assist with all those warnings...
~PD
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply