Automating the importation of an Oracle table into Sql Server 2000

Hi guys,

I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue....

I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up??

Thanks in Advance all.........

'WaleDid 9i ever come out with an export utility?

Your best bet is to schedule the Oracle export, TRUNCATE the SQL Server table, then do a bcp load or BULK INSERT...

or you can set up a linked server in sql server and do a delete and an insert from the oracle table...

The latter will incur more over head...but will appear to be easier...|||problem is that the ORACLE database is not in my control. So I can't just schedule an export.|||I wonder if you can bcp out data from a linked server...

look into sp_addlinkedserver

You must authority to that box, right?|||yeah I do have authority on the SQL Server box. i'll look into it.|||You'll have to look into sp_addlinkedsrvlogin as well

Automatically process a cube which is on another server

Hello

I want to create a DTS package on my server #1 which will process a cube on my server #2. The problem is that when I create the DTS package, I select the "Analysis Services Processing Task" and then, the only choice I have in the left box "Select the object to process" is the local server (server#1). How could it be possible to select my server#2 in that box ?

I know I can create connexions ... could that be part of the solution ?

I'm using SQL SERVER 2000 on server#1 and SSAS2000 on server #2.

Mike

When you're editing a DTS package, I believe the Analysis Services Processing Task will display servers that you've registered in Analysis Manager with your current login profile.

Open Analysis Manager and register the server that you intend to process cubes on, then try the package design again.

Are you logged directly into server #1 or term-served into it? If not, just be aware of the classic problem of DTS package design & deployment. Since Analysis services and DTS package design use live connections, what may work during DTS design time may not work once you, for instance, schedule the package to run as a job step, due to differences in permissions between the developer account credentials versus the account credentials of the service account which SQL Server Agent starts up as.

I hope this helps. I remember suffering through my first DTS package design sessions all too well.

CJB

|||

Enterprise Manager only allows me to register a SQL Server , but the database is on my server#1. My server #2 just has Analysis Services installed, so it is not a SQL Server.

I tried to register my server#1 with the Analysis Manager, and it worked ( I think it's because I have a sample cube on my srever#1), but that didn't change anything.

I still can't do what i need to.

Mike

|||

I think you're looking for the TreeKey setting. It's been so long since I've done DTS and AS2000 that I'm a little rusty. But have a look at:

http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Search for "TreeKey" then look at the image above that section. I believe if you set it to "YourServerName\YourCubeName" you should be able to process a cube on another server. I think you'll need a dynamic properties task to accomplish that.

And you might look at:

http://blogs.msdn.com/bi_systems/articles/141632.aspx

|||

thanks for your help, furmangg, but I am not familiar with dynamic properties tasks... What are they ?

And I never used ActiveX scripts, like it is suggested there http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Could you give me more detailed explanations, please ?

|||

You need a dynamic properties task to set the TreeKey property of your Analysis Services Processing Task. Here's more on dynamic properties tasks:

http://msdn2.microsoft.com/en-us/library/aa933528(sql.80).aspx

(If there's a UI way to hardcode the TreeKey for the AS Processing Task without a dynamic properties task, then you won't need one.)

I think you'll only need ActiveX script if you don't want to hardcode your TreeKey and want it to be more dynamic like that article suggests.

|||

Ok, I think I understand the problem now.

You can change the treekey setting by entering "Disconnected Edit" mode in the DTS designer. The drawback here is that you won't be able to double click your Analysis Services Processing Task to edit it. But that's okay, since it's not working anyway.

Right click anywhere in the background while designing the DTS package.

A dialog should appear.

Select "Disconnected Edit..."

Expand "Tasks"

Select the Task which is processing the cubes.

A collection of Task properties will show up in the right hand panel. The bottom one is "TreeKey", and its syntax is

servername\databasename\CubeFolder\cubename

So you might edit this to read (note that spaces here are fine, the package will "wrap" them properly at runtime)

mike8srv\FoodMart 2000\CubeFolder\Sales

