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

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...

Rename Database

I have a custom TFSBuild task that restores a backup of our database, runs some scripts against it, deletes the previous day's database, and the renames the new database to the same name as the one that was deleted. This has been working fine for weeks now, but started failing yesterday and also failed again today. The old database is deleted successfully, but the rename command fails.

The code that runs is:

Dim strRenameScript As String = String.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", _strTempDatabaseName) & Environment.NewLine & _
"GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} MODIFY NAME={1}", _strTempDatabaseName, _strDatabaseName) & _
Environment.NewLine & "GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} SET MULTI_USER", _strDatabaseName) & _
Environment.NewLine & "GO"

Executed using the Microsoft.SqlServer.Management.Smo classes, which is interpreted as:

ALTER DATABASE MyTempDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MyTempDatabase MODIFY NAME=MyLiveDatabase
GO
ALTER DATABASE MyLiveDatabase SET MULTI_USER
GO

This used to be fine, but now the batch fails after setting the database into single user mode, and the only error returned is:

An exception occurred while executing a Transact-SQL statement or batch

If I subsequently run the above script in SQLSMS it works fine. Does anyone know why this might have started failing all of a sudden?

Could you run the code below and post back the results?

Chris

SELECT *
FROM master.sys.databases
WHERE [name] IN ('MyTempDatabase', 'MyLiveDatabase')

|||

from which database context u r executing this statement... be sure that u r not connected to this particular database, in that case the connection is already broken by this statement

Madhu

|||

Madhu:
I'm connecting to the master database both when deleting the old database and when renaming the new database:

Dim cnTempDatabase As SqlConnection = New SqlConnection(String.Format(_strConnectionString, _strDatabaseServer, "Master"))
Dim sqlServer As Server = New Server(New ServerConnection(cnTempDatabase))

Chris:
I've manually renamed the database now so daily development can continue, but I get one result back for the 'live' database (what the temp one was renamed to).

I'll see if it fails again in the morning and if so I'll run the same query before doing anything and post back.

Would the error have logged any more details in the SQL Server log?

|||

OK, the statement failed again last night so I ran your query again. As expected, only the temp database (the one left in single user mode) is returned.

Would a more verbose error have been logged anywhere?

|||

if the error is not logged in SQL Server Event Log....most probably it means that the error is not occuring at the SQL Level but the Application level... capture the error from application or application log

Madhu

|||

There are lots of informational messages in the log about restoring the database, setting the old database to single user mode etc (although I note nothing about deleting it) and then the last entry is the about setting the temp database to single user mode (which happens just before the rename command):

2007-03-09 06:16:42.68 spid55 Setting database option SINGLE_USER to ON for database HighwayP2Temp20070309.

Then nothing. Yesterday (when the rename worked) there also wasn't any log entry about deleting the old database, so I guess that isn't an indicator.

Any ideas?

|||

Found more info:

If I use the full backup of our database (~70Gb) then the rename fails, unless I have restarted the SQL Server instance at some point in the day beforehand. If I use the shrunk version of our database (~1.5Gb) then the rename is always fine. Perhaps this is something to do with SQL Server memory usage? Is anyone aware of any existing issues regarding memory utilisation that may affect a rename action?

Rename Database

I have a custom TFSBuild task that restores a backup of our database, runs some scripts against it, deletes the previous day's database, and the renames the new database to the same name as the one that was deleted. This has been working fine for weeks now, but started failing yesterday and also failed again today. The old database is deleted successfully, but the rename command fails.

The code that runs is:

Dim strRenameScript AsString = String.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", _strTempDatabaseName) & Environment.NewLine & _
"GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} MODIFY NAME={1}", _strTempDatabaseName, _strDatabaseName) & _
Environment.NewLine & "GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} SET MULTI_USER", _strDatabaseName) & _
Environment.NewLine & "GO"

Executed using the Microsoft.SqlServer.Management.Smo classes, which is interpreted as:

ALTERDATABASE MyTempDatabase SET SINGLE_USER WITHROLLBACK IMMEDIATE
GO
ALTERDATABASE MyTempDatabase MODIFY NAME=MyLiveDatabase
GO
ALTERDATABASE MyLiveDatabase SET MULTI_USER
GO

This used to be fine, but now the batch fails after setting the database into single user mode, and the only error returned is:

An exception occurred while executing a Transact-SQL statement or batch

If I subsequently run the above script in SQLSMS it works fine. Does anyone know why this might have started failing all of a sudden?

Could you run the code below and post back the results?

Chris

SELECT *
FROM master.sys.databases
WHERE [name] IN ('MyTempDatabase', 'MyLiveDatabase')

|||

from which database context u r executing this statement... be sure that u r not connected to this particular database, in that case the connection is already broken by this statement

Madhu

|||

Madhu:
I'm connecting to the master database both when deleting the old database and when renaming the new database:

Dim cnTempDatabase As SqlConnection = New SqlConnection(String.Format(_strConnectionString, _strDatabaseServer, "Master"))
Dim sqlServer As Server = New Server(New ServerConnection(cnTempDatabase))

Chris:
I've manually renamed the database now so daily development can continue, but I get one result back for the 'live' database (what the temp one was renamed to).

I'll see if it fails again in the morning and if so I'll run the same query before doing anything and post back.

Would the error have logged any more details in the SQL Server log?

|||

OK, the statement failed again last night so I ran your query again. As expected, only the temp database (the one left in single user mode) is returned.

Would a more verbose error have been logged anywhere?

|||

if the error is not logged in SQL Server Event Log....most probably it means that the error is not occuring at the SQL Level but the Application level... capture the error from application or application log

Madhu

|||

There are lots of informational messages in the log about restoring the database, setting the old database to single user mode etc (although I note nothing about deleting it) and then the last entry is the about setting the temp database to single user mode (which happens just before the rename command):

2007-03-09 06:16:42.68 spid55 Setting database option SINGLE_USER to ON for database HighwayP2Temp20070309.

Then nothing. Yesterday (when the rename worked) there also wasn't any log entry about deleting the old database, so I guess that isn't an indicator.

Any ideas?

|||

Found more info:

If I use the full backup of our database (~70Gb) then the rename fails, unless I have restarted the SQL Server instance at some point in the day beforehand. If I use the shrunk version of our database (~1.5Gb) then the rename is always fine. Perhaps this is something to do with SQL Server memory usage? Is anyone aware of any existing issues regarding memory utilisation that may affect a rename action?

Removing Weekends?

Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.

I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?

Determing the Datenumber of the Week depends on the user settings (session settings) of Datefirst, you could also check for the DATEPARTed full day, but this is not language independent, see the following snippet to see how to use the query.

SET Datefirst 1
SELECT 'This is a workday' WHERE Datepart(dw,GETDATE()) < 6 -- 6 is Saturday.

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||

Also consider using a calendar table. Joining on the date you can eliminate weekends, holidays, whatever.

Here is an article I wrote that loads a calendar table:

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||The next thing they will ask is to remove holidays, so you should just go ahead and do the calendar file.

My method creates a calendar file with all holidays and Sat/Sun for the year that are NOT holidays. Then this is easy to do in a report:

SET @.calendardays = DATEADD(@.date,-1*(SELECT COUNT(*) FROM HOLIDAYS WHERE HolidayDate >= @.date AND HolidayDate <= @.date),d)

This works well for short periods, but not if you want to subtract weekends between 1/1/1923 and 10/24/2006. Then you need to program it.