Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:

Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Data Conversion Numeric to date

I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)

I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.

However I cannot seem to be able to convert and store (in SQL) this column can anyone advise

Thanks

The source column is called AHDATE and a value is 60703 which I am told is YY/MM/DD and using a data conversion componant transforming to data type date[DT_DATE] I get 12/03/2066 00:00:00

Which is way out

Thanks for any help

Robbie

|||

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

|||

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

|||

Blackuke wrote:

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

Store it however you like. That's up to you. As is the data type of the output column from the Derived Column component.

If you want to store it in a datetime field, cast it as a datetime value. You can do this within the Derived Column component.

-Jamie

|||

Blackuke wrote:

Jamie Thomson wrote:

60703 is not a literal that can be successfully converted to a date. "6" is not a year. You'll have to do something cleverer to work this out. i.e. Inside a Derived Column component extract all the different parts and concatenate them together to get what you are after.

e.g. "200" + SUBSTRING((DT_STR, 5, 1252)[columnname]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[columnname]),4,2)

Something like that anyway. I haven't test it.

-Jamie

Thanks for the help, I think half my battle is how I am trying to store it, what should the data type be in the derived column and also on the column of the table i am trying to store it in?

At the moment I am trying to store it as a date format should i be storing it as just a char or some thing

thanks again

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

|||

Blackuke wrote:

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

yeah my bad, sorry. Its just a matter of counting brackets though. Try this instead:

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],4,2)

rather than this which is what it was before (I've highlighted the offending characters in blue):

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

-Jamie

|||

Thanks guys, I have done my goal for today and that is import the data into SQL 2005 as a date which i have done thanks to your help.

I used a derived Column using the followin expression

"200" + SUBSTRING((DT_STR,5,1252)AHDATE,1,1) + "-" + SUBSTRING((DT_STR,5,1252)AHDATE,2,2) + "-" + SUBSTRING((DT_STR,5,1252)AHDATE,4,2)

I selected database timestamp as the data type and on the SQL 2005 table itself, on the date column I changed the data type to datetime

This was the only way I could get it to work.

Thanks guys for pointing me in the right direction, where shall i send the beer

(If you have a Exchange 2007 Question I am your man)!!!

|||

Jamie Thomson wrote:

Blackuke wrote:

So much for this beeing easy, you make it sound so!!

I put the following in the derived column expression feild but it remain s red saying the function subtring requires 3 parameters not 1 bla bla bla, I have no idea can someone advise

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

thanks

yeah my bad, sorry. Its just a matter of counting brackets though. Try this instead:

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE],4,2)

rather than this which is what it was before (I've highlighted the offending characters in blue):

"200" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),1,1) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),2,2) + "-" + SUBSTRING((DT_STR, 5, 1252)[AHDATE]),4,2)

-Jamie

Thanks, i managed to google around to find it out, thanks for the help, no doubt you will see more of me!!!!

|||

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

|||

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

|||

Blackuke wrote:

Hi Jamie

It worked great until I put in the historical data

for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03

however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this broke the import

is there a way to say that there are 6 digits, the first needs to be a 2 and all the missing digits need to be 0's

Please help

Thanks again

Yes.

I'll point you in the right direction but you're gonna have to piece this together yourself I'm afraid. I'm busy :)

The conditional operator will help you out:

http://msdn2.microsoft.com/en-us/sql/ms141680.aspx

-Jamie

|||

Thanks I appreciate it

|||

I could not get it working, so I decided to do 3 conditional splits and then the derived columns.

Seems to be working, now just got to work out how to do a time dimension, I have the invoice date in the fact table I think I have already made a mistake.

Thanks again

data conversion - numeric to string loosing precision

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

Is your ultimate database target for the numeric data type a character based column, hence the need to retain the leading zeros?

If that is the case, retaining leading zeros can be accomplished with a derived column transform, rather than a data conversion transform. There you have access to the SSIS expression language, with a relatively standard, if meager, set of string functions, as well as type casts.

Another alternative for pretty much an data type conversion is a Script Transform, which will afford you the full power of the .NET framework, including in this case custom numeric formats for types destinated for strings.|||

Hi jaegd,

i tried that, but no luck. i use a derived table and this is my expression

ISNULL(investmentPercentage) ? 0.00 : investmentPercentage

here investmentPercentage is a numeric (15,2). i add the above expression as a new column (string) (8 chars). but still it looses the 0

.00 when it's null

.32 when it;s 0.32

any comments

AJ

|||

Use the Script Task, and follow the instructions in this link for numeric format specifiers for strings. You need to do something similar to this...

Dim number as float

number.ToString("D")

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

|||

In the derived column, you can still acheive it with conditional logic The script as mentioned above is cleaner, but if you want to avoid adding that, you can try an expression like:

(investmentPercentage < 1 ? "0" : "") + (DT_WSTR, 20)investmentPercentage

This expression will add a "0" to the beginning of the string if the number is less than one (which is the only time the zero is dropped). When the number is greater than or equal to 1, an empty string is added.

NOTE: this does not take into account negative values. If investmentPercentage can be negative, you will need to tweak the expression a little (or post here, and I can help with that).

Thanks
Mark

|||

Hi Mark,

thanks for that, but it sort of half answer my question.

yes investmentPercentage can be negative or can even be null. i was wondering if there's an eaisier way. i've got a lot of cloumns like this, writing an expression for each of them this long can be tedious. is this how you would "tweak" it?

ISNULL(investmentPercentage) ? "0.00" : (investmentPercentage < 1 && investmentPercentage >-1 ? "0" : "") + (DT_WSTR, 8)investmentPercentage

|||

You might find it easier to use string formatting in the script component... I can't think of any other way that would be better.

As for tweaking the expression, you will have to do some extra things to take care of the negative sign (so the prepended zero doesn't end up before the negative sign). The following expression adds a case for values between -1 and 0 to handle that:

ISNULL(investmentPercentage) ? (DT_WSTR,8)"0.00" : investmentPercentage < 0 && investmentPercentage > -1 ? "-0" + SUBSTRING((DT_WSTR,8)investmentPercentage, 2, 8) : ((investmentPercentage >= 0 && investmentPercentage < 1 ? "0" : "") + (DT_WSTR,8)investmentPercentage

Let me know if this does the trick for you.

Thanks
Mark

Daft DTS Data Driven task question

Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:

UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)

The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.

Anyone know the answer - I'll bet it's simple :)

Cheers,

MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.

Very strange.