Need help with conversion -- string conversion

Question

Post reply

Need help with conversion -- string conversion

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
December 5, 2009 at 1:43 pm

#137409

Hi All,
I need some help in conversion in my SSIS package.
I have two variables defined
1. user::scriptfolder ---> value is G:\SSISTest\Test\Failed\
2. user::filename --> this is dynamic --> meaning at runtime it will be
G:\SSISTest\Test\Files\test2.sql
G:\SSISTest\Test\Files\test3.sql
G:\SSISTest\Test\Files\update.sql
G:\SSISTest\Test\Files\thisisdelete_03.sql
I want to concatenate those variables and have the final output as
G:\SSISTest\Test\Failed\test2.sql
G:\SSISTest\Test\Failed\test3.sql
G:\SSISTest\Test\Failed\update.sql
G:\SSISTest\Test\Failed\thisisdelete_03.sql
I know that this can be done by using string functions in SSIS.
Any help is appreciated .
thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

SQL_Quest-825434 (12/5/2009)
I know that this can be done by using string functions in SSIS.

... not really.

Why not relly on concatenate? you know: +

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

If you have some idea of the path that will come in, you can search through it with Charindex to find the last "\" and then use substring to get the file name. Then as Paul mentioned, use + to concatenate the substring with your variable.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

Perfect paul and steve !

After posting this I was playing with SSIS string function and I was able to figure out:

@[User::ScriptFolder] + RTRIM( SUBSTRING (@[User::filename],31,40) )

Thanks for help,

\\K 😀

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Also, an FYI for next time: SSIS questions will get better response in the Integration Services forum.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 5

RBarryYoung (12/5/2009)
Also, an FYI for next time: SSIS questions will get better response in the Integration Services forum.

As it was a string manipulation question, so thought of posting here. Thanks for pointing out ...

I will follow the rules ....:-)

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

SQL_Quest-825434 (12/6/2009)
RBarryYoung (12/5/2009)
Also, an FYI for next time: SSIS questions will get better response in the Integration Services forum.
As it was a string manipulation question, so thought of posting here. Thanks for pointing out ...

This the SQL2005 Administering forum, it has nothing to do with string manipulation. If it was about manipulation strings with SQL, then it could go in the T-SQL forum or perhaps the Development forum (a stretch though). Manipulating strings in SSIS, should of course go in an SSIS forum.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Bad day, Barry?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jeff Moden (12/6/2009)
Bad day, Barry?

Heh, well, if I wanted to be a Jerk, I could say that since I've been an invalid for the last six weeks due to a still undiagnosed neurological disorder, that every day is a bad day. ... I could say that, but I won't, because then I would be a Jerk. 😀

Instead, I'll just say that it shouldn't be too much trouble for people to post their questions in something close to the right forums.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

I don't have nearly the same intensity problem, but Scotch helps. 😛

Shifting gears... Man, I sure do hope you get better. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff Moden (12/7/2009)
I don't have nearly the same intensity problem, but Scotch helps. 😛

Hmm, I'm pretty sure that Scotch & Percocet are contraindicated, but what the heck, maybe if I take enough, it'll work? 😛

Shifting gears... Man, I sure do hope you get better. 🙂

Thanks, Jeff. Keeping a positive outlook so far... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

RBarryYoung (12/7/2009)
Scotch & Percocet

Heh... heat it up, add a little lemon and honey, and you have my favorite totie. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)