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 replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don

removing replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don