How to rename a file in SSIS

I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor.

Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task Editor

HOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME???

File System Task Editor

Properties Value

Destination Connection
IsDestinationPathVariable True
DestinationVariable User::ArchivePath
OverWriteDestination True

General
Name Rename a file
Description File System Task

Operation
Operation Rename file

Source Connection
IsSourcePathVariable True
SourceVariable User::ImportFilePath

Thanks

There is more than one way to do it, the last link is run by DTS/SSIS experts you will find most of what you need. Hope this helps.

http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

http://msdn2.microsoft.com/en-us/library/ms140185.aspx

http://www.sqlis.com

How to remove ALL as a dimension member

Is there a way to remove the ALL member that Analysis Services adds to each dimension?

thanks.

In Analysis Services 2000 you can change AllLevel property of the dimension from Yes to No.

In AS2005 you to navigate to the highest level in the hierarchy in your dimension and for the corresponding attribute you change IsAggregatable property from True to False.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Ed!|||

Setting the IsAggregatable propery to True does remove the 'ALL' dimension member but now my application only receives the first member from the Dimensions Level. Is there another property that I should change in relation to this? I've messed around with some of them to no avail.

BTW I'm using AS 2005 and accessing the cube from VS2005 and the Dundas charting tool.

Any suggestions are welcome!

thx,

-Marilyn

|||

This could be limitation of the client tool accessing Analysis Services.

Try to browse your cube in the Cube Browser, you should see all members of your dimension.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

When setting the IsAggregate property to 'False' the browser only shows the first member value in the dimension. The strange thing is that the 'All' value is grayed out (which is what I want) and all the checkboxes are checked...yet I only get results for the first member.

Any thoughts on how I can disable the 'ALL' and yet still retrieve data on all of the member values that I have checked in the drop-down box?

thx,

-Marilyn

|||

What browser are you using?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ed,

I'm using IE V6.0 sp2 on windows xp pro sp2.

thx, Marilyn

|||

Sorry, I overlooked you using Dundas.

Try and take a look in properties of you chart control and see if you can skip the "All" level.

If you remove the "All" member from the hierarchy what is happening is that some other member becomes a default member.

The checkboxes you talk about are the way Dundas is presenting you the dimension structure. It is hard to say what logic it applies when you disable the "All" level.

The bottom line is: looks like there is little more you can do on the server side. try posting how you can tweak your chart to fit your requirements.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

I have a similar problem but relating specifically to Parent-Child dimensions. I wan't the ability to store data against the All member of the All level. I am not sure how to do this because the All member has no dimension key for me to use in fact table. So far I have created my own All level, which works OK except that I can now see two All levels. So now I would like to hide the default all level but I can't seem to do that on a Parent-Child dimension.

Any suggestions appreciated!

|||

I'm still having this same problem as well. It's really gotten to the point where I need to fix this. I have a demo on Friday a.m. and the customer will not like the fact that it's there!

I'm not sure what else to try. I've done everything that was suggested above.

if anyone has any additional thoughts on this please respond. I'm sure this is a problem that many would have come across.

thx,

-Marilyn

|||

I finally got past this problem. It was related to the 'level' I was using within my OLAP cube. Thanks for the suggestions I received.

-marilyn

|||Marilyn, what did you do to fix the problem?

How to remove ALL as a dimension member

Is there a way to remove the ALL member that Analysis Services adds to each dimension?

thanks.

In Analysis Services 2000 you can change AllLevel property of the dimension from Yes to No.

In AS2005 you to navigate to the highest level in the hierarchy in your dimension and for the corresponding attribute you change IsAggregatable property from True to False.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Ed!|||

Setting the IsAggregatable propery to True does remove the 'ALL' dimension member but now my application only receives the first member from the Dimensions Level. Is there another property that I should change in relation to this? I've messed around with some of them to no avail.

BTW I'm using AS 2005 and accessing the cube from VS2005 and the Dundas charting tool.

Any suggestions are welcome!

thx,

-Marilyn

|||

This could be limitation of the client tool accessing Analysis Services.

Try to browse your cube in the Cube Browser, you should see all members of your dimension.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

When setting the IsAggregate property to 'False' the browser only shows the first member value in the dimension. The strange thing is that the 'All' value is grayed out (which is what I want) and all the checkboxes are checked...yet I only get results for the first member.

Any thoughts on how I can disable the 'ALL' and yet still retrieve data on all of the member values that I have checked in the drop-down box?

thx,

-Marilyn

|||

What browser are you using?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ed,

I'm using IE V6.0 sp2 on windows xp pro sp2.

thx, Marilyn

|||

Sorry, I overlooked you using Dundas.

Try and take a look in properties of you chart control and see if you can skip the "All" level.

