I'm trying to take a flat file and turn it into two tables in SSIS and Load to a OLEDB Source (SSMS). I have the tables created in SSMS already that match up with the flat file that the rows are separated by ,'s. I had to do a data conversion Transform because the flat file data type was different than the nvarchar data type I had setup in the Table in SSMS. I'm running into some roadblock with my current package and can't get the data to Load into OLEDB without throwing some Errors before it loads to the Destination.
The Two Tables I have setup in SSMS. The one Has a Primary Key (Unique_ID) and that Primary Key References the Foreign Key the same (Unique_ID) in the other table
Right Now On my Control Flow I Have
Execute SQL Task To Truncate the Tables Prior to The New Load -> Two Data Flow Task for Each New Table
Data Flow Task I have setup for the First Table is -> 1.Txt File Flat Source 2.Data Conversion to convert String[DT_STR] to Unicode String [DT_WSTR] 3.Sort -> That I thought would remove the duplicate rows from the Data Conversion but the rows are still duplicating which is throwing an Error before it gets to the Destination(I have the CheckBox Checked for removing the duplicate Sort Values) 4.OLE DB Destination
The Error is: "An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object 'dbo.'. The duplicate key value is (123).".
In The Data Viewer its duplicating records that I don't need which is causing the Primary Key Constraint For Example:
Data Conversion.sequence
123 Last Name First Name
123 Last Name First Name
The Second Data Flow Task is similar to the first one just the table in the Destination and the Columns I use is different.
I've looked at a lot of post and researched this issue. I am new to using SSIS and looking for the best possible way from a performance standpoint to Load these Two New Tables into a OLEDB Destination. Any recommendations would be greatly appreciated.
Couple Questions I need Answered:
1.Can I have Primary Key and a Foreign Key Setup between my two tables before I execute a package? I was able to Load the flat file into my two tables without a Primary Key and Foreign key referencing the Primary Key in my second table. I need to be able to Join these tables in the future so I need to keep the referential integrity between the two tables. 2. Am I going about this the right way in SSIS with the Transformations I am performing currently? I can't use a Lookup Transform because the Data Types are different between the Source(Flat File) and Destination(OLEDB Source)
If you need me to provide anymore details about the package or flatfile I used and how my Table is Setup in SSMS I can provide more details.