Extract data from multiple oracle database

I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.

Use a For Each loop to iterate through the database connections. Use data flows inside the For Each loop to copy the data.

You might want to start with the SSIS tutorials in Books Online, if this is your first time using SSIS.

sql

EXtract data from 10 identical oracle database into one

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.

There is a table which list all the 10 databases.

You could do it all in one data flow task or many it's up to you. Basically, you'll need to create a connection manager for oracle and one for sql server. For the source define the table or query to extract the needed data from orcale and then connect it to the destination. Then just map the columns from source to target.

|||

JIGJAN wrote:

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.

There is a table which list all the 10 databases.

You can use a For Each loop to drive this process. Put an Execute SQL to retrieve the list of databases, and connect it to a For Each loop set to enumerate a rowset. In the For Each loop map a variable to the database name. Put an expression on the connection manager for the Oracle database, to create the connect string dynamically. Put a data flow (or multiple flows, depending on the number of tables) inside it, configured to copy the tables.

Expressions

Is there a good guide out there that I can reference for expressions?
Crystal's formula editor seems very strong in comparison, and frankly
if we can't find any decent documentation with good examples, we may
just ditch reporting services for Crystal.
We have several legacy reports in Crystal that have some lengthy
formulas. As we are a medical technology firm, we have many laws we
must follow, and our formulas have to take tons of things into
account.
Can anyone point me towards some decent resources?Hi Rick:
If the formulas and expressions are long and involved, you might want
to consider writing a custom assembly ( a dll written in a .NET
language, like VB.NET or C#).
You can reuse the logic in the assembly in other reports and even
other applications. The methods in the assembly can call each other.
It can open up the entire .NET framework for you, including all the
Math functions, the data structures like lists and hashtables, the
File IO operations, and so on. In Crystal you can't even get close to
this level of abstraction and functionality.
This might be a steeper learning curve compared to the function editor
in Crystal, but it is much more powerful.
If you google for [reporting services custom assembly] you'll see a
number of resources on the internet to help out.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On 15 Oct 2004 05:21:23 -0700, rickb@.wynpartners.com (RickB) wrote:
>Is there a good guide out there that I can reference for expressions?
>Crystal's formula editor seems very strong in comparison, and frankly
>if we can't find any decent documentation with good examples, we may
>just ditch reporting services for Crystal.
>We have several legacy reports in Crystal that have some lengthy
>formulas. As we are a medical technology firm, we have many laws we
>must follow, and our formulas have to take tons of things into
>account.
>Can anyone point me towards some decent resources?

Expression for calculating the different between two dates

i am currently doing a system where user can submit their suggestions to us
and part of my system is using Reporting Services and i need some guide on my
problem.
When user submit their suggestions, the date of submission will be auto
input into SQL Server database and if the suggestions is being evaluated by a
evaluator, the date of evaluation will also be input into the SQL Server
database and all this part i had done it all in aspx.
So now when user want to view their suggestions, they will go to a page
which i had generate using Reporting Services. In this page, there is a
column call Turnaround Time(the datediff from the day of submission to now
till the suggestions is being evaluated). That means In this column i wan to
display the datediff from the date of submission till the date today if it
still had not been evaluated(that means the SQL Server database that contains
the field "Date of Evaluation" is still null). And if the suggestions had
been evaluated(that means the SQL Server database that contains the field
"Date of Evaluation" contains a date value now), it will display the datediff
from the date of submission to the date of evaluation.
For now i can only display the datediff from the date of submission to now
using the following expression : =DateDiff(DateInterval.Day,
Fields!DateSubmitted.Value, Now()) and thus i need help on how to display the
datediff from the date of submission to now if the suggestions had not been
evaluated and display the datediff from the date of submission to the date of
evaluation if the suggestions had been evaluated.
I appreciate for the help and guide. ThanksYou can try this :
IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
SubmissionDate.Value)
>--Original Message--
>i am currently doing a system where user can submit their
suggestions to us
>and part of my system is using Reporting Services and i
need some guide on my
>problem.
>When user submit their suggestions, the date of
submission will be auto
>input into SQL Server database and if the suggestions is
being evaluated by a
>evaluator, the date of evaluation will also be input into
the SQL Server
>database and all this part i had done it all in aspx.
>So now when user want to view their suggestions, they
will go to a page
>which i had generate using Reporting Services. In this
page, there is a
>column call Turnaround Time(the datediff from the day of
submission to now
>till the suggestions is being evaluated). That means In
this column i wan to
>display the datediff from the date of submission till the
date today if it
>still had not been evaluated(that means the SQL Server
database that contains
>the field "Date of Evaluation" is still null). And if the
suggestions had
>been evaluated(that means the SQL Server database that
contains the field
>"Date of Evaluation" contains a date value now), it will
display the datediff
>from the date of submission to the date of evaluation.
>For now i can only display the datediff from the date of
submission to now
>using the following expression : =DateDiff
(DateInterval.Day,
>Fields!DateSubmitted.Value, Now()) and thus i need help
on how to display the
>datediff from the date of submission to now if the
suggestions had not been
>evaluated and display the datediff from the date of
submission to the date of
>evaluation if the suggestions had been evaluated.
>I appreciate for the help and guide. Thanks
>.
>|||you can directly get the Turnaround Time from database.
In your query get this column...
DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
TurnaroundTime
and use Fields!TurnaroundTime.Value in your report.
hth
"JiaN" <JiaN@.discussions.microsoft.com> wrote in message
news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
>i am currently doing a system where user can submit their suggestions to us
> and part of my system is using Reporting Services and i need some guide on
> my
> problem.
> When user submit their suggestions, the date of submission will be auto
> input into SQL Server database and if the suggestions is being evaluated
> by a
> evaluator, the date of evaluation will also be input into the SQL Server
> database and all this part i had done it all in aspx.
> So now when user want to view their suggestions, they will go to a page
> which i had generate using Reporting Services. In this page, there is a
> column call Turnaround Time(the datediff from the day of submission to now
> till the suggestions is being evaluated). That means In this column i wan
> to
> display the datediff from the date of submission till the date today if it
> still had not been evaluated(that means the SQL Server database that
> contains
> the field "Date of Evaluation" is still null). And if the suggestions had
> been evaluated(that means the SQL Server database that contains the field
> "Date of Evaluation" contains a date value now), it will display the
> datediff
> from the date of submission to the date of evaluation.
> For now i can only display the datediff from the date of submission to now
> using the following expression : =DateDiff(DateInterval.Day,
> Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> the
> datediff from the date of submission to now if the suggestions had not
> been
> evaluated and display the datediff from the date of submission to the date
> of
> evaluation if the suggestions had been evaluated.
> I appreciate for the help and guide. Thanks|||ok thanks for your help ravi but my datatype for EvaluationDate and
SubmissionDate is Date/Time and it shows a error when i preview using the
expression you had given me.
The error is : "The value expression for the textbox 'Turnaround Time'
contains and error: [BC30452] Operator '-' is not defined for types 'Date'
and Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields"
"Ravi" wrote:
> You can try this :
> IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
> SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
> SubmissionDate.Value)
>
> >--Original Message--
> >i am currently doing a system where user can submit their
> suggestions to us
> >and part of my system is using Reporting Services and i
> need some guide on my
> >problem.
> >
> >When user submit their suggestions, the date of
> submission will be auto
> >input into SQL Server database and if the suggestions is
> being evaluated by a
> >evaluator, the date of evaluation will also be input into
> the SQL Server
> >database and all this part i had done it all in aspx.
> >So now when user want to view their suggestions, they
> will go to a page
> >which i had generate using Reporting Services. In this
> page, there is a
> >column call Turnaround Time(the datediff from the day of
> submission to now
> >till the suggestions is being evaluated). That means In
> this column i wan to
> >display the datediff from the date of submission till the
> date today if it
> >still had not been evaluated(that means the SQL Server
> database that contains
> >the field "Date of Evaluation" is still null). And if the
> suggestions had
> >been evaluated(that means the SQL Server database that
> contains the field
> >"Date of Evaluation" contains a date value now), it will
> display the datediff
> >from the date of submission to the date of evaluation.
> >
> >For now i can only display the datediff from the date of
> submission to now
> >using the following expression : =DateDiff
> (DateInterval.Day,
> >Fields!DateSubmitted.Value, Now()) and thus i need help
> on how to display the
> >datediff from the date of submission to now if the
> suggestions had not been
> >evaluated and display the datediff from the date of
> submission to the date of
> >evaluation if the suggestions had been evaluated.
> >
> >I appreciate for the help and guide. Thanks
> >.
> >
>|||Its working!! Thanks avnrao!!
Ravi, thanks too! for spending the time in helping me. =)
"avnrao" wrote:
> you can directly get the Turnaround Time from database.
> In your query get this column...
> DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
> TurnaroundTime
> and use Fields!TurnaroundTime.Value in your report.
> hth
> "JiaN" <JiaN@.discussions.microsoft.com> wrote in message
> news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
> >i am currently doing a system where user can submit their suggestions to us
> > and part of my system is using Reporting Services and i need some guide on
> > my
> > problem.
> >
> > When user submit their suggestions, the date of submission will be auto
> > input into SQL Server database and if the suggestions is being evaluated
> > by a
> > evaluator, the date of evaluation will also be input into the SQL Server
> > database and all this part i had done it all in aspx.
> > So now when user want to view their suggestions, they will go to a page
> > which i had generate using Reporting Services. In this page, there is a
> > column call Turnaround Time(the datediff from the day of submission to now
> > till the suggestions is being evaluated). That means In this column i wan
> > to
> > display the datediff from the date of submission till the date today if it
> > still had not been evaluated(that means the SQL Server database that
> > contains
> > the field "Date of Evaluation" is still null). And if the suggestions had
> > been evaluated(that means the SQL Server database that contains the field
> > "Date of Evaluation" contains a date value now), it will display the
> > datediff
> > from the date of submission to the date of evaluation.
> >
> > For now i can only display the datediff from the date of submission to now
> > using the following expression : =DateDiff(DateInterval.Day,
> > Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> > the
> > datediff from the date of submission to now if the suggestions had not
> > been
> > evaluated and display the datediff from the date of submission to the date
> > of
> > evaluation if the suggestions had been evaluated.
> >
> > I appreciate for the help and guide. Thanks
>
>