Renaming a system (host) after MSDE is installed

Should this be an issue?
My system was working fine before I renamed my system (host), but since
I have renamed it, my application is working from a remote system. From
the local system (the machine where the MSDE is running) the app is
working fine.
Any ideas?
- manzoor
With MSDE 2000, if you rename the host then the name of the SQL Server will
follow the name of the host. Make sure your remote client is connection to
the new name.
Jim
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105393521.649255.134690@.c13g2000cwb.googlegr oups.com...
> Should this be an issue?
> My system was working fine before I renamed my system (host), but since
> I have renamed it, my application is working from a remote system. From
> the local system (the machine where the MSDE is running) the app is
> working fine.
> Any ideas?
> - manzoor
>
|||Yeap, it's working fine now. Even though I am not doing anything
different. I had expected teh new name to be required and was trying to
use it initially. Maybe it has something to do with using DHCP, but I'm
not sure.
Anyway, I realy do appreciate your reposnse.
- manzoor

Renaming a file with File System task

I'm having trouble working this out in SSIS. I am trying to use a File System task to rename a file using an expression so that file.zip will be renamed to filemmyy.zip at the end of every month (for instance this month would be file0506.zip).

I am using the destination expression variable. But I'm not sure what to put for DestinationConnection. It seems to want a file name, but the file name is going to be variable, so I'm not sure what to put.

Any ideas?

If you change the IsDestinationPathVariable to be True, then DestinationConnection expects a variable. Select the variable created previously that has the expression on it.|||

What would the variable be called? Under the expressions part of the File System Task editor I have Destination and the expression is "TestExpression.txt".

So would my variable be User::Destination, or something else?

sql

Renaming a File to include Date and Time

Hi

I am trying to move a flat file to an archive folder and then rename the file to include the date and time of runtime.

I have set up a File System Task to move the file which works fine and a second File System Task to rename. The IsDestinationPathVariable is set to TRUE and the DestinationVariable is called User::Error_Log_File_Rename. There is an expression as follows:

@.[User::Error_Log_File_Rename] = "Y:\\SSIS_PUD_Upload\\Error_Log_Archive\\Update_Error_Messages - " +REPLACE((DT_WSTR, 30)(DT_DBDATE)GETDATE() + "-" + (DT_WSTR, 30)(DT_DBTIME)GETDATE(),":","-") +".txt"

which is the required file name.

If I go into the expression and evaluate it it will give me the current date and time. However when I run it as part of the package say 10 minutes later the file is renamed using the same date and time from 10 minutes before when I evaluated the expression. If I wait another 10 minutes and run the package again it still retains the original date and time.

Is there a way to get the expression evaluated with the current date and time during execution of the package?

Thanks in advance

Scott

Are you using the expression section of the properties for the variable, User::Error_Log_File_Rename? And you have EvaluateAsExpression set to true?|||

Hi Phil

I have the variable in the expression section in the properties but I cannot see a 'EvaluateAsExpression' setting that I can set to true?

Where would i see this?

Thanks

Scott

|||

Racsco wrote:

Hi Phil

I have the variable in the expression section in the properties but I cannot see a 'EvaluateAsExpression' setting that I can set to true?

Where would i see this?

Thanks

Scott

Scott,

Select your variable in the variables panel and press F4. This will bring up the properties panel, displaying the properties of the variable. One of the properties is called EvaluateAsExpression. Set it to TRUE.

-Jamie

rename instance possible?

Hi,
is a rename of a instance possible?

In the registry I found this service:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$WIEN
mit
Display Name REG_SZ SQL Server (WIEN)
ImagePath REG_EXPAND_SZ "C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Binn\sqlservr.exe" -sWIEN

In Enum, Linkage, Performance I found "WIEN" to.

Is the name "WIEN" burnt in in the database too? Or can I rename the entires "WIEN" only in the service (and restart the service) and the instance had a new name?

Well, officially not, you will have to do a reinstallation. You might try this here, but i guess this i not supported nor recommended.

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/544c4eaf43ddfaf3/e9065e05718e984e

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Definitely not supported.

Mike

|||You cant do this, Since i have tried sp_dropserver and sp_addserver commands but even its not possible to change the name. The only thing is to re-install.

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an

expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"
|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?
|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.
|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"
|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?
|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.
|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...sql

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an

expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an

expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...

Rename file using File System Task Editor

Could someone please instruct me on how to use the File System Task Editor to rename a file? I place control on control flow tab, change the operation to rename, from there I am not sure what to do.

Create two package variables called FileSource and FileDestination.

Assign the path+existing filename to User::FileSource Variable and assign the path+newfilename to User::FileDestination variable.

In the FileSystemTask properties -
Set Operation to 'Rename File'
Set 'IsSDestinationPathVariable' to True and select 'User::FileDestination' variable for 'DestinationVariable' Parameter.
Set 'IsSourcePathVariable' to True and select 'User::FileSource' variable for 'Sourcevariable' parameter.

When you execute this task - you will find that the source file is renamed as destination file.

Thanks,
Loonysan

|||Please post the exact syntax the destination variable.
I am challenged by something like:
"\\ServerName\DirectoryName\"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+"_Myfile.txt"
|||IanO, Are you using EvaluateAsExpression == True and using the above as an expression instead of the variable value?|||

A slight aside, but if you are writing this file as part of the SSIS package, and just want to create a date stamp named file, then use the expression on the connection string of your flat file connection, and save the extra step of renaming, just create the file with the correct name to start with.

Why are you challenged, the expression itself looked good.

|||Hi Darren, I am trying to do the same thing as you just described, but when I use an expression like:

"C:\Test\Export\CustomFileNamePrefix"+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt"

and place it into the ConnectionString property of my existing Flat File Connector, it errors out when run with the statement that the file name was not valid, even though the directory itself exists and is perfectly valid...

I get similar messages when placing that expression into a variable string (in a File System Task), and setting it to EvaluateAsExpression == True.

What am I missing here please?
|||Thanks for your reply, Phil.
Gets or sets a Boolean that indicates that the variable contains an expression.
That is a nice feature however I'm still looking for a place in one of the dialogs to use it. The examples show its use in code.
|||Thanks for your reply, Darren. My challenge is that when I specify new file name, in the connector, it wants to validate that the file already exists. Furthermore, it wants to see columns before I can click OK. So, how do I tell it to give me a connection but not check it at design time? Hasn't this happened to one of you?

Thanks again,
IanO
|||Often you need to use a resource that does not yet exist, be that a file or table. Generally you do need to create the object to help develop the package, but after that you can set the DelayValidation property to prevent errors at run-time. This means that the task does not validate until immediatly prior to executing, rather than at the begining of the overall package execution as well. It is of course assumed that by the time validation does take place any dependencies do then exist.|||I am sorry to ask but where do you create thise package variables?|||

Sugo wrote:

I am sorry to ask but where do you create thise package variables?

In the variables window. View->Other Windows->Variables|||

Thanks for the help,

I am trying to do a similar task to what eveyone else is doing, basically I created a SSIS by going through the data export wizard where the data I selected gets exported to a flat file.

My package has a data export task that goes to a flat file export task. Based on what Darren spoke of I also created an expression for the default export file name ""DestinationConnectionFlatFile. Then I edited the Connectionstring in expressions and found that none of the sames would work in the posting becuase of the \ escaping issues. I basically used \\\\servers\\share\\filename+DATEPART("yyyy",GETDATE())+DATEPART("mm",GETDATE())+DATEPART("dd",GETDATE())+".txt to see if this would work for me. I am getting the below error stating that I need to cast the last part of the modification where the date is being added on becuase of data type mismatches.

Can someone show me how the expressions should be configured or cast correctly? If I can get one of these working I can get the other date elements coded that I want to use.

Expression:

"\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE()) +".txt"

Error:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""\\\\ServerName\\Share\\filename" + cast(DATEPART("yyyy",GETDATE()) as varchar(100)) +".txt"
" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

Error w/o the cast:

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.867&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""\\\\ServerName\\Share\\filename" + DATEPART("yyyy",GETDATE())" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

Thanks,
Wayne

|||

valid Expression:

"\\\\ServerName\\Share\\filename" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) +".txt"

Datepart returns a number. You can't concatenate a number to a string. Instead, you need to cast the number to a string. I have done so for you in the example.|||

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

|||