If you remove the "All" member from the hierarchy what is happening is that some other member becomes a default member.

The checkboxes you talk about are the way Dundas is presenting you the dimension structure. It is hard to say what logic it applies when you disable the "All" level.

The bottom line is: looks like there is little more you can do on the server side. try posting how you can tweak your chart to fit your requirements.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

I have a similar problem but relating specifically to Parent-Child dimensions. I wan't the ability to store data against the All member of the All level. I am not sure how to do this because the All member has no dimension key for me to use in fact table. So far I have created my own All level, which works OK except that I can now see two All levels. So now I would like to hide the default all level but I can't seem to do that on a Parent-Child dimension.

Any suggestions appreciated!

|||

I'm still having this same problem as well. It's really gotten to the point where I need to fix this. I have a demo on Friday a.m. and the customer will not like the fact that it's there!

I'm not sure what else to try. I've done everything that was suggested above.

if anyone has any additional thoughts on this please respond. I'm sure this is a problem that many would have come across.

thx,

-Marilyn

|||

I finally got past this problem. It was related to the 'level' I was using within my OLAP cube. Thanks for the suggestions I received.

-marilyn

|||Marilyn, what did you do to fix the problem?
sql

How to remove a previous Installed instance

I uninstall the complete SQL Server 2005, and then when I reinstall it,
Reporting Services don't want to install because a previous installed
instance is detected.Hi Robert,
Welcome to MSDN newsgroup.
From your description, you've uninstalled the existing SQL Server 2005 on
your server. However, when you try reinstalling it later, you get
error(during Reporting Service installation) indicate that there is a
previous installed instance, correct?
Based on my experience, when installing the SQL Server, setup program will
try detecting existing SQL Server instances through the windows registry.
So you can have a look in the registry to see whether the Reporting Service
entry is still there. For SQL 2005, it uses the MSSQL.X like naming
convention, e.g
on my test server, the SQL 2005 Reporting Service is associated with the
below key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4
In addition, before we performing registry editing, I suggest you still
have a look at the following SQL Server setup document and try the
commandline uninstallation (for specific named instance...) to see whether
it can help clearly remove the Reporting service instance.
#How to: Uninstall an Existing Instance of SQL Server 2005 (Setup)
http://msdn2.microsoft.com/en-us/library/ms143412.aspx
Hope helps. Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Roberto Hernández" <robertohh@.newsgroups.nospam>
| Subject: How to remove a previous Installed instance
| Date: Tue, 24 Jan 2006 12:30:59 -0600
| Lines: 5
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <e1y#uoRIGHA.3036@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 201.201.36.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67408
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I uninstall the complete SQL Server 2005, and then when I reinstall it,
| Reporting Services don't want to install because a previous installed
| instance is detected.
|
|
||||Thanks,
solved deleting the regedit key
And then I reinstall it and all works ok.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> escribió en el mensaje
news:VOdn65WIGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> Hi Robert,
> Welcome to MSDN newsgroup.
> From your description, you've uninstalled the existing SQL Server 2005 on
> your server. However, when you try reinstalling it later, you get
> error(during Reporting Service installation) indicate that there is a
> previous installed instance, correct?
> Based on my experience, when installing the SQL Server, setup program will
> try detecting existing SQL Server instances through the windows registry.
> So you can have a look in the registry to see whether the Reporting
> Service
> entry is still there. For SQL 2005, it uses the MSSQL.X like naming
> convention, e.g
> on my test server, the SQL 2005 Reporting Service is associated with the
> below key:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4
> In addition, before we performing registry editing, I suggest you still
> have a look at the following SQL Server setup document and try the
> commandline uninstallation (for specific named instance...) to see whether
> it can help clearly remove the Reporting service instance.
> #How to: Uninstall an Existing Instance of SQL Server 2005 (Setup)
> http://msdn2.microsoft.com/en-us/library/ms143412.aspx
> Hope helps. Thanks,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
> --
> | From: "Roberto Hernández" <robertohh@.newsgroups.nospam>
> | Subject: How to remove a previous Installed instance
> | Date: Tue, 24 Jan 2006 12:30:59 -0600
> | Lines: 5
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | Message-ID: <e1y#uoRIGHA.3036@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 201.201.36.70
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:67408
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I uninstall the complete SQL Server 2005, and then when I reinstall it,
> | Reporting Services don't want to install because a previous installed
> | instance is detected.
> |
> |
> |
>|||Thanks for your response Roberto,
Glad that we've resolved the problem.
Have a good day!
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--
| From: "Roberto Hernández" <robertohh@.newsgroups.nospam>
| References: <e1y#uoRIGHA.3036@.tk2msftngp13.phx.gbl>
<VOdn65WIGHA.3944@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: How to remove a previous Installed instance
| Date: Wed, 25 Jan 2006 14:32:42 -0600
| Lines: 72
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uk3xA6eIGHA.984@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 201.201.36.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67515
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Thanks,
| solved deleting the regedit key
| And then I reinstall it and all works ok.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> escribi?en el mensaje
| news:VOdn65WIGHA.3944@.TK2MSFTNGXA02.phx.gbl...
| > Hi Robert,
| >
| > Welcome to MSDN newsgroup.
| > From your description, you've uninstalled the existing SQL Server 2005
on
| > your server. However, when you try reinstalling it later, you get
| > error(during Reporting Service installation) indicate that there is a
| > previous installed instance, correct?
| >
| > Based on my experience, when installing the SQL Server, setup program
will
| > try detecting existing SQL Server instances through the windows
registry.
| > So you can have a look in the registry to see whether the Reporting
| > Service
| > entry is still there. For SQL 2005, it uses the MSSQL.X like naming
| > convention, e.g
| >
| > on my test server, the SQL 2005 Reporting Service is associated with the
| > below key:
| >
| > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4
| >
| > In addition, before we performing registry editing, I suggest you still
| > have a look at the following SQL Server setup document and try the
| > commandline uninstallation (for specific named instance...) to see
whether
| > it can help clearly remove the Reporting service instance.
| >
| > #How to: Uninstall an Existing Instance of SQL Server 2005 (Setup)
| > http://msdn2.microsoft.com/en-us/library/ms143412.aspx
| >
| > Hope helps. Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| > --
| > | From: "Roberto Hernández" <robertohh@.newsgroups.nospam>
| > | Subject: How to remove a previous Installed instance
| > | Date: Tue, 24 Jan 2006 12:30:59 -0600
| > | Lines: 5
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <e1y#uoRIGHA.3036@.tk2msftngp13.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: 201.201.36.70
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:67408
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | I uninstall the complete SQL Server 2005, and then when I reinstall
it,
| > | Reporting Services don't want to install because a previous installed
| > | instance is detected.
| > |
| > |
| > |
| >
|
|
|

