Can I create ODP.NET connection in my SSIS connection manager. I had downloaded and installed ODP.NET on my server provided by oracle. The idea is I need to test this provider and see what is the difference connecting oracle database and the data load speed.
thank you
I didn´t know where to put this so sorry if this is not the appropriate place.
I am running Windows XP pro sp2 with visual studio 2005, ms sql server 2005, and a number of other applications.
My problem is that I am not allowed to install new windows components(I want to install IIS). When I try to access from either control panel, add/remove programs, Install new windows components or directly from the winXP cd I get the following error in a pop-up form and I can only press "OK":
"Another copy of Setup is already running. Complete the other Setup or restart Windows."
I am not running the instance more than once. I do this as the first thing after a restart with the same result. When looking at the processes in the task manager I cant see anything unusual.
It is a pretty slow computer so that may have something to do with it - some timeout or similar.
It is getting pretty frustrating so any suggestions are more than welcomed! thanks.
EDIT: I used process explorer to locate some stalled windows update process which I killed. Then msmqocm.inf was missing, but that seems to be a FAQ.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
hi all.
In my SSIS project, i have to retrieve data from an oracle 10g database. On my personal laptop it works, but on our test-env i get the error below, when i try to test my connection to the oracle datasource.
Could this because i have oracle XE installed yet on my personal laptop, and it is not installed on the computer of our test environment where the SSIS runs? If that's the reason, it sounds very strange, because it would mean i have to install an oracle environment also on the computer with SSIS, just to make a connection to another oracle server...
Error:
TITLE: Connection Manager
------------------------------
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.
------------------------------
BUTTONS:
OK
------------------------------
I'm attempting to move data from an Oracle table to an Access table using an Oracle stored procedure in DTS. The problem is that you can't pass parameters to an Oracle stored proc when its called in a data pump task. Is there a way to pass global variables into an Oracle stored procedure which retrieves data and moves it to an Access database? Maybe in an Active X task? We are required to use an Oracle stored procedure by our DBA's or else I'd just pass the variables into a SQL string and use it in the data pump task.
View 1 Replies View RelatedHow to access the XML, CLOB and BLOB Oracle data through linked server from SQL Server 2012?
I want to retrieve XML, CLOB and BLOB data from SQL Server 2012 through Oracle Linked server .
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies View RelatedI am trying to execute oracle procedure (anonymous block) with input and output parameters from ms sql code and return 2 values back:a number and a string.I am using **EXEC() AT** to execute oracle code and return a string back to ms sql. It works fine when returning numbers, but if I add a string output parameter, I am receiving an error:Msg 0, Level 11, State 0, Line 0...A severe error occurred on the current command.  The results, if any, should be discarded.
The server log has the following entry in exception.txt:
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 00000005031C07E0 at 0x0000000070F9C183
There is also a SQLDump file generated:
* BEGIN STACK DUMP:
* Â 10/28/15 12:45:38 spid 63
* Â Exception Address = 000000005040C183 Module(MSVCR100+000000000003C183)
[code]....
It looks like the problem is in passing varchar2 from oracle into ms sql. I had logging code in oracle block, and it works fine. I have found a few examples of using **exec() at** but there is always only integer output passed back.
This is a fresh install of SQL Server 2005 (64bit) on a Windows 2003 R2 SP2 64bit OS. The Cluster has been built and SQL 2005 has been installed (Database Engine(clustered), Analysis Server(clustered), Notification Server, Integration Server, Reporting Services). SQL 2005 SP2 has been installed.
Now, I want to install Notification Server, Integration Server and Reporting Services on the second node for high availability.
When I select 'Install New Components' from Add/Remove Programs, the System Configuation Checker tells me theres a version difference and to:
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
I'm using the same binaries as the original install. Could this be a result of installing SQL 2005 SP2?
Can someone help me out with this. I'd prefer using the setup interface instead of a command line script.
How can I access data to SQL server 2005 using Windows form application,ADO.net and .Net remoting?
Can anybody help me? please...
Hi,
Pls let me know if SQL Server 2005 Management Studio (client tools only) is compatible with Windows Server 2000 or not?
Thanks in advance.
Regards,
Bhuvana
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
Hi,
I've just installed SQL Server 2005 onto a new Windows 64 bit server and am trying to set up Oracle connections (we have an Oracle 8i database) in SSIS with no luck.
Has anybody had any luck in this area? We currently have the Oracle 10g client installed with nothing else.
When I attempt to use the OracleClient Data Provider in BIDS I get the following error:
------------------------------
Test connection failed because of an error in initializing provider. Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.
------------------------------
When I try to use the Microsoft OLEDB Provider for Oracle in BIDS I get this error:
------------------------------
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.
------------------------------
Do I need to have a 32 bit client installed along with the 64 bit client? When we initially had the 32 bit client installed I was constantly getting errors about being unable to resolve TNS names and I did some reading that said this was due to problems with the (x86) folders and Oracle having a bug with parsing these folders but couldn't really find any clear cut resolution.
Any help would be greatly appreciated as I've been butting my head against a wall on this for a while now.
Thanks in advance,
Nathan.
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Hi,
In another thread Jamie Thomson very informatively said "The components in SSIS
are deliberately atomic (i.e. they do something very specific) so that
its easy to put them together to build something greater than the sum
of the parts". Which does make a lot of sense. However, I've been finding that I end up having to create exactly the same "pattern" of combined transform components again and again in order to solve the same problem but in different dataflows (or even within the same dataflow). Cut-and-paste-tastic! In order to obtain real re-use, it seems to me like SSIS is crying out for an easy way to create new components by using composition - i.e. the ability to take commonly-used combinations of existing components and create new "super" components (without having to write Custom Transform Components in C#/VB.Net and handle everything in code).
Does anyone know if this sort of functionality is likely to make it into SSIS in the forseeable future?
Regards,
Lawrie
I am currently having issues getting the odbc32ad driver configured properly so I can transfer the database off of the old Oracle 7 database (currently running SCO UNIX). I have the orignal Oracle 7 disk and installed the drivers, but I am not sure what the SA login for Oracle is, nor do I know what the "Connection String" is.
Has anyone made this transfer before? Please help.
I am trying to pull data from an Oracle 9i database to my SQL Server 2005 sp1 database on Windows 2003 64bit Itanium server. Each time I try to use the import wizard, select the Microsoft OLE DB driver for Oracle, I get an error message telling me that the Oracle client tools are not installed. This happened after I had already installed the 9i tools, tested the connection using TNSPING and confirmed the tnsnames file was correct. So, I uninstalled the 9i tools and then installed the 10g client tools. Tested everything after the install and then tried the import again selecting the MS OLE DB driver for Oracle. I again got the error message that the Oracle tools were not installed.
has anyone ran into this problem and if so, were you able to fix it?
Hi, all...I have a sizeable database running under Oracle 9.2.0.4 under AIX5.2.0.I am faced with an impending move to a Windows environment, runningunder SQL 2000.Currently, we are heavy users of shell scripts, for exports, backups,etc.I am looking for viable scripting options under W2K/W2K3. Maybe sh/kshemulators, VB, Perl...?Scheduling of script operations is also required.Anyone been through this kind of move, who could offer some wisdom?Thanks!DW
View 19 Replies View RelatedGod morning!
I 've just installed SQL Server 2005 on Windows 2003 Server 64 bit. One of my databases need to connect to seveal Oracle databases.
- I tried installing 64 bit Orcale 9 on this server. Got a strange message that the application (Oracle 64 bit) could not be installed on a 32 bit server! Strange!
- I installed Oracle 9 32 bit on the server, but I could not find ODBC driver in the Data sources! Neither the oledb under providers in MSS Manager Studio!
- Same result efter several desintall and installation. A college copied the .dll files och run a script to register the dll files. We could then se the Oracle driver in the Database sources, but we could not create any connection.
- We have even tried an 10G version of Oracle with the same result.
Is there anyone who succeeded installing Oracle on 64 bit Windows and could create odbc connection/linked server?
Regards
Does anyone know a good place to download (or even purchase) custom data flow components?
Some ideas I've had (and I'm sure I'm not the first):
Transformations:
Filter
Sometimes I just want to filter out certain rows.
Unicode Converter
Convert all unicode columns in place.
Sequence Column
Adds a column with a sequence of numbers. Or perhaps even random numbers.
GUID Column
Adds a column with a GUID.
Destinations:
Garbage
Would act as a visual indicator in the designer that these rows are being discarded. You could add a data viewer right before it too.
Log File
Good for redirected error rows, writes them to various log files.
Email
Along the same lines as Log File, but sends off all the rows in a single email. Great for day to day things. Nothing too critical.
Hi,
I am having a simple difficulty regarding my transformation component that I have created.
I followed all of the relevant documentation to create the component, UI and such, and when I run a program that transfers data through my component (without it doing anything to the data) to a flat file source, all I get in the flat file is ,,,,,,,,,,
Meaning that the data is not actually being passed through my component to the destination, but it does recongize the correct number of columns. I get this warning for each column: [DTS.Pipeline] Warning: The output column "PurchaseOrderDetailID" (20) on output "OLE DB Source Output" (11) and component "Source - PurchaseOrderDetail" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
To fix this issue, all I need to do is "check" the boxes in the advanced editor for my component. However, I want to be able have these boxes "checked" automatically.
My question is, when you "check" a box next to a column name in the advanced editor, what does that exactly do that allows you to transfer the data? What do I need to program in order for it to replicate that? I actually want it to happen automatically, so by default, all data in all columns are transfered through my component out the other side, so I just need to know how to do this by code, and not how to replicate the UI.
My component, I thought, did that automatically, as I specified everything that I thought was required based on all of the documentation I read. Obviously, I am missing something. Here are the methods that I believe would all be involved.
Please let me know what I am missing.
int[] inputColumnBufferIndexes; // ...
int[] outputColumnBufferIndexes; // ... used in PreExecute
PipelineBuffer outputBuffer; // used in ProcesInput
public override void OnInputPathAttached(int inputID)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vCol in vInput.VirtualInputColumnCollection)
{
IDTSOutputColumn90 outCol = output.OutputColumnCollection.New();
outCol.Name = vCol.Name;
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
}
}
public override void PreExecute()
{
//base.PreExecute();
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
inputColumnBufferIndexes = new int[input.InputColumnCollection.Count];
outputColumnBufferIndexes = new int[output.OutputColumnCollection.Count];
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn90 column = input.InputColumnCollection[x];
inputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
}
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn90 column = output.OutputColumnCollection[x];
outputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(output.Buffer, column.LineageID);
}
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
//base.PrimeOutput(outputs, outputIDs, buffers);
if (buffers.Length != 0)
{
outputBuffer = buffers[0];
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
//base.ProcessInput(inputID, buffer);
if (!buffer.EndOfRowset)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
while (buffer.NextRow())
{
// TODO: Examine the columns in the current row.
// Add a row to the output buffer.
outputBuffer.AddRow();
for (int x = 0; x < inputColumnBufferIndexes.Length; x++)
{
// Copy the data from the input buffer column to the output buffer column.
outputBuffer[outputColumnBufferIndexes[x]] = buffer[inputColumnBufferIndexes[x]];
}
}
}
else
{
// EndOfRowset on the input buffer is true.
// Set EndOfRowset on the output buffer.
outputBuffer.SetEndOfRowset();
}
}
Hello everyone,
I am developing an SSIS solution where the Data Flow task extracts data from a source csv file, then performs several transformations on the source data and then starts inserting the cleansed data into several destination tables.
The Data Flow task is getting too large!
Question:
Is there a way (best practice) for grouping components in the Data Flow - similar to the Container concept in the Control Flow?
I know this question sounds too luxurious, but I really loose the overall picture, when the Data Flow canvas gets too crowded.
Thank you in advance.
Samar
Hi,
I have a SSIS Package which I would like to modify using SSIS API. I need to put new component between some two existing data flow's components. During this process I need to disconnect two data flow's components using SSIS API. How can I do that?
Thanks,
Rafal
I am using the SSIS wizard to pull data from DB2 z/os to sql server. The data flow task that is created converts the data to DT_STR Ansi 1252 before storing to sql server database. The package is blowing up on in the data conversion component...no match in found in target code page...for my city name field.
My old dts wizard didn't have this problem. The forums seem to indicate that SSIS is no longer doing some of the implicit conversions that DTS did and I may have to do more than one conversion.
What format type/code page do I use for the other conversion? The code page for my DB2 data source is 37.
I've tried several scenarios and none of them have worked. Any hints?
I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success.
- Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server
------------------------------
Cannot connect to ardsqldatawh.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
Any help would be appreciated.
949jc
We've been trying to use CozyRoc components in SQL server Data Tools. It seems like way to insert Cozy Components has been changed for SSDt as we're not getting CHOOSE ITEMS option in ToolBox(Right Click on Toolbox).link or Video where SSDT was used to Cozy Components.
View 2 Replies View RelatedThe following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
I've been having an issue with the integration of a third-party DLL into a custom data flow component.
The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.
I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.
The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".
I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.
What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
Does anyone know if I can have a MS Access project with Oracle backend ?
I know it's a breeze with MS SQL backend !
Thanks in advance !
Ivan
Hi all,
SQL Server 2005 SP2
Windows XP Pro SP2
Oracle 10g Client 10.2
Oracle Data Access Components 10.2 VB6
Oracle Rdb 7.1 (not Oracle RDBMS).
I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=someusername;
Password=topsecret;Data Source=my_oledb"
I got this example VB appication off the net but have to admit I'm not sure of the difference between
OraOLEDB.Oracle.1 & OraOLEDB.Oracle.
The alias in TNSNAMES looks like this...
my_oledb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (Host = spike) (Port = 1527))
)
(CONNECT_DATA = (SERVICE_NAME = oci_oledb)
)
)
I can also TNSPING this alias without any probems.
My SQL server linked server is setup as follows.
exec sp_addlinkedserver
@server = 'MFP2',
@srvproduct='Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'my_oledb'
exec sp_addlinkedsrvlogin
@rmtsrvname='MFP2',
@useself='false',
@locallogin='sa',
@rmtuser='******',
@rmtpassword='******'
Using both openquery and four part naming (?) I get the following errors
select job_title from mfp2...jobs
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
There are no schemas in the Oracle database so I've just used three dots between the linked server and the table name.
Again I know this syntax works becuase I have used it sucessfully with ODBC drivers.
select * from openquery(mfp2,'select * from jobs')
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
The passwrods are correct on the Oracle database as these are the ones used in the VB6 application.
Trying to expand the catalogue also causes management studio to hang.
Anyone spot the obvious mistake?
I did read on the net a while ago that registry entries might be rquired on the PC/server, is this still necessary?
Thanks in advance
Dave
HI All,
In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.
Thank you.
Steve.