SSIS: Insert and Update data in Oracle table from SQL Server

Recently I was asked to develop a package which would pull data from SQL Server Database and insert and update data in Oracle database.

To begin with this, we have to keep few things in mind:

First make sure that you know what data type are in your source and destination table. For example say EmpID is a integer in SQL Server database but chances are that it is NUMBER in your Oracle table to make sure to do correct data conversion task.

Secondly, in SQL Server, commit is auto, so we don't have to issue any statement (chances are that database is setup like this) however in oracle database you have to issue commit statement after you make any change to data.

For my SSIS packages, I designed my task into following steps:

1. I created a temp table in oracle database to dump all my data from sql server (using right data conversion and data type.
2. I created a Merge Stored Procedure on Oracle side which I would execute inside my SSIS package to update and insert data.

And everytime, I use DML statement, I use SQL excute task to issue commit statement on Oracle Database.

Also make sure that you use ADO.Net connection Manager to communicate with your oracle database.

To call stored procedure inside Execute SQL task use something like this.

 CALL MY_Stored_Procedure ()

If you get this error message,

Warning: Multiple-step OLE DB operation generated errors. 
Check each OLE DB status value, if available. 
No work was done.

It means that you have missed commit statement.

How to upload documents in sql server table

How to upload documents in sql server table

Not the best solution but you still want to do it??

Lets do it.

Step 1: Create a table called documents with following columns: docID int IDENTITY, DocumentName varchar(100), Document VARBINARY(MAX), DateCreated Datetime default (getDate()));

Create table documents
(DocID int IDENTITY(1,1),
DocumentName varchar(100) NOT NULL,
Document VARBINARY(MAX),
DateCreated Datetime default (getDate())
);


Step 2:

Lets say we have a document tittle " A sql guide" and we want to store it into sql server so that incase we want to use it later for some reason.

Say this is located in C:\documents\sql\ A sql guide.doc

let's save this document in sql table:


Declare @doc As VARBINARY(MAX)

Select @doc = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET
(BULK 'C:\documents\sql\A sql guide.doc', Single_BLOB) AS x

INSERT INTO documents (DocumentName, Document) Values ('A sql guide', @doc)

And You are done!!