converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

Converting a date with a derived column

I'm having trouble converting a date in the format of:

Jan 11 2006 12:00:00:000AM

to a smalldatetime in my new SSIS package. I'm trying the derived column transformation, but I'm at a wall now, especially with the conversion of 'Jan' string to integer/month/value of 1. Anyone have experience/advice on this transformation?

What happened to the DateTime String transformation in SQL 2000? Was that too unpopular to move to integration services? That transformation saved my butt many times - every banks' data feed we import uses a different date format.

Thanks in advance for any advice/help!

-Erik

Have you attempted to chop the seconds and milliseconds off the date? And then stuff it into a smalldatetime column.|||

Seems like it does not like those 000 at the end.

The only way I see at this point - to cat the 000 out of it and them cast it with DT_DBDATE or something

|||I can use SUBSTRING to skip the time at the end, but what about converting Jan to the value 1?|||After you do SUBSTRING, use type casting to cast to needed date format. If this is not enough, you can use YEAR,MONTH abd DAY after you converted to ANY date format

Convert String to smalldatetime

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Noel Fernandez wrote:

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Try changing Table2.ColumnB to datetime rather than smalldatetime and see if it works.

-Jamie

|||

What type of database are you writing to? I can't replicate this against a SQL Server 2005 db.

If you can't change the datatype of the destination, you might try casting the column to a smalldatetime when you read it from the source.

|||

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Hi Subhash,

Well now I'm confused. First of all you said the error was:

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Then you said it was:

error while converting DB_STR type to DB_DATATIMESTAMP type

Which is it?

-Jamie

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Use this expression in a Derived Column transform:

Code Snippet

(DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + SUBSTRING(DateValue,5,2) + "-" + SUBSTRING(DateValue,7,2))

The date needs to have the dashes inserted for the cast to work.

|||

Jwelch,

Thanks for your support. That resolved our issue. I was unable to mark this as an answer, however I have marked that as a helpful post.

Regards

Subhash Subramanyam

convert getdate() to string dd-mm-yyyy

I would like to convert getdate() value to string of dd-mm-yyyy format in SSIS... how can I achieve this ?

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ (DT_WSTR,4)YEAR(GETDATE())

|||

Hmm... that is ugly clutter of functions!

I was hoping for a simple function [as in oracle to_char(date, format) ]

How come microsoft didn't provide such a simple and too commonly used function ?

|||

Probably because this is a version 1 product and the aim is to get in as much functionality as possible. Later I hope ti will be extended to include shorthand functions like you suggest. The syntax that Darren provided will work just fine.

Admittedly it would be nice to have shorthand functions that do this - or even better, the ability to build your own function libraries. if you think that would be a good idea then ask for it at Microsoft Connect.

It is inappropriate to compare SSIS's expression language to Oracle's PL/SQL language. If you want to compare apples with apples then you will find that the T-SQL convert() function is similar to PL/SQL's TO_CHAR() function.

-Jamie

|||If you wanna do it in a script task or component its just:

MyDate.ToString("dd-MM-yyyy")

HTH

PJ|||That works a treat, but it should come with information note that says it will be slower. I wouldn't worry about this for a task, but for a component, I would use the Derived Column over a Script Transform any day of the week, performance is much better. You have the choice however if you feel that the maintenance overhead of the more complicated expression syntax outweighs the performance loss.|||

It gets more complicated if I need to do any arithmetic on date… need to use dateadd function everywhere on getdate().... which may sometime lead to unnoticed/invisible errors..

I was trying to store current date minus 1 in a variable so the package extracts previous day’s data from source system for load.

I guess we have to leave with it for now….

Current Date minus 1:

RIGHT("0" + (DT_WSTR,2) DAY( DATEADD("DD",-1,GETDATE())), 2) + "-"

+ RIGHT("0" +(DT_WSTR,2) MONTH( DATEADD("DD",-1,GETDATE())), 2) + "-"+ (DT_WSTR,4) YEAR (DATEADD("DD",-1,GETDATE()))

|||

Have you registered anything at Microsoft Connect? If you don't ask you don't get.

-Jamie

|||

Jamie,

Added the suggestion in microsoft connect (subject "more standard functions needed in SSIS ").

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=166356

Request people to vote on this.

Thanks

|||

Mahesh,

Thanks for posting that!

I would actually rather they give us the ability to build user defined functions rather than adding a plethora of new out-of-the-box functions. I have added a comment to that end.

Regards

-Jamie

|||

Jamie Thomson wrote:

Mahesh,

Thanks for posting that!

I would actually rather they give us the ability to build user defined functions rather than adding a plethora of new out-of-the-box functions.

ask and ye shall receive: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=166367

|||

Bonzer!

Thanks Duane.

Although I think the phrase should be "ask and ye might one day get it if you're bloody lucky"

-Jamie

|||

I also think that the ability to reference a column created in the same Derived Column component would be damn useful: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010

-Jamie

Convert DateTime field

Hi,

I'm not entirely sure that this is the correct forum for this question but it relates to my SSIS package.

I am currently implementing an SSIS package to replace an existing stored procedure which is getting very unmanageable. I have come across a part in the stored procedure which performs a convert(datetime, @.Parameter3, 14) on a string of data. The string value of @.Parameter3 is in the following format HH:mm:ss.

The problem i am having is how to implement similar functionality in a script task. everything i have tried involving the datetime object returns 1,1,0001, 00:00:00 or similar. It never seems to get the time so that it can be passed to a datetime field in the database.

Thanks in advance for any help that is provided.

GrantAh. The data conversion task seems to have resolved my problem. Sorry.

Grant

Convert Date Format of 00-XXX-00 to NULL

Let me start by saying that I'm brand new to SQL Server 2005 and SSIS.
I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"

The value of 00-XXX-00 means that there is no purchase date.

I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.

The table Data Type is datetime.

If I use the import wizard using the example above I get this error message:

- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

If I remove the 00-XXX-00 values and import something like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"

The import is successfull and the dates look correct in a querry.

SELECT *
FROM date

date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000

(3 row(s) affected)

Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?

Thank you,

Ryan

Ryan,

The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.

In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.

You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

Hope that helps!

-Jamie

|||Could you please break down what that expression does or point me to a reference for the expression language?
|||

Don't mean to butt in here, but what Jamie has listed here:

Jamie Thomson wrote:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

-Jamie

uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.

So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.

The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.

Hope this helps.

Mark

http://spaces.msn.com/mgarnerbi

|||

There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.

-Jamie