SSIS Package Error

Biztalk is not always the right tool for Bulk data inserts. SQL Integration Services provide powerful tool set for inserting large bytes of data from source ( flat files / excel files/DB) into DataTables. In my scenario, I created SSIS package using SQL 2008R2 Business Intelligence Development Studio. It was a simple Data flow, read the excel file from a network location and insert to a remote DataTable. Image below:


On executing the package, OLE Destination 1 shape would threw the following error:
“[OLE DB Destination 1 [421]] 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_tbl_so'. Cannot insert duplicate key in object 'dbo.tbl_so'.".”

I tried various online forums but the solutions didn’t work for me. Then I tried data encryption and it worked. Check the image below.







Update

After I deployed the package to remote server, I tried executing it and received validation errror around login/username. I am using SQL authentication for the DB that will receive the data from excel sheet. Internally, SSIS encrypts the credentials information. If you open the package in a notepad, you can view the encrypted information. But, when the same package is run on a different machine the user context changes and decryption process throws the error. Setting the protection level as below can get you around that issue.