DB2 Connect and SSIS | Error in OLE DB Source Task

Hi All,

I am trying to connect to DB2 database via OLE DB connection manager in SSIS. But when I enter the SQL Query and press OK it gives following error

"Error at Data Flow Task - Header Load [OlE DB Source - Header_Load[1]]": An OLE DB Error has occured. Error Code: 0x80040E21

Additional Information:

Exception from HRESULT: 0xC0202009(Microsoft.SqlServer.DTSPipelineWrap)"

I followed following steps: -

1. I created OLE DB provider and tested the connection, it was successful(with give username and password)

2. Created query in Build query as following and tried executing it. It worked! Query used was

SELECT SRC_ID, ORG_ID FROM DB123.DEAL_HEADER

3. But when, in OLE DB Source Provider Task, when I press preview, It thorws the above error!

Kindly let me know, Because I am stuck at that point.

Thanks

Sid

What happens if you don't preview the results? Just run the package normally after building the query.|||In that case; when I press "OK" in OLE DB Source Task, the same Error appears. In short, I am not able to save the task and move futher with my implementation.|||What driver are you using to connect to DB2? IBM's DB2Connect?|||Yes I am using IBM's DB2 Connect|||

sidzone123 wrote:

Yes I am using IBM's DB2 Connect

Well, if you are on SQL Server Developer or Enterprise edition, you can download the Microsoft OLE DB for DB2 driver. That works really well for me.

Never-the-less, you can try some of the techniques in here to see if they'll help your situation, even though it doesn't deal with DB2 directly:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142282&SiteID=1|||

Hi Phil,

I am using SQL Server Standard Edition.

However, I tried all methods suggested by the link suggested by you.

But unfortunately nothing work!

Thanks

Sid

|||

Hi,

I found the workaround for this!

1) In OLE DB Source, Open the Advance Editor and in the Custom property, set the Access Mode to "OpenRowset"

2) In the OpenRowset property, write the table that you want to access i.e. say "CZ123"."DEAL_HEADER"

Thats it and press OK!

To my surpise it worked great. I was able to connect to DB2 and transfer the data.

However I am not sure about why I was getting the errors that I mentioned earlier and why the above solution worked. Still trying to find the aswer to this.

Hope the same works for all.

Thanks

Sid