sql server dts: Substing and Len SSIS -- sql server dts

Substing and Len SSIS

dba
3/14/2007 2:25:05 PM

sql server dts: (DT_BOOL) SUBSTRING(@USER::_DataFile,(DT_I4)
LEN(@USER::_DataFile)-1,1))==0 ? 1:0

Let me state what I'm trying to do, I have to do a restore from a
backup, I want to append a 0 to the end of the data and log file
names, then when I do it the next day I want to append a 1, because it
takes about 12 hours for the restore to complete, so the database is
available until I'm done restoring, drop the old one and rename the
fresh restore. So I'm just trying to read in what I currently have, a
zero or a one, and then take the inverse. I'm putting this into it's
own variable and evaluating it as an expression. Any help would be
greatly appreciated.

Re: Substing and Len SSIS

Darren Green
3/18/2007 9:31:15 PM

So I mocked up a variable called StringVar with a value of -

C:\MyFile1.mdf

Using this expression -

SUBSTRING(@[User::StringVar], 1, LEN(@[User::StringVar])-5) +
(SUBSTRING(@[User::StringVar], LEN(@[User::StringVar]) - 4, 1) == "0" ? "1"
: "0") + RIGHT(@[User::StringVar], 4)

I get -

C:\MyFile0.mdf

If I change the variable value to -

C:\MyFile1.mdf

I get -

C:\MyFile0.mdf

Is that what you want?

I make some assumptions, most importantly that you have a 4 character
extension and the character preceeding that is the flag 0 or 1. This should
then work for MDF, NDF or LDF filenames.

--
Darren Green
http://www.konesans.com | http://www.sqlis.com | http://wiki.sqlis.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.