Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 SSIS flat error file generation for loading data from flat csv file Expand / Collapse
Author
Message
Posted Friday, March 18, 2011 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 6:13 AM
Points: 2, Visits: 37
We are doing a CSV file loading using a Flat file connection as source and OLEDB connection as Destination. The CSV file is pipe delimited file. To capture errors if any, we

are using again a flat file connection as Destination generating similar pipe deleimeted CSV file. The packge is well defined with config files and necessary variables

dynamically picking up the source and destination file names and all. It is working fine in all the environment Development, Test and Production if there is no data related

error. But if there is any error and captured in the generated error file it is comming as expected in Development environment (Please copy paste the following lines in notepad and see),

LoadID|UniqueConsumerId|RegistrationRefNo|ERPConsumerNo|DistributorId|ConsumerNo|ConsumerName|ConsumerGroup|StatusCode|ActiveFrom|ActiveTo|AreaRefNo|AddressLine1|AddressLine2|TownorCity|StateCode|Pin|NatureCode|TypeCode|SubTypeCode|BusinessTypeCode|PackageCode|RegulatorCode|CylinderHoldingStatusCode|CylinderHoldingQuantity|RegulatorHoldingQuantity|PendingSVCashMemoFlag|BlockFlag|BlockedBy|BlockingDate|LastestSVNo|IsStatosMember|StatosCategoryRefNo|LastUpdateBy|LastUpdateDateTime|RenewRefNo|CorpEnrolRefNo|LastTransferEvent|create_timestamp|update_originator_id|create_originator_id|OriginalSVNo|OriginalSVDate|PreviousDistributorId|StopSupplyFlag|LatestSVDate|LatestRefillStatus|LatestRefillStatusDate|LatestOrderRefNo|AddressLine3|DistrictCode|LastRefillBookingDate|LastRefillDeliveryDate|LastInspectionDate|SchemeRefNo|LoginUserId|RefillCount|ErrorCode|ErrorColumn|ErrorColumnName|ErrorDescription
89||1100843300000001||8433|648565|SHANAWAZ JALAL SHAIKH|D|Active|2010-12-13||9100843300000001|R NO-1.GRD FLR.|INDIRA NGR. NR. DURGA DEVI TEMPLE|132000|MH|400076|1|1|||036|047|S|1|1||N|||1100843300042950|||madhu|2010-12-13 11:02:54.967||||952758|0|0|1033168|2010-12-13|||2010-12-13|DLVD|2011-01-15 00:00:00.000|1110843300012128|POWAI|246|2011-01-15 10:11:44.827|2011-01-15 00:00:00.000|2010-12-13 11:02:55.000||||-1071607683|1537|Copy of UniqueConsumerId|The data value violates integrity constraints.


but not in Test and Production environment. I am getting all the error records unreadable in the error file as shown below. Can anyone put any idea what may be the cause.

_x003C_none_x003E_LoadID_x003C_none_x003E_|_x003C_none_x003E_UniqueConsumerId_x003C_none_x003E_|_x003C_none_x003E_RegistrationRefNo_x003C_none_

x003E_|_x003C_none_x003E_ERPConsumerNo_x003C_none_x003E_|_x003C_none_x003E_DistributorId_x003C_none_x003E_|_x003C_none_x003E_ConsumerNo_x003C

_none_x003E_|_x003C_none_x003E_ConsumerName_x003C_none_x003E_|_x003C_none_x003E_ConsumerGroup_x003C_none_x003E_|_x003C_none_x003E_StatusCod

e_x003C_none_x003E_|_x003C_none_x003E_ActiveFrom_x003C_none_x003E_|_x003C_none_x003E_ActiveTo_x003C_none_x003E_|_x003C_none_x003E_AreaRefNo_x0

03C_none_x003E_|_x003C_none_x003E_AddressLine1_x003C_none_x003E_|_x003C_none_x003E_AddressLine2_x003C_none_x003E_|_x003C_none_x003E_TownorCity

_x003C_none_x003E_|_x003C_none_x003E_StateCode_x003C_none_x003E_|_x003C_none_x003E_Pin_x003C_none_x003E_|_x003C_none_x003E_NatureCode_x003C_n

one_x003E_|_x003C_none_x003E_TypeCode_x003C_none_x003E_|_x003C_none_x003E_SubTypeCode_x003C_none_x003E_|_x003C_none_x003E_BusinessTypeCode_x

003C_none_x003E_|_x003C_none_x003E_PackageCode_x003C_none_x003E_|_x003C_none_x003E_RegulatorCode_x003C_none_x003E_|_x003C_none_x003E_Cylinder

HoldingStatusCode_x003C_none_x003E_|_x003C_none_x003E_CylinderHoldingQuantity_x003C_none_x003E_|_x003C_none_x003E_RegulatorHoldingQuantity_x003C_non

e_x003E_|_x003C_none_x003E_PendingSVCashMemoFlag_x003C_none_x003E_|_x003C_none_x003E_BlockFlag_x003C_none_x003E_|_x003C_none_x003E_BlockedBy

_x003C_none_x003E_|_x003C_none_x003E_BlockingDate_x003C_none_x003E_|_x003C_none_x003E_LastestSVNo_x003C_none_x003E_|_x003C_none_x003E_IsStatos

Member_x003C_none_x003E_|_x003C_none_x003E_StatosCategoryRefNo_x003C_none_x003E_|_x003C_none_x003E_LastUpdateBy_x003C_none_x003E_|_x003C_none