Sugo wrote:

Not sure if this is the correct way but it seems to work ok.

"\\\\ServerName\\Share\\filename" + "_" + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE()))

Either way would work...

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:
sp_configure 'allow updates', 1
go
reconfigure with override
go
DROP TABLE aonflict_SiacDataEEC_security_info
...
sp_configure 'allow updates', 0
go
reconfigure with override
go
Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Maria,
most system metadata is removed by sp_removedbreplication, but this is an
exception and it is common practice to drop these tables manually as part of
a cleanup.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the database is no longer replicating then it will not damage the
database to delete the old replication objects.
In fact, if you do not delete the replication triggers you will
probably encounter problems.
|||I agree. sp_MSdroparticletriggers is called from sp_removedbreplication but
has been known to not work in rare circumstances. I have a script which
removed redundant duplicate triggers that may be of interest in these cases:
http://www.replicationanswers.com/Script8.asp
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:

sp_configure 'allow updates', 1
go
reconfigure with override
go

DROP TABLE aonflict_SiacDataEEC_security_info
...

sp_configure 'allow updates', 0
go
reconfigure with override
go

Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.

Thanks a lot for your help.

*** Sent via Developersdex http://www.developersdex.com ***You might get a better response in
microsoft.public.sqlserver.replication

Simon

removing system administrators from fixed server role

Hello all,

On Analysis Services 2005 the members of the Administrators local group are also members of the fixed server role, therefore they have full control over Analysis Services databases.

I think this can be a problem becouse many system administrators don't need full control over AS. Does someone kwon how can I remove those high privileges to the local administrator?

Thank you.

Hernan.

In Management Studio right click on the server, select properties, turn on the "Show Advanced (All) Properties" option and scroll down to the bottom and set the "Security\BuiltInAdminsAreServerAdmins" property to false.|||

Thank you Darren, your post is very usefull.

Before create this thread I tried some searches in google, microsoft and BOL... I didn't get any importart results. I would like to known how you learned this.. maybe if you used a web serach... what words did you use?

|||Sorry, I can't remember. I am a bit of an SSAS junkie and it's just one of thost things I knew.

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of job
s
running on this server, so what priviledges does i need to grant them to all
o
to see the results of this jobs?496371c0860" target="_blank">http://groups.google.de/group/micro.../>
496371c0860
http://groups.google.de/group/micro...853a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:

> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want t
o
> take them off, but the people said me that they need to monitor a lot of j
obs
> running on this server, so what priviledges does i need to grant them to a
llo
> to see the results of this jobs?
>

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of jobs
running on this server, so what priviledges does i need to grant them to allo
to see the results of this jobs?
http://groups.google.de/group/micros...9c4496371c0860
http://groups.google.de/group/micros...53a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:

> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
> take them off, but the people said me that they need to monitor a lot of jobs
> running on this server, so what priviledges does i need to grant them to allo
> to see the results of this jobs?
>

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of jobs
running on this server, so what priviledges does i need to grant them to allo
to see the results of this jobs?http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/795ec20703b7b535/919c4496371c0860?tvc=1&q=jobs+Jens+S%C3%BC%C3%9Fmeyer&hl=de#919c4496371c0860
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_frm/thread/1d0ffa0f2b2fbdcd/0853a5d26aa1b4e1?lnk=st&q=jobs+Jens+S%C3%BC%C3%9Fmeyer&rnum=6&hl=de#0853a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:
> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
> take them off, but the people said me that they need to monitor a lot of jobs
> running on this server, so what priviledges does i need to grant them to allo
> to see the results of this jobs?
>

Removing Databases

Using SQL Server Management Studio, I see 4 entries, master, model, msdb and temdb, under Databases -> System Databases. Which of these can I safely remove, if any?

Thank you

Hi there,

I don't think you should remove any of them....Each of the system databases is there for a purpose and removing one or more will adversely affect your database server (possibly to the point of inoperability).

Below is a link to adescription of the purpose of each system database:
System Databases

Hope that helps a bit or sheds some light on the matter, but sorry if it doesn't
|||Why should one delete the system databases ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Nate,

Thank you for the reponse, it helped quite a bit. I am new to SQL Express and I wasn't sure what those Db's were for, so the link is very helpful.