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.
| Author |
Topic |
|
robofski
Starting Member
3 Posts |
 Posted - 2011-03-13 : 10:57:09
|
| Hi, new to the forum and new to SSIS so please be gentle!I have a simple SSIS package I'm trying to put together, input is:Part#Seq#TextOutput isPart#Seq#TextThe problem is the input Seq# is numeric and steps up in 10's (10, 20 , 30 etc) The output Seq# is string length 4 in the format 0001, 0002, 0003 etc.I've got the the point where my input is a string 1, 2 or 3 and I thought I could use a script to concatenate 000 or 00 or 0 infront of this depending on the length of the input, so essentially something like:if Len(Seq#) = 1 then seq# = "000" + seq# if Len(Seq#) = 2 then seq# = "00" + seq# and so on.This might not be the best way, and I'm no SQL genius or VB genius for that matter but I'm hoping to find one here.Any help very much appreciated.Thanks,Dan |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-13 : 18:08:22
|
I familiar with VB only in passing, but I would do it like this:seq = right("0000" & cstr(seq / 10), 4) |
 |
|
|
robofski
Starting Member
3 Posts |
Posted - 2011-03-14 : 04:19:31
|
| Thanks for the pointer, can you expand on what the script might look like a little more?Thanks,Dan |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-14 : 15:02:58
|
| I thought you had set up a script task in SSIS and that reformatting the string was part of that task. You can do the reformatting as I described, in the same part of the code that you are using to get the input string to the 1,2,3 form.If you are unfamiliar with script task, here is a link to a tutorial: http://www.mssqltips.com/tip.asp?tip=1443If this is the only thing you are doing in a script task, there may be simpler ways to accomplish that than via a script task. |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-03-15 : 11:39:02
|
| It might be easier to do this with a derived column transformation in your dataflow and manipulate the data with an expression. |
|
|
|
robofski
Starting Member
3 Posts |
Posted - 2011-03-15 : 12:07:03
|
| That's how I was first thinking of doing it but couldn't figure out an expression that would work! I'm using the derived column for getting the 10 converted to 1 in the first place so it would be a good place to add it if I could. I've got a dirty work around right now using a lookup table (I know it's probably the worst way to do it!) but I'd like to improve on it! As I said new to all this SSIS stuff, this was previously being done in MS Access and it works but it's just not great!Thanks,Dan |
|
|
|
|
|
|
|
|
Using a Script in the Data Flow