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

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
LijunHave a look at oSql in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

expression based on multiple values in a dataset

Hi
I am trying to seth the background colour of a textbox to either red or
green depending on the multiple values in my dataset.
Each row of my dataset has a boolean value for Pass. The background colour
of the text box needs to be green if all rows in my dataset have a Pass
value of True but if only one row in the dataset has a Pass value of False,
the background colour needs to be set to red.
I have tries the obvious expression =IIF(Fields!Pass.Value = True, "Green",
"Red") but this will set the background to green again if their is a row in
the dataset with a Pass value of True after the row which had the Pass value
of False.
I have also tried counting the number of rows with a specific value but this
does not seem to work.
=IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
Does anyone have any ideas how to get what I want?
Thanks
Lewis Holmes
eNateTry this for multiple values:
=iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"l.holmes" <enate@.newsgroups.nospam> wrote in message
news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Hi
> I am trying to seth the background colour of a textbox to either red or
> green depending on the multiple values in my dataset.
> Each row of my dataset has a boolean value for Pass. The background colour
> of the text box needs to be green if all rows in my dataset have a Pass
> value of True but if only one row in the dataset has a Pass value of
> False, the background colour needs to be set to red.
> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
> "Green", "Red") but this will set the background to green again if their
> is a row in the dataset with a Pass value of True after the row which had
> the Pass value of False.
> I have also tried counting the number of rows with a specific value but
> this does not seem to work.
> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
> Does anyone have any ideas how to get what I want?
> Thanks
> Lewis Holmes
> eNate
>|||Hi Robert
Thanks for the reply.
I do not think this will work still. For example say my dataset has three
rows which have Pass values of True, False and True.
As i understand, using this expression for the first row, the expression
will evaluate to Green as Value is True. Then for the second row the
expression will evaluate to Red as value is False (this is all correct).
However, my problem is that now after evaluating the expression for third
row, the value returned will be Green as Value is true and so sum returns 1.
This is not the behaviour I want as the background colour should be Red if
one or more of the Pass values is false.Using this expression, the result
from the third row is setting the background colour back to green.
Hope this explains the problem better.
Kind Regards
Lewis Holmes
eNate
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eO1NksR2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Try this for multiple values:
> =iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "l.holmes" <enate@.newsgroups.nospam> wrote in message
> news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> I am trying to seth the background colour of a textbox to either red or
>> green depending on the multiple values in my dataset.
>> Each row of my dataset has a boolean value for Pass. The background
>> colour of the text box needs to be green if all rows in my dataset have a
>> Pass value of True but if only one row in the dataset has a Pass value of
>> False, the background colour needs to be set to red.
>> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
>> "Green", "Red") but this will set the background to green again if their
>> is a row in the dataset with a Pass value of True after the row which had
>> the Pass value of False.
>> I have also tried counting the number of rows with a specific value but
>> this does not seem to work.
>> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
>> Does anyone have any ideas how to get what I want?
>> Thanks
>> Lewis Holmes
>> eNate
>|||Hi Lewis,
In you case, I understood if there is one backgroup to be Red (false), you
want all following backgroup to be shown as Red (false). If I have
misunderstood your concern, please feel free to point it out.
I am afraid there won't be an easy to accomplish this. You may try using
.net assembly to identify the color according to row number with custom
function.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

exporting to pdf

When we export a report as a PDF and if there are multiple columns in the
report, all the columns in the report do not fit in one single page in the
exported PDF. We would ideally like to accommodate all the columns within one
single page. The only way we could do this today is to reduce the width of
the columns , but this causes the Report to loose its clarity and readability.
Thanks in advance,Go to the report menu item and open "report properties". On the "layout"
tab, increase the "page width" property accordingly. Re-deploy the report,
run and export.
"Chandra" wrote:
> When we export a report as a PDF and if there are multiple columns in the
> report, all the columns in the report do not fit in one single page in the
> exported PDF. We would ideally like to accommodate all the columns within one
> single page. The only way we could do this today is to reduce the width of
> the columns , but this causes the Report to loose its clarity and readability.
> Thanks in advance,
>

Exporting to multiple sheets in Excel has limitations ?

guys, I am exporting a report in excel that generates in different
sheets.
It seems that only 3 tab or sheets can be generated.
If the report exceeds 3 sheets all the rest of the sheets combined in
the last sheet.
Can anyone confirm this? Does it have any work around or I am just
doing something wrong?
thanks in advanceHi, I am doing some testing on sending data to Excel myself. I just tested
this and was able to make 5 sheets when I made page brakes between rectagles
in the report. Have you used the device setting RemoveSpace when rendering
to Excel ?
"jhun_garma@.hotmail.com" wrote:
> guys, I am exporting a report in excel that generates in different
> sheets.
> It seems that only 3 tab or sheets can be generated.
> If the report exceeds 3 sheets all the rest of the sheets combined in
> the last sheet.
> Can anyone confirm this? Does it have any work around or I am just
> doing something wrong?
>
> thanks in advance
>

Exporting to multiple files from a single source table using DTS

Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.Can't you split up your export by using views with restrictions on your recordtype instead of your table?|||manthenasri, does DTS allow you to redirect output on the fly? From what I have seen the source and destination is fixed at design time.

Have you tried BCP with a query? You could use DTS to script your BCP commands to a CMD file. Then at the cmd prompt you could use DTSRUN to build the CMD file and then execute the CMD file. Or just set up a procedure to script the BCP commands and use xp_cmdshell.

Sort of bassackwards but it's the best I could come up with.|||Hello Paul

I do not know much about BCP. But here is the problem that i have to solve.

I have a table by name T1 which has the following data

Business Unit Name SSN RATE
1 Joe 923456789 40
1 Jenny 568234569 50
3 Mike 234198634 35
3 Meri 743579374 45

Now i need to create a two files by name 1.ben and 3.ben on the fly.
and them put in the data related to Joe and Jenny into 1.ben and the data for Mike and Meri into 3.ben. If there is more data related to other business units i need to create those files also.

Can you please tell me how i can accomplish using BCP.

Thanks,
Srinivas.|||I think the BCP command you want is...

bcp "select * from <dbname>.<owner>.<table> where <where clause>" queryout <file name> -c -T -S <server>

probably could wrap it all up like this:
create table manthenasri([Business Unit] int, [Name] varchar(15), SSN varchar(9), Rate int)
insert into manthenasri values(1,'Joe','923456789',40)
insert into manthenasri values(1,'Jenny','568234569',50)
insert into manthenasri values(3,'Mike','234198634',35)
insert into manthenasri values(3,'Meri','743579374',45)

declare @.bu int, @.TSQL varchar(255)
select @.bu = min([Business Unit]) from manthenasri
while @.bu is not null begin
set @.TSQL = 'bcp "select * from ' + db_name() + '..' + 'manthenasri where [Business Unit] = ' + cast(@.bu as varchar) + '" queryout ' + cast(@.bu as varchar) + '.ben -c -T -S ' + @.@.servername
print @.TSQL
-- master.dbo.xp_cmdshell(@.TSQL)
select @.bu = min([Business Unit]) from manthenasri where [Business Unit] > @.bu
end

Open Query Analyzer and run the above code. It should produce a few BCP commands. Make adjustments for your table and rerun the script. Next try cutting and pasting the results to a CMD prompt and see how it works. Once that's done you can un-comment the xp_cmdshell call and try running this on your server.

THIS IS UNTESTED CODE!

Look this over and post back with questions.|||Hi Srinivas ,

Use DTS designer add one data pump task for each condition and output file.

For example : Use select statement as the source of data pump task with where clause and desination to the the disired out put file.

cheers :)

Shaji

Originally posted by manthenasri
Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.|||Hai Paul


Thank you for the BCP solution that you have given. I really appreciate you patience in giving me a sample program also.

The sample worked just fine except for the brackets enclosing the variable @.Tsql. I think these brackets should not be there. I had to remove these brackets.

But now have a problem of reading from multiple files and posting that data into table. I have to do an isert and also an update on the table. How can i do that?

Thank you very much once again.

Srinivas.

Exporting to Multiple Excel worksheets

