Issue with SQL Server Bulk Load

[thumsup9]

Below is all the error in the log when we are bulk loading into SQLServer. Username,Pwd,tablename and the Server Name seem to be fine as we could load it using an ODBC stage.Please advice.

SQL server OLEDB provider authorization failed.
Failed to establish datasource connection.

[gootala_ravi]

Below is all the error in the log when we are bulk loading into SQLServer. Username,Pwd,tablename and the Server Name seem to be fine as we could load it using an ODBC stage.Please advice.

SQL server OLEDB provider authorization failed.
Failed to establish datasource connection.

Seems like a problem with installation OR license issue. I am not sure though

[shawn_ramsey]

I would double check your connection information since it does not look like you are connecting to the databases.

[jdmiceli]

Howdy!

Some other things to check:
1. Make sure the OLEDB driver is registered properly
2. Make sure that the DSN is using the proper Ascential SQL Server Wire Protocol driver.

If you are sure you have access to the system, then these are my first suggestions.

Bestest!

[jdmiceli]

Hi again,

I received a private email on this, but thought the response would be of benefit to others should this be their situation. Here is the text I was sent:

Hi John

I am trying to set up an environment where my target DB would be a SQL Server. Basically its a Windows Unix based Environment where my source system is in main frames. We are basically trying to set up an environment and were debating about the target DB. Could you shed some light on the performance part of SQL Server and how it would act at huge volumes say around 10 million rows.


Thanks alot sir and all your help in this regard would be appreciated


regards,
Bhanu

Now that we know what the target system is, the problem becomes evident. The target is Unix based and you cannot use OLEDB to attach to Unix (all you guru's out there - if I've misstated this please correct me). Your only option here is ODBC (Bill Gates' revenge) or a third party driver that will act as a translation layer for OLEDB to Unix.

I believe you will need to use ODBC to do what you wish to do. As for loading 10 million rows at once, perhaps you should look at partitioning the job or running multiple instances of it at once. There a lots of posts out on the site giving you instructions on how to do it (plus I'm still figuring it out myself, so I would be a bad source )

Ken Bland has an entire framework methodology for doing stuff like this, so you may want to look at how his job control stuff works as this is part of it.

Hope that helps!

Bestest!

[thumsup9]

Thanks for all the inputs. I am waiting to hear from our admin. I will post the results as it comes.

Thanks

[jdmiceli]

It just dawned on me, MCDBA testing notwithstanding....

How is SQL Server running on Unix??

Do you actually mean that your server setup is as follows (trying to make sure I've understood your messages above):

Source system: Mainframe
DataStage Server: Server Edition running on Unix ???
Target system: Microsoft SQL Server running on Windows ???

If this is the case, then my statement above is true. If different, then I may need to restate...