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

db seperation

I was given a task of doing the following and I'm not 100% sure what the
manager means. I'm not a database admin at all so I'm totaly new to all of
this. The manager wants me to seperate the transactional database from the
backend database.
What does that mean exactly? Can anyone provide me any information on what
exactly is and how to go about designing it then putting it into a working
mode?
thanks
Thanks, I'm in that process now actually to get more details on what she
actually wants done with the databases.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6SNFx3rHHA.4180@.TK2MSFTNGP04.phx.gbl...
> He might mean that the 2 databases are 'transactional' and 'reporting/BI'
> and should go on different servers, but who knows? We can help with
> technical details but you'll need to clarify the requirements fully with
> the manager first otherwise it's guesswork...
> Cheers,
> Paul Ibison
>

db seperation

I was given a task of doing the following and I'm not 100% sure what the
manager means. I'm not a database admin at all so I'm totaly new to all of
this. The manager wants me to seperate the transactional database from the
backend database.
What does that mean exactly? Can anyone provide me any information on what
exactly is and how to go about designing it then putting it into a working
mode?
thanksHe might mean that the 2 databases are 'transactional' and 'reporting/BI'
and should go on different servers, but who knows? We can help with
technical details but you'll need to clarify the requirements fully with the
manager first otherwise it's guesswork...
Cheers,
Paul Ibison|||Thanks, I'm in that process now actually to get more details on what she
actually wants done with the databases.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6SNFx3rHHA.4180@.TK2MSFTNGP04.phx.gbl...
> He might mean that the 2 databases are 'transactional' and 'reporting/BI'
> and should go on different servers, but who knows? We can help with
> technical details but you'll need to clarify the requirements fully with
> the manager first otherwise it's guesswork...
> Cheers,
> Paul Ibison
>

db seperation

I was given a task of doing the following and I'm not 100% sure what the
manager means. I'm not a database admin at all so I'm totaly new to all of
this. The manager wants me to seperate the transactional database from the
backend database.
What does that mean exactly? Can anyone provide me any information on what
exactly is and how to go about designing it then putting it into a working
mode?
thanksHe might mean that the 2 databases are 'transactional' and 'reporting/BI'
and should go on different servers, but who knows? We can help with
technical details but you'll need to clarify the requirements fully with the
manager first otherwise it's guesswork...
Cheers,
Paul Ibison|||Thanks, I'm in that process now actually to get more details on what she
actually wants done with the databases.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6SNFx3rHHA.4180@.TK2MSFTNGP04.phx.gbl...
> He might mean that the 2 databases are 'transactional' and 'reporting/BI'
> and should go on different servers, but who knows? We can help with
> technical details but you'll need to clarify the requirements fully with
> the manager first otherwise it's guesswork...
> Cheers,
> Paul Ibison
>

DB restore puzzle

Hi friends,
I like to write a program to automate the following time
consuming and tedious task:
A database was full backed up and got pruned to keep one
week worth of data daily. Now I need to build an archive
type database using all the backup files I have since the
beginning of last year. This archive database contains all
the history data, just lile the original database never
gets pruned. How to do it?
My current plan is to:
1. loop through the backup files
2. For each back up file, restore it using "restore DB"
command
3, After each database is restored, load the data into my
archive database
4. loop to next backup file
5. When all the back files have been processed, remove the
duplicate from archive database.
Although I have not tried, I can expect one problem I am
going to run into is that I can not restore the database
in loop because the prior restored database "is in use".
Does anyone have any suggestion?
Thanks a thousand times in advance,
New Bee> Although I have not tried, I can expect one problem I am
> going to run into is that I can not restore the database
> in loop because the prior restored database "is in use".
If you don't have a connection yourself in the db and no users has connections to the db, then you
don't have to worry. If you are worried about users having connections, you can use ALTER DATABASE
to set to single user and the ROLLBACK option to kick out current users.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"New Bee" <anonymous@.discussions.microsoft.com> wrote in message
news:0beb01c397da$ac242320$a301280a@.phx.gbl...
> Hi friends,
> I like to write a program to automate the following time
> consuming and tedious task:
> A database was full backed up and got pruned to keep one
> week worth of data daily. Now I need to build an archive
> type database using all the backup files I have since the
> beginning of last year. This archive database contains all
> the history data, just lile the original database never
> gets pruned. How to do it?
> My current plan is to:
> 1. loop through the backup files
> 2. For each back up file, restore it using "restore DB"
> command
> 3, After each database is restored, load the data into my
> archive database
> 4. loop to next backup file
> 5. When all the back files have been processed, remove the
> duplicate from archive database.
> Although I have not tried, I can expect one problem I am
> going to run into is that I can not restore the database
> in loop because the prior restored database "is in use".
> Does anyone have any suggestion?
> Thanks a thousand times in advance,
> New Bee
>

DB Maint Plan - Cleanup old DB Backup files not working

I created a DB Maint plan with just a 'Maintenance Cleanup Task'; the job executes fine, but does not remove files. The sql it says it's running is: EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup',N'.bak',N'9/11/2005 11:12:31 AM', any ideas? The logfile says it worked. June CTP.Found the problem, shouldn't have had the '.' in the extension.