Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Using REPLACE Function with a variable

Author  Topic 

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-11-14 : 14:21:54
Hi all,
I need to remove special characters from file names.
I thought the REPLACE function work work but I'm having some doubts.

The variable strFileName contains the name of the file.

Replace("strFileName", "-", " ")

Replace(Dts.Variables("strFileName").Value, "-", " ")

Neither of these changes the value.
What am I missing, please?
Thanks, Jack

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 15:18:06
Replace returns a string, but you're not doing anything with it. have you tried (I didn't test this):

Dts.Variables("strFileName") = Dts.Variables("strFileName").Value.Replace("-"," ")
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-11-14 : 15:41:05
Thanks gbritton.

I got Error 'Property 'Item' is 'Readonly'
but the var strFileName is defined as a Read-write
also got warning Implicit conversion from Object to String

Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-11-18 : 09:24:55
Can anyone help me with this? My package loops thru file names and stores the name in a string.
I need to be able to REPLACE special chars in that string and that is where I'm stumped.

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 09:29:06
Note this article: http://msdn.microsoft.com/en-us/library/aa337079.aspx

and this paragraph:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts. Therefore you cannot directly increment the value of a package variable as you process each row of data. Increment the value of a local variable instead, and set the value of the package variable to the value of the local variable in the PostExecute method after all data has been processed. You can also use the VariableDispenser property to work around this limitation, as described later in this topic. However, writing directly to a package variable as each row is processed will negatively impact performance and increase the risk of locking conflicts.
Go to Top of Page
   

- Advertisement -