SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 SSIS flat error file generation for loading data from flat csv file


SQL Server 2008 SSIS flat error file generation for loading data from flat csv file

Author
Message
anjibrajkhowa
anjibrajkhowa
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
mkhan2001
mkhan2001
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 51
Check the flat file connection properties.

Set the TextQualifier to empty and try again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search