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


Unable to dynamically add multiple excel files into SQL Server 2008


Unable to dynamically add multiple excel files into SQL Server 2008

Author
Message
Ashish Dutt
Ashish Dutt
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 84
Dear forum members,

I have several hundreds of excel files that I must transfer data to SQL Server 2008 with service pack 1. 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


[OLE DB Destination [20]] Error: 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: "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
Stuart Davies
Stuart Davies
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16834 Visits: 4894
There are multiple post for this question. Please reply on this post :- http://www.sqlservercentral.com/Forums/Topic1630950-1292-1.aspx
Thanks

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
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