November 4, 2014 at 11:48 pm
Dear forum members,
I have several hundreds of excel files that I must transfer data to SQL Server 2008. So i created a Integration Services Project in BIDS 2008. Am using a For each loop container and needless to mention it works successfully when I simply use a single excel file and write it dynamically to SQL server but when I add multiple excel file in the source folder, on executing the package, I get the following error
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK__BasicDat__E43AF61B58D1301D'. Cannot insert duplicate key in object 'dbo.BasicData'. The duplicate key value is (01010100102).".
All the excel files have the same structure. I have created a SQL Task that creates the table at run time.
I use a For each loop container and have used two package level user defined variables fileName that stores the first excel file name and file path that stores the absolute path to the directory that holds the excel files. I have already disabled Validate 64 bit runtime to False
My understanding is that each time the for loop run it will pick up a new file and append its contents to the SQL table. My question therefore is what am i doing wrong?
Thank you for your time in helping me understand what am I missing.
Cheers,
Ashish
November 5, 2014 at 12:16 am
Quick question, an you post the ddl (create script) for the destination table?
😎
November 5, 2014 at 12:21 am
Sure, by the way thank you for the reply.
The script is DROP TABLE BasicData CREATE TABLE BasicData(District_name nvarchar(255), School_code nvarchar(255) NOT NULL PRIMARY KEY, School_name nvarchar(255),
Block_name nvarchar(255), Cluster_name nvarchar(255), Village_name nvarchar(255), Year nvarchar(255))
November 5, 2014 at 1:10 am
Quick suggestion, change the primary key, something like this:
😎
IF OBJECT_ID('') IS NOT NULL DROP TABLE BasicData;
CREATE TABLE BasicData
(
BasicData INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,District_name nvarchar(255)
,School_code nvarchar(255) NOT NULL
,School_name nvarchar(255)
,Block_name nvarchar(255)
,Cluster_name nvarchar(255)
,Village_name nvarchar(255)
,[Year] nvarchar(255)
);
November 5, 2014 at 1:54 am
Thank you, thank you and thank you so much.
I had been struggling with this problem for over a week now when I acccidentaly stumbled on this website. Out of sheer frustration I had posted earlier today in several groups. Your solution worked for me. Thanks a lot.
November 5, 2014 at 11:01 pm
I was overjoyed yesterday because the solution you gave worked for me. I'm sorry but I'm re-opening this thread again because I have the following questions.
Q1. Why have you created a new column BasicData as the Primary Key? I actually want School Code as the primary key
2. What is the meaning and significance of the keyword "Clustered" in the T-SQL query that you had posted as a solution to me yesterday.
Thank you for your time
Cheers
Ashish
November 5, 2014 at 11:25 pm
Q1. Why have you created a new column BasicData as the Primary Key? I actually want School Code as the primary key
Because you were getting the Primary key violation on School Code, which was your actual problem. which means there are duplication in your excel data. if you want to use the schoolcode as primarykey then you need to remove the duplicates before inserting the records into this table.
2. What is the meaning and significance of the keyword "Clustered" in the T-SQL query that you had posted as a solution to me yesterday.
When you define primary key in sql server its by default is a clustered indexes. to get the idea what is index and what are their types kindly refer to the following Stairway to Sql Server Indexes[/url]
November 5, 2014 at 11:42 pm
Thank you for answering the Q1. Now let me ask another question based on this. I have four Excel tables named BasicData, FacilityData, StaffData and StudentData. They all have SchoolCode as PK in them. Now, if I follow the solution which you have suggested here then I will have to add a similar Primary key to the other three tables too when I'm exporting excel data to SQL Server?.
Also Will I be able to extract data from all the tables in the database based on this new primary key?
Thank you for your help
Cheers,
Ashish
November 6, 2014 at 12:09 am
I have four Excel tables named BasicData, FacilityData, StaffData and StudentData. They all have SchoolCode as PK in them
if I follow the solution which you have suggested here then I will have to add a similar Primary key to the other three tables too when I'm exporting excel data to SQL Server?.
Also Will I be able to extract data from all the tables in the database based on this new primary key?
SchoolCode is you natural Key do not confuse it will the primary key.
Following might be the structure of your files (purely assumtions)
1) BasicData which represent the Schools, main table you can also call it Master table.
2) following table FacilityData, StaffData and StudentData are considered as Child table because all will have SchoolCode in them.
You can join these tables on SchoolCode anytime from the basicData table to get the id.
you are focusing on the solution instead of fixing the actual issue. which is SchoolCode is getting duplicated in you basicData (Excel sheet) as you have mentioned that SchoolCode is you Primary key. which is clearly not in this case.
The above solution is just to give you can idea that if you change your primary key, you will be able to insert records in BasicData.
but this will not fix your actual problem. consider the following scenario
Declare @BasicData TABLE
(
BasicData INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,District_name nvarchar(255)
,School_code nvarchar(255) NOT NULL
,School_name nvarchar(255)
,Block_name nvarchar(255)
,Cluster_name nvarchar(255)
,Village_name nvarchar(255)
,[Year] nvarchar(255)
)
Declare @StudentData Table
(
School_code nvarchar(255) NOT NULL
, Student_code nvarchar(255) NOT NULL
, Student_Name nvarchar(255) NOT NULL
)
------ If there are duplicates in the Basicdata table
insert into @BasicData
(District_name, School_code, School_name)
select 'ABC', '111', 'ABC School' union all
select 'ABC', '555', 'DDD School' union all
select 'ABC', '111', 'XYZ School'
----- @StudentData is a Child table so that SchoolCode can be repeated as there can be many student in a school.
insert into @StudentData
(School_code, Student_code,Student_Name)
select '555', 'Student001', 'Student A' union all
select '111', 'Student002', 'Student B' union all
select '111', 'Student003', 'Student C'
------------ now Student B & C both represent two different school. this issue is appearing because you have duplicates in your BasicData.
select b.School_code, b.School_name, f.Student_Name
from @BasicData b
join @StudentData f on b.School_code = f.School_code
Hope it helps
November 6, 2014 at 12:39 am
Thank you for clarifying the doubts succinctly. Your lucid explanation helps in understanding a lot. I clearly understand now the reason to introduce a new Primary Key because SchoolCode was being duplicated in the basicData table and that would not allow me to insert records in the basicData table in the first place. So for the remaining three tables I will introduce a new primary key each because they already have schoolCode in them.
Although the questions have been answered satisfactorily to which I'm grateful to you. But I will keep this post open for the time being in case I have anything similar or related to this topic to clarify with. Now that you have a fair idea about my rudimentary knowledge of databases, any good resource you could suggest that I can read through and work on this project simultaneously?
Thank you for your help.
Cheers,
Ashish
November 6, 2014 at 2:46 am
SQLServerCentral there are alot of articles which might help you to kick start your database knowledge and understanding.
"Stairways" is one of the best because its start from very basic stuff and then go into complexity. you can study them which you think are relevant. Beside that if you need any clarification or any understanding you can post your question and we will try to help you.
November 6, 2014 at 7:50 pm
Thank you for the suggestion. Earlier I had briefly looked at "Stairways" but did not delve deeper into it. And so far my initial experience interacting with folks here has been fantastic. You guys are experienced, knowledgeable and above all down to earth with a will to implicitly help newbies like us especially vividly explaining the concepts.
Thanks for your help
Cheers
Ashish
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply