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 ] 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.