How to Re-Install Reporting Services in SQL 2005?

I have removed Reporting Service from SQL2005.
Now, I want to install it again.
The setup does not give me this option.
What should I do to re-install reporting services again?
Thank youOn Mar 5, 12:41 pm, "leoh" <leonardomach...@.gmail.com> wrote:
> I have removed Reporting Service from SQL2005.
> Now, I want to install it again.
> The setup does not give me this option.
> What should I do to re-install reporting services again?
> Thank you
It sounds kind of like you were not able to completely uninstall SSRS.
I believe that there is a Microsoft utility that should be able to
remove the remanants of the original SSRS; however, I don't remember
the name of it. Sorry, I wish I could help you further.
Regards,
Enrique Martinez
Sr. SQL Server Developer

how to read database connection data from a properties file?

Hi guys,
I am very new to Reporting Services, so please bear with me here. I am
curious about how we can configure the database server, database name, and
login data using data from a properties file so that when Reporting Services
and the Report Server start up, they have all the data they need to connect
to the appropriate database. Has anyone tried this yet? If so how would it
be possible?
Thanks a bunch,
Carolyn
cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.comWhen you design reports you have the option of using shared data sources for
running the report. Or each report can use its own data source. I always use
the shared datasources. The shared data source is referred to when you
design the report. When you deploy the default is that the shared datasource
does not get written over. What this means is you can configure the shared
datasource on the server and it can be different than what the developers
are using. For the credentials for the shared datasource I use a specific
username. The db username I use is one I have setup for reporting that has
only read access to the database. An advantage of this is it allows
connection pooling since all the connections for reporting will use the
exact same connection string (a necessity for connection pooling).
Note that what I am talking about here is getting to the database which has
the data for the report. RS also has a database which is used for caching of
the reporting objects. This can be the same or different database from which
you are reporting on.
Bruce L-C
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:O3lMQ9BhEHA.556@.tk2msftngp13.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>

How to rank an union of two result sets?

I am new to SQL Server Integration Services. I need to help here.

If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it.

Does anyone know how to achieve this?

Thanks!

Dense_rank is not a SSIS function. If you want to use the DB engine, you could write the 2 sets of data to a staging table and then use an additional dataflow with a query that does the rank over the staging table.

Another option would be to look at Jamie's rank transformation:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

|||

Thanks for the info. Do you know where to get the Conchango Runk transform conponent?

|||

Jun Fan wrote:

Thanks for the info. Do you know where to get the Conchango Runk transform conponent?

At the bottom of that post, there is an "Attachment" link. Click on that to get it.