I am having trouble exporting my SRS data to multiple Excel worksheets.
I have four tables. I want each of these tables to appear on a
different worksheet even if there is no data for it. For one of my
queries, there was only data for 3 of these tables. First I selected
"Insert a Page Break after this table" for all of my Table properties.
When I exported this output to Excel, it only showed me 3 worksheets.
But I want it to always show me all four worksheets even if there is no
data for that table. And I believe it should still show me something
on this 4th worksheet because in SRS on each table's property I entered
in "No Data Available" for the NoRows table property. Instead what it
does is the Excel file includes this 4th table on the same worksheet as
one of the other tables. And I clearly see the text "No Data
Available", but it's not on its own page.
So again, how do I automatically have all 4 tables show up on 4
different tabs (worksheets).Try selecting "Insert a Page Break Before this table" for your 4th
table and see what happens.
Mike|||I tried this, but this didn't fix it either.
"Bassist695" wrote:
> Try selecting "Insert a Page Break Before this table" for your 4th
> table and see what happens.
> Mike
>|||Try to use GROUP in the layout design. Each group will be distributed as
individual worksheet accordingly.
ironryan77@.gmail.com wrote:
>I am having trouble exporting my SRS data to multiple Excel worksheets.
> I have four tables. I want each of these tables to appear on a
>different worksheet even if there is no data for it. For one of my
>queries, there was only data for 3 of these tables. First I selected
>"Insert a Page Break after this table" for all of my Table properties.
>When I exported this output to Excel, it only showed me 3 worksheets.
>But I want it to always show me all four worksheets even if there is no
>data for that table. And I believe it should still show me something
>on this 4th worksheet because in SRS on each table's property I entered
>in "No Data Available" for the NoRows table property. Instead what it
>does is the Excel file includes this 4th table on the same worksheet as
>one of the other tables. And I clearly see the text "No Data
>Available", but it's not on its own page.
>So again, how do I automatically have all 4 tables show up on 4
>different tabs (worksheets).
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200603/1|||Just return a space character on any of the table fields so that RS will
thing that the table needs to be printed on the fourth sheet as well. If you
have empty it wont return but just insert a space if no records are
available.
Amarnath.
"ironryan77@.gmail.com" wrote:
> I am having trouble exporting my SRS data to multiple Excel worksheets.
> I have four tables. I want each of these tables to appear on a
> different worksheet even if there is no data for it. For one of my
> queries, there was only data for 3 of these tables. First I selected
> "Insert a Page Break after this table" for all of my Table properties.
> When I exported this output to Excel, it only showed me 3 worksheets.
> But I want it to always show me all four worksheets even if there is no
> data for that table. And I believe it should still show me something
> on this 4th worksheet because in SRS on each table's property I entered
> in "No Data Available" for the NoRows table property. Instead what it
> does is the Excel file includes this 4th table on the same worksheet as
> one of the other tables. And I clearly see the text "No Data
> Available", but it's not on its own page.
> So again, how do I automatically have all 4 tables show up on 4
> different tabs (worksheets).
>|||I have tried all of the above suggestions, but none of them work. Have any
of you ever tried to do what I'm doing? Regarding Amarnath's response, it
would not be easy for me to insert a space since I am filtering the data from
one table. In other words, my SP selects data into one table which is then
filtered in SRS based on the grouping. Unless there is a way to form an
expression so that it inserts a space. Is this possible?
Regarding Frog's post, I created a group for this table and set the filter,
but this did not work either. And I have tried adding header and footer
where all of the footers contain the text "End of Record", but for this one
table with no data in it, neither header nor footer display. Only the text I
enter into the NoRows property displays. If I remove the text from NoRows
then there is a big space on that part of the worksheet, but I still only
have 3 total worksheets.
"Amarnath" wrote:
> Just return a space character on any of the table fields so that RS will
> thing that the table needs to be printed on the fourth sheet as well. If you
> have empty it wont return but just insert a space if no records are
> available.
> Amarnath.
> "ironryan77@.gmail.com" wrote:
> > I am having trouble exporting my SRS data to multiple Excel worksheets.
> > I have four tables. I want each of these tables to appear on a
> > different worksheet even if there is no data for it. For one of my
> > queries, there was only data for 3 of these tables. First I selected
> > "Insert a Page Break after this table" for all of my Table properties.
> >
> > When I exported this output to Excel, it only showed me 3 worksheets.
> > But I want it to always show me all four worksheets even if there is no
> > data for that table. And I believe it should still show me something
> > on this 4th worksheet because in SRS on each table's property I entered
> > in "No Data Available" for the NoRows table property. Instead what it
> > does is the Excel file includes this 4th table on the same worksheet as
> > one of the other tables. And I clearly see the text "No Data
> > Available", but it's not on its own page.
> >
> > So again, how do I automatically have all 4 tables show up on 4
> > different tabs (worksheets).
> >
> >