Inspect the other property values, particularly DataSource (should match the name in Analysis Manager "Data Sources"), Fact table, and ProcessingOption (enumerated list: 0 = full, 1 = refresh, 2 = incremental).

|||

Maybe I was getting close of a solution with furmangg, but with your last post, you really solved my problem John !

I wouldn't have imagined a simpler solution !

thanks for your help to both of you - i really appreciate

Automatic Email

Dear Freind,
Its all boutt Dts.I have already created a DTSpackage and Activex script in VBscript to retrieve some particular names ...and i want to send those names through E mail.But the email should be automatic(using sql Sheduler ).Real Problem is...How can i write the code to access the DTS object from ASP.Net with VB? and How can shedule...please help me...i am hopefully waiting..........thanks in advanceIf you just want to send the mails queried from a table you can use the regular mail sending procedures from SQL Server like xp_sendmail (depends on your SQL Server version which to use)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Moving to the "SQL Server Integration Services" forum.

Automated DTS package will not run

I suspect this is a user-rights/security issue:
I have a DTS package that needs to run weekly. This DTS package runs fine
when you open it and select "execute". I have done the following to set
this up as an automated task:
1)Go to DTS/Local Packages and right click the package.
2)Select "Schedule" from the pop-up menu.
3)Change settings to "weekly" and set the time to the current time + 5
minutes
4)Go to Management/SQL Server Agent/Jobs and refresh
5)Find the job that you just created, right-click and choose 'properties'
6)Confirm the settings (Owner: <my login name chosen from list> ) and let it
run.
I set up the "Notifications" tab to send me an email on completion which it
does:
========================================
================
JOB RUN: 'PhoneImportFromHR' was run on 2/12/2004 at 2:30:00 PM
DURATION: 0 hours, 0 minutes, 11 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 32
(PhoneImportFromHR). The last step to run was step 1 (PhoneImportFromHR).
========================================
================
Due to the fact that the DTS package runs fine when I run it, but won't run
automatically, I am thinking that this is a security issue (ie. The
automated process does not think I have rights to run this package, but
recognizes me when I try to run it manually). I have tried other login IDs
in the "Owner" box (see step #6 above), but I always get the same result.
Can anyone tell me what I can do to get DTS jobs to run automatically?
Schoo
PS: Windows 2000 Server, running SQL 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided. since you got the email notification,
the job runs and you the notification is working fine. For the job to run
automatically, could you check the job schedule. You could right-click the
job, then choose 'properties'; In the 'Schedule' tab, you will notice the
schedule of how you job runs. If you want to change the schedule, you could
press the 'Edit' button. You could arrange a new schedule of the job, Then
the job will run automatically. You could confirm by right-click the job
and choose the 'View job history' and check the record of how the job runs.
For the 'Notification' button, you could check the 'Email operator', choose
one operator and select when to send the email notification.
Hope this helps. If you still have questions, please feel free to post your
message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for your response... perhaps I was not clear enough on what is
happening. All of the items you mention in regards to changing a schedule,
I am aware of. I am saying that the actual job starts, fails and I get an
email notifying me that the job fails (see original email in this string).
The issue is not how to run the job automatically or how to get it to email
me. The issue is that the DTS package will run fine if I open it up and
execute it, but not if I create a job and try to run it automatically. My
questions is: how can I get the DTS package (that runs manually without
errors) to run without errors automatically.
I hope this is more clear and also that you might shine some light on this
issue for us.
Scott
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Scott,
> Thank you for using the newsgroup and it is my pleasure to help you with
> your issue.
> From the information you provided. since you got the email notification,
> the job runs and you the notification is working fine. For the job to run
> automatically, could you check the job schedule. You could right-click the
> job, then choose 'properties'; In the 'Schedule' tab, you will notice the
> schedule of how you job runs. If you want to change the schedule, you
could
> press the 'Edit' button. You could arrange a new schedule of the job, Then
> the job will run automatically. You could confirm by right-click the job
> and choose the 'View job history' and check the record of how the job
runs.
> For the 'Notification' button, you could check the 'Email operator',
choose
> one operator and select when to send the email notification.
> Hope this helps. If you still have questions, please feel free to post
your
> message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||questions are
1) who is the owner of the job
if owner of job is member of sysadmins fixed serverrole then job will run
dts in job owner's context
if owner of the job is not member of sysadmins then job will attempt to run
in sqlproxyaccount's context(if sql proxy account is configured) if not you
will get a failure
Olu Adedeji
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OEcdtEm8DHA.4044@.tk2msftngp13.phx.gbl...
> Thank you for your response... perhaps I was not clear enough on what is
> happening. All of the items you mention in regards to changing a
schedule,
> I am aware of. I am saying that the actual job starts, fails and I get an
> email notifying me that the job fails (see original email in this string).
> The issue is not how to run the job automatically or how to get it to
email
> me. The issue is that the DTS package will run fine if I open it up and
> execute it, but not if I create a job and try to run it automatically. My
> questions is: how can I get the DTS package (that runs manually without
> errors) to run without errors automatically.
> I hope this is more clear and also that you might shine some light on this
> issue for us.
> Scott
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
run
the
the
> could
Then
> runs.
> choose
> your
rights.
>|||Hi Scott,
Thanks for your update and Olu's reply.
Besides Olu's question, could you run the DTS package in the design windows
of the package? Is it successful or any information? When the mail is send
to you, what is the job history? You could also run the following code in
you Query Analyzer and collect the information of this job.
select * from msdb..sysjobs
select * from msdb..sysjobsteps
select * from msdb..sysjobhistory
select * from msdb..sysnotifications
select * from msdb..sysjobservers
select * from msdb..sysjobschedules
I am waiting on your reply. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott:
How about everything going? Now I would add some more information on this
issue:
There is one good article for your reference:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Also, you could add the error file to capture the detailed error happened
when run the DTS package:
In the DTS package design window, choose from menu, 'Package'->'Properties'
and set the options in the 'Logging Tab'. You could refer to the DTS
Package Properties (Logging Tab) in the SQL Server Books Online.
Hope this helps. If you still have questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Automated DTS Package Problem

