Post reply

Unable to dynamically add multiple excel files into SQL Server 2008

  • November 4, 2014 at 11:48 pm

    #397608

    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

  • November 5, 2014 at 12:37 am

    #1756663

    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 [/url]Smart way to ask a question
    There 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply