Problem with Bulk Insert Task and Oracle Database

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

Saurabh Kulkarni wrote:

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

I don't know for sure but I can make an educated guess.

The Bulk Insert task leverages SQL Server's bulk insert functionality. Hence, it is to be used with SQL Server only. The first sentance in the BOL topic about Bulk Insert Task says "The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view"

i.e. Not Oracle.

Did you read the documentation?

-Jamie

|||

Jamie is right; here is the link to the BOL that talks about Bulk insert task:

http://msdn2.microsoft.com/en-us/library/ms141239.aspx

To solve your problem you can use OLE DB Destination instead and choose fast load option...

|||Rafael,

As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?

Thanks.|||

Kaarthik Sivashanmugam wrote:

Rafael,
As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?
Thanks.

I was not confused; I provided the link to support Jamie statement. I have edited my previous post to clarify it.

|||

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

|||

OLE DB destination has several moving parts that will allow you to tune it for large amount of rows. First at all; you should use fast load as the data access mode; at least against SQL server that works as bulk inserts. Then you have 'rows per batch' and 'Maximum Insert commit size' parameter that would help to control how often the transactions will commit. I have seen at least 1 thread in this forum discussing how to use those options. Also and if third party providers are an option for you; I think there is at least 1 vendor offering a high performance connector for Oracle DBs (http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm). I recommend you to run some tests and see by yourself.

|||

Loading data into Oracle could be done by using the "execute process task" and then use sqlldr as command. You have to install the oracle client - which contains the sqlldr - on the maschine, where your package will run. Using the process task gives you all the flexibility for dealing with paramters, config-files and so on, which makes sqlldr fast.

I realized a similar scenario for transfering Sybase data into a MS SQL Server by using standard utilities from Sybase to get data out (or in, whatever the direction is), dependent on a configration table (so fully dynamic).

Norbert

|||

Saurabh Kulkarni wrote:

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

The OLE DB Destination is perfectly fine for loadif to Oracle as long as you use it the right way. Read these links:

Persistent Oracle Connector
(http://blogs.conchango.com/jamiethomson/archive/2006/04/07/3575.aspx)

Loading to Oracle
(http://blogs.conchango.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-Loading-to-Oracle.aspx)

-Jamie

Problem with a Web Service Task that uses a digital certificate

Hi every body... I will thank u the help that you could bring me...

This is my problem:

I am using a Web Service Task in Integration Services for access a Web Method, this method is not mine, it is a method provided for another company. This Web Method is published in a VPN (Virtual Private Network) and it is in a secured channel (https).

The properties of the Connection Manager that the Web Service Task uses are ok. The Server URL is the address of the asmx and the client certificate is the correct. In fact when I press Test Connection, the message Test Connection Succeed is shown. If I change the certificate, a connection error occurs (403 Forbidden), this means that the client certificate and the address is ok. I addition, I use this certificate to access another methods published by the same company in a Visual basic application (with WSE) and I don′t have problems.

When I execute the DTSX the error "403 Forbidden" occurs and I can't execute the method. I executed the DTS in the VS2005, SQL IDE and in a batch file (.bat) too, and the same error happened.

If I use the File Monitor tool (from SysInternal, that now belongs to Microsoft) I can see that when I test the connection (in the Connection Manager used by the Web Service Task), the process accesses the certificate's private key located in the RSA folder of my certificates. But when I run the DTSX (no matters which way), it never reads the private key, it only accesses the certificate repository.

I saw that the code of the DTSX includes the certificate in Base64, but I am thinking that this code doesn't include the private key (I don't know if it would have to do it)...

Well, the fact is that I can′t use this Web Service task. I am suspecting that is a bug of the Integration Service

Thanks for the help that could bring me...

Can you tell us a bit more about http connection manager's credentials? what user credentials are you using to access the web service?

|||

Sure ...

The properties of the connection manager (seen in the edition window) are:

Server URL: https://<server url>/<folder name>/webservice.asmx. Where <server url > is the direction of the server, <folder name> is the folder name where the webservice is and finally the webservice.asmx. The direction is ok because I am using another .asmx in the same direction with a .net windows service application and all works fine with my certificate.

Another important thing is that I don't need to use a special network user, or account, to access the web service from this .net windows service. In fact the windows service runs with the Local System Account. For that, I installed the certificate (with it's root certificate) in the Local Computer repository. For my tests I installed the certificate in the Current User repository too.

In the checkbox "Use Credentials" I have tried nothing and my network user, but the same error raises with both.

In the "Client Certificate" I chose the certificate.

In the Proxy settings (the another tab) I don't have to put information because the connection is not made between a proxy.

As you can see, the problem is very strange, because I can access the Web Service from the Internet Explorer and .Net applications.

Thanks for your time and help...

Problem when passing parameter to Execute SQL Task

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?\ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?\ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?\ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?

Thank you!

Did you set the "BypassPrepare" to True on the execute task? That gets me every time I use variables.

If you don't set that, it treats the ? as a literal and fails.|||

Hi!

Yes, BypassPrepare is set to true...

Thank you!

|||Setup another variable to hold your full string so that in your SQL you only have the ? placeholder instead of ?/ASPNET.

So, in my test, I created a new variable, set its expression to concatenate the User::ServerName variable and the /ASPNET string. Set the new variable to EvaluateAsExpression and then use it in the parameter mapping of the ExecuteSQL task.

Does that do what you're looking for?|||

Hi!

I tried what you suggest and I still have the error... Then I tried something simpler, CREATE LOGIN ? WITH PASSWORD = 'lalala', and I got the following error:

Executing the query "CREATE LOGIN ? WITH PASSWORD = 'lalala'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And if I change it to CREATE LOGIN test WITH PASSWORD = 'lalala' it works fine, so I guess it's not a connection problem. I guess I'm missing something silly!

Thank you!

|||What type of connection are you using? OLE DB, ADO, ADO.NET, ODBC, etc..

The connection type that you use will dictate how you name the parameter and then use a parameter in your SQL.|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx|||

I'm using an OLE DB connection, and it seems the parameter name is ok... Posting the code would help?

Thank you!

|||I have a variable (User::Test) set as a string with a value of "P0160".

In the Execute SQL Task editor, my sql statement is:
update client set clientdesc = 'testing' where client = ?

Then, in the parameter mapping section, I added a variable and selected User::Test as the variable name. Its data type is VARCHAR. The Parameter Name is simply, 0.

That's it. I run the task and it works correctly.|||It works now. Thank you very much for you help!

Problem w/ Script Task accessing Directory Services

Using the example from SS online books:

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

I'm nod directory services expert, but from MSDN, the indexer of ResultPropertyCollection is declared as

Public ReadOnly Default Property Item ( _ name As String _ ) As ResultPropertyValueCollection

I.e. result.Properties("name") returns a collecions of properties, not the value.

|||

Found additional examples of how to do this and got it working

thanks

Problem w/ Data Flow Task of SSIS Package

I have a Data Flow Task in an SSIS package that transfers data from a local table to a table on a remote server. The remote database is SQL Server 2000 and the local one is SQL Server 2005. The package in the designer runs great. But, when I setup the job for the server agent it fails with the connection. I get the following error:

The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.

This is on validation from what the error logs say. It is probably just a simple configuration problem or something I have overlooked.

=============================================================

I also ran a seperate import task on the remote server (the same one as above) database table (Tasks > Import Data) and saved it as an SSIS package as the last option it gives you in the wizard. I am copying from a local table to a remote table. The import was successful as expected. But, when I run that same SSIS package in a SQL Server Agent Job, that package fails. I have not made any changes to the package what so ever. I never even opened the package to look it in the editor. I just ran it as is.

I am thinking it is a security problem of some sort. Like the Agent does not have right privilidges. I am running the packages under my local system account.

Thanks in advance!

This does sound like a security problem. If you're executing from SQL SAerver Agent then the packages will run as the startup acocunt for SQL Server Agent. Does this account have permissions on the source database?

There is a way that you can see what connection string is being used at runtime as I have explained here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

This will cause the connection string to be output to your log provider (assuming you are using one and if you aren't - you should be).

-Jamie

|||

Thanks for your response.

I am running the local database from a Windows XP development machine. This is where the SSIS package resides because the hosting provider I am using for the remote database does not support SSIS. So I have to run the Server Agent locally and then copy the data over as the last step of the package.

So to answer your question, NO, my local account that is running Server Agent is not on the remote server. I am using the same username/password on the remote machine as I am for the ASP.NET pages on the application on that remote machine. The remote account works. I am using windows security locally on my development for the database server login.

As I mentioned earlier, I can run the SSIS package in the designer without a problem but it does not work in the Server Agent.

I do have a log provider setup to output to a table on the local database. Below is what the log output is (I replaced the actual server name for security reasons to "server.name.here"):

__

988	OnInformation	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	1074016266	<Binary data>	Validation phase is beginning.
989	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>	Validating
990	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	50	<Binary data>	Validating
991	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1071611876	<Binary data>	The AcquireConnection method call to the connection manager "server.name.here" failed with error code 0xC0202009.
992	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450985	<Binary data>	component "Destination - Listings" (187) failed validation and returned error code 0xC020801C.
993	OnProgress	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	100	<Binary data>	Validating
994	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073450996	<Binary data>	One or more component failed validation.
995	OnError	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	-1073594105	<Binary data>	There were errors during task validation.
996	OnPostValidate	RETHINK1	NT AUTHORITY\SYSTEM	Export Listings Data	13d18a9e-932d-4bcc-9b14-d55c139c0bec	b3ae2538-1821-4999-9c2d-b29cc3511ca6	12/18/2005 4:33:52 PM	12/18/2005 4:33:52 PM	0	<Binary data>

|||

After my last post I changed the timeout on the remote database connection to 60 seconds as you suggested in your blog. I would have never thought that it would have been that simple but it fixed the problem. I thought that the timeouts set at 0 seconds meant there WAS NO TIMEOUT. BAH!!

Thanks for your help! I was about ready to pull my hair out.