I need to create an Automated DTS package. At present I am running it manually each morning to import our phone switch data to an SQL table. The reason I had to go this route is this.

Because the Access tables are locked, I have a System DSN that connects to the System.mda file, this in turn gives me access to the locked tables to extract the data I need into Excel. I then drop the existing SQL table and run the DTS package recreating the SQL table with the data from the Excel file. I do not want to use OLEDB to connect to the Access table from within my application because it changes each month and of course the traffic load, I have as many 150 clients hitting the DB at one time.

I actually need the DTS package to update the SQL table with the latest data.

I'm no DTS guru, so how can I do this automagically?I managed to create the DTS package using my DSN, but I still have a few problems.

1. This is the Query that I need to use, it works every where else except insde the DTS Query Pane:

SELECT * FROM CallLog WHERE
(TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

It throws an OLEDB error telling me that GETDATE is an Undefined function.

2. I need to insert the above results into another table, this should work right?:

INSERT INTO SD_2004
SELECT * FROM CallLog
WHERE (TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

If I can overcome this Undefined Function bit, I got it whipped.

Automate Cube Processing

Hello,
Can some one tell me how i could automatically
refresh the cube when the DW is updated, I know its
possible through DTS, can someone refer me to a site which
gives step by step details on how i could accompalish this?
Regards
ImranYou need to have analysis services installed on the machine that you will
run the DTS package from, there is a task called process cube that you can
then schedule
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <anonymous@.discussions.microsoft.com> wrote in message
news:c78a01c40fcc$c8bef7c0$a001280a@.phx.gbl...
> Hello,
> Can some one tell me how i could automatically
> refresh the cube when the DW is updated, I know its
> possible through DTS, can someone refer me to a site which
> gives step by step details on how i could accompalish this?
> Regards
> Imran