April 6, 2011 at 4:37 am
Hi,
I have a clustered SQLS2K8 running on a 64-bit environment ( sql server therefor also 64-bit ). All good so far.
Now a customer wants me to transport data from sql to oracle using Integration Services.
I install SSIS on both nodes and Busines Intelligence Development Studio (BIDS) aswell. I also install 32-bit and 64-bit version of Oracle 11g client on the sql server. Then I create a project in BIDS using Oracle Provider for OLE DB. That works. Data flows correctly between sql to Oracle.
I then create an sql agent job and run the dtsx file that I created in the project. When I run the job it executes indefinitely. I need to stop the job manually.
Also when I create a linked server object and choose Oracle Provider for OLE DB the whole Management Studio Environment stops responding (freezes).
Has anyone come across this problem before and maybe knows of a good solution? 🙂
April 6, 2011 at 5:31 am
We have used Attunity to suck the data out of oracle. Should be possible to push it back into Oracle from SQL. Check that out if you haven't yet. http://www.attunity.com/index.aspx
Also is it copying any data across, ie it is merely trying to connect and the job doesn't know that it is not connecting? You could test this by executing the file by double clicking on your *.dtsx file. If it works through the file (and your credentials) and not through the job, it points me into the security access issue. We use config files for our SSIS packages as it is helps to iron out those security "niglies", as the job is running under the SQL Server agent context.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply