July 19, 2010 at 1:14 pm
I have a simple SSIS package to archive old data from Tabls_A on Server_A to Table_B on Server_B. Tables A & B have the same layout, (I scripted Table_A to create Table_B). When I ran the package, I get the error: "The value could not be converted because of a potential loss of data" on the field "Agent".
The "output column "Agent" (29)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "Agent" (29)" specifies failure on error.
CREATE TABLE [dbo].[My_Table](
[UNIQUE_ID] [decimal](10, 0) NOT NULL,
[List_ID] [int] NULL,
[MARKET] [varchar](255) NOT NULL,
[SYS_SBB] [char](10) NOT NULL,
[PRIN_SBB] [char](10) NOT NULL,
[SUB_ACCT_NO_SBB] [char](16) NOT NULL,
[SAL_CDE_SBB] [char](1) NOT NULL,
[RES_NAME_SBB] [char](26) NOT NULL,
[ADDR1_HSE] [char](26) NOT NULL,
[RES_ADDR_2_HSE] [char](26) NULL,
[RES_CITY_HSE] [char](18) NULL,
[RES_STATE_HSE] [char](2) NOT NULL,
[POSTAL_CDE_HSE] [char](20) NOT NULL,
[HOME_PHONE_SBB] [char](10) NULL,
[SERV_CDE_SBB] [char](255) NULL,
- ->>[b]Agent[/b] [char](8) NULL,
[Sales_Rep] [char](8) NULL,
[Ops_ID] [char](8) NULL,
[Tech_ID] [char](8) NULL,
[Node] [char](15) NULL
) ON [PRIMARY]
I confirmed that all the fields between A & B are the same length & data type, and in the SSIS packages all the fields seem to be mapped correctly. It ran for a few minutes, transferring 3,000,000+ records before failing.
Thoughts ?
July 19, 2010 at 3:57 pm
I'm a SSIS newbie, so it took some poking around, but I was able to find the "Redirect Row" function within the error output feature. This gave me 12 bad rows that I can now look at to see what caused them to fail. It still seems odd that I would get an error in the first place, since source & destination are the same. At least now I have something to look at.
July 20, 2010 at 1:23 am
I agree - what are your findings? A single quote in one of the fields perhaps?
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
July 20, 2010 at 1:25 pm
There were 12 rows that were flagged as having bad data :
Agent
--------
BA BR 1
BA BR 1
33 1U 1
1V 0B :
D2 HB 1
BU GB
1U 1V 0
BA BD G
N6 0B 0
E1 N6 0
1U 32 N
WM WS #
data should be something like:
2100
1647
50
30
1170
1120
40
Data was originally loaded from flat files. Also, I notice the name field has double-quotes for those records.
I don't know if there were errors at the time, or why it wouldn't copy the bad data to the new table. Not terribly important in this case.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply