Thursday, November 10, 2011

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.



No comments: