hi! It was working absolutely fine but i removed sorting and made "source" sorting position to true,After that while converting SID from str to int error occuring Error: 0xC02020C5 at ETL, Data Conversion [8253]: Data conversion failed while converting column "SID" (8234) to column "SID3" (8281). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at ETL, Data Conversion [8253]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "SID3" (8281)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "SID3" (8281)" 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.mhassanshahbaz

Hi,this error raise because data stream found Inappropriate value in the input SID column which can not convert it to int. so you can CONFIGURE ERROR OUTPUT on data conversion transform and redirect error rows to a flat file destination, this will populate all rows which cause problem into the flat file, then you can troubleshoot the problem easily.http://www.rad.pasfu.com

Hi,this error raise because data stream found Inappropriate value in the input SID column which can not convert it to int. so you can CONFIGURE ERROR OUTPUT on data conversion transform and redirect error rows to a flat file destination, this will populate all rows which cause problem into the flat file, then you can troubleshoot the problem easily.http://www.rad.pasfu.com

*when i extract data from a source and union all data from different sources and then load into a table,at that time duplicate values are being loaded. AFTER extract and transform when i`m loading data in registration table,from where i`m making dimensions and facts data to be extracted,look up and making measure values. 1)if CREATE TABLE [Registration] ( [RegistrationNo] nvarchar(27) FOREIGN KEY REFERENCES Student_Info(RegistrationNo), [Std_ID] int, [Semester] nvarchar(50), [Course] nvarchar(50), [Marks] int, [Department] nvarchar(10) PRIMARY KEY(RegistrationNo, Semester, Course) ) *voilation of Pk occurs,i want to sort those error creating duplicate values before loading.mhassanshahbaz

*when i extract data from a source and union all data from different sources and then load into a table,at that time duplicate values are being loaded. AFTER extract and transform when i`m loading data in registration table,from where i`m making dimensions and facts data to be extracted,look up and making measure values. 1)if CREATE TABLE [Registration] ( [RegistrationNo] nvarchar(27) FOREIGN KEY REFERENCES Student_Info(RegistrationNo), [Std_ID] int, [Semester] nvarchar(50), [Course] nvarchar(50), [Marks] int, [Department] nvarchar(10) PRIMARY KEY(RegistrationNo, Semester, Course) ) *voilation of Pk occurs,i want to sort those error creating duplicate values before loading.mhassanshahbaz

an ID auto increment can be used but i don`t want so,i want to sort out those duplicated values because if a student with same registrationNo,Course,semester occurs,it not valid statemhassanshahbaz

I hope now you understand what i`m looking formhassanshahbaz

I hope now you understand what i`m looking formhassanshahbaz

before loading values,can i check values with same registrationNo,Course,semester as we use sort technique to load distinct values??? i sorted registrationNo in student table but in registration i can not do this as one registrationNo has registered into many courses in different semesters??? mhassanshahbaz

before loading values,can i check values with same registrationNo,Course,semester as we use sort technique to load distinct values??? i sorted registrationNo in student table but in registration i can not do this as one registrationNo has registered into many courses in different semesters??? mhassanshahbaz

OK, you want to remove duplicate rows from data stream before load it into destination, this article from todd explained a method to do this: http://toddmcdermid.blogspot.com/2009/01/eliminating-duplicate-primary-keys-in.html http://www.rad.pasfu.com

OK, you want to remove duplicate rows from data stream before load it into destination, this article from todd explained a method to do this: http://toddmcdermid.blogspot.com/2009/01/eliminating-duplicate-primary-keys-in.html http://www.rad.pasfu.com

I am not clear about what do you mean, could you explain more about what you trying to do now?http://www.rad.pasfu.com

I am not clear about what do you mean, could you explain more about what you trying to do now?http://www.rad.pasfu.com

SSIS package "Load_Dimension_Fact.dtsx" starting. Information: 0x4004300A at Student_Course_Fact, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Student_Course_Fact, SSIS.Pipeline: Validation phase is beginning. Warning: 0x80049304 at Student_Course_Fact, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. Warning: 0x80047076 at Student_Course_Fact, SSIS.Pipeline: The output column "Dest_RegistrationNo" (210) on output "Union All Output 1" (194) and component "Union All" (192) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Warning: 0x80047076 at Student_Course_Fact, SSIS.Pipeline: The output column "Dest_Semester" (213) on output "Union All Output 1" (194) and component "Union All" (192) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Warning: 0x80047076 at Student_Course_Fact, SSIS.Pipeline: The output column "Dest_Course" (216) on output "Union All Output 1" (194) and component "Union All" (192) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Warning: 0x80047076 at Student_Course_Fact, SSIS.Pipeline: The output column "Dest_Marks" (219) on output "Union All Output 1" (194) and component "Union All" (192) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Information: 0x40043006 at Student_Course_Fact, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Student_Course_Fact, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Student_Course_Fact, SSIS.Pipeline: Execute phase is beginning. Error: 0xC0202009 at Student_Course_Fact, Student_Course_Fact [239]: 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". Error: 0xC020901C at Student_Course_Fact, Student_Course_Fact [239]: There was an error with input column "RegistrationNo" (290) on input "OLE DB Destination Input" (252). The column status returned was: "The value violated the integrity constraints for the column.". Error: 0xC0209029 at Student_Course_Fact, Student_Course_Fact [239]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (252)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (252)" 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. Error: 0xC0047022 at Student_Course_Fact, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Student_Course_Fact" (239) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (252). 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. Error: 0xC02020C4 at Student_Course_Fact, Registration [104]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. Error: 0xC0047038 at Student_Course_Fact, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Registration" (104) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Student_Course_Fact, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x402090DF at Student_Course_Fact, Student_Course_Fact [239]: The final commit for the data insertion in "component "Student_Course_Fact" (239)" has started. Information: 0x402090E0 at Student_Course_Fact, Student_Course_Fact [239]: The final commit for the data insertion in "component "Student_Course_Fact" (239)" has ended. Information: 0x4004300B at Student_Course_Fact, SSIS.Pipeline: "component "Student_Course_Fact" (239)" wrote 0 rows. Information: 0x40043009 at Student_Course_Fact, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Student_Course_Fact Warning: 0x80019002 at Load Facts: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Load_Dimension_Fact: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Load_Dimension_Fact.dtsx" finished: Failure. The program '[5892] Load_Dimension_Fact.dtsx: DTS' has exited with code 0 (0x0).mhassanshahbaz

let us know what did you do till now, could you give us a screen shot or data flow schema?http://www.rad.pasfu.com

reason for that... i removed sort trnasformations,and after taht i`m facing itmhassanshahbaz