_x003E_LastUpdateDateTime_x003C_none_x003E_|_x003C_none_x003E_RenewRefNo_x003C_none_x003E_|_x003C_none_x003E_CorpEnrolRefNo_x003C_none_x003E_

|_x003C_none_x003E_LastTransferEvent_x003C_none_x003E_|_x003C_none_x003E_create_timestamp_x003C_none_x003E_|_x003C_none_x003E_update_originator_id_x

003C_none_x003E_|_x003C_none_x003E_create_originator_id_x003C_none_x003E_|_x003C_none_x003E_OriginalSVNo_x003C_none_x003E_|_x003C_none_x003E_Origi

nalSVDate_x003C_none_x003E_|_x003C_none_x003E_PreviousDistributorId_x003C_none_x003E_|_x003C_none_x003E_StopSupplyFlag_x003C_none_x003E_|_x003C_no

ne_x003E_LatestSVDate_x003C_none_x003E_|_x003C_none_x003E_LatestRefillStatus_x003C_none_x003E_|_x003C_none_x003E_LatestRefillStatusDate_x003C_none_x

003E_|_x003C_none_x003E_LatestOrderRefNo_x003C_none_x003E_|_x003C_none_x003E_AddressLine3_x003C_none_x003E_|_x003C_none_x003E_DistrictCode_x003C

_none_x003E_|_x003C_none_x003E_LastRefillBookingDate_x003C_none_x003E_|_x003C_none_x003E_LastRefillDeliveryDate_x003C_none_x003E_|_x003C_none_x003E

_LastInspectionDate_x003C_none_x003E_|_x003C_none_x003E_SchemeRefNo_x003C_none_x003E_|_x003C_none_x003E_LoginUserId_x003C_none_x003E_|_x003C_no

ne_x003E_RefillCount_x003C_none_x003E_|_x003C_none_x003E_ErrorCode_x003C_none_x003E_|_x003C_none_x003E_ErrorColumn_x003C_none_x003E_|_x003C_none

_x003E_ErrorColumnName_x003C_none_x003E_|_x003C_none_x003E_ErrorDescription_x003C_none_x003E_
_x003C_none_x003E_159_x003C_none_x003E_|_x003C_none_x003E_1110843600086368_x003C_none_x003E_|_x003C_none_x003E_1110843600000001_x003C_none_x

003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E_8436_x003C_none_x003E_|_x003C_none_x003E_662645_x003C_none_x003E_|_x003C_none_x

003E_MR YOGESH A

SHINDE_x003C_none_x003E_|_x003C_none_x003E_D_x003C_none_x003E_|_x003C_none_x003E_Active_x003C_none_x003E_|_x003C_none_x003E_2011-01-28_x003C_

none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E_9110843600000032_x003C_none_x003E_|_x003C_none_x003E_SHIVAM

NIWAS,NR_x003C_none_x003E_|_x003C_none_x003E_NEELKANTH BUS

STOP,KONKANI_x003C_none_x003E_|_x003C_none_x003E_726500_x003C_none_x003E_|_x003C_none_x003E_MH_x003C_none_x003E_|_x003C_none_x003E_400606

_x003C_none_x003E_|_x003C_none_x003E_1_x003C_none_x003E_|_x003C_none_x003E_1_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_n

one_x003E__x003C_none_x003E_|_x003C_none_x003E_036_x003C_none_x003E_|_x003C_none_x003E_047_x003C_none_x003E_|_x003C_none_x003E_S_x003C_none

_x003E_|_x003C_none_x003E_1_x003C_none_x003E_|_x003C_none_x003E_1_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E_N

_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E_1110843600215346_x003C_none_x

003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E_laxman_x003C_none_x003E_|_x003C_none_x003E

_2011-01-28

16:21:33.620_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_

|_x003C_none_x003E_4547029_x003C_none_x003E_|_x003C_none_x003E_1_x003C_none_x003E_|_x003C_none_x003E_0_x003C_none_x003E_|_x003C_none_x003E_1

033311_x003C_none_x003E_|_x003C_none_x003E_2011-01-28_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x0

03E_|_x003C_none_x003E_2011-01-28_x003C_none_x003E_|_x003C_none_x003E_DLVD_x003C_none_x003E_|_x003C_none_x003E_2011-02-23

00:00:00.000_x003C_none_x003E_|_x003C_none_x003E_1110843600050981_x003C_none_x003E_|_x003C_none_x003E_PADA,2ND POKHRAN

RD,THANE_x003C_none_x003E_|_x003C_none_x003E_263_x003C_none_x003E_|_x003C_none_x003E_2011-02-19

18:07:06.727_x003C_none_x003E_|_x003C_none_x003E_2011-02-23 00:00:00.000_x003C_none_x003E_|_x003C_none_x003E_2011-01-28

16:21:33.007_x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_|_x003C_none_x003E__x003C_none_x003E_

|_x003C_none_x003E_-1071607685_x003C_none_x003E_|_x003C_none_x003E_0_x003C_none_x003E_|_x003C_none_x003E_Row

Error_x003C_none_x003E_|_x003C_none_x003E_No status is available.


Post #1080256
Posted Monday, October 10, 2011 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:17 AM
Points: 1, Visits: 51
Check the flat file connection properties.

Set the TextQualifier to empty and try again.
Post #1187974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse