Concatenating fields: transformation

Showing posts with label transformation. Show all posts

Monday, March 19, 2012

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:
I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Split Transformation

Can any one please tell me how do I give multiple conditions in Conditional Split Transformation.

Exp:

I have few columns as

ReturnSUK

TimeSUK

EntitySUK

PeriodSUK

Now the condition should be :

! ISNULL (ReturnSUK) & ! ISNULL (TimeSUK) & ! ISNULL (EntitySUK) &! ISNULL (PeriodSUK)

Please provide me the proper condition for the above mentioned requirement.

Thank you

Use two & symbols:

!ISNULL(ReturnSUK) && !ISNULL(TimeSUK) && ....|||

Thank you Its Working

If i need to give the same condition for OR (^) rather then AND (&) so the condition would be

this :

ISNULL(ReturnSUK) ^ ISNULL(TimeSUK) ^ ISNULL(BankSUK) ^ ISNULL(EntitySUK) ^ ISNULL(PeriodSUK)

or ,can you please help me in this too.

Thank you

|||Or is written by using two || symbols:

TEST1 || TEST2 || TEST3 ....

Sunday, March 11, 2012

CONDITIONAL SPLIT Assistance

i need to use a conditional split transformation to find missing column and direct the output of conditional split to my destination.

I have the following columns PatientId, Allergycode, SeverityCode

My requirement is to check whether value of a particular column is null or not null.

Please help.

Ronald

What part is a problem? Have you looked at transform documentation?
http://msdn2.microsoft.com/en-us/library/ms137886.aspx
To check for null values use IsNull method :)
http://msdn2.microsoft.com/en-us/library/ms141184.aspx|||

Hi Entin,

I have an access source table and the destination SQL table. in between i have the Conditional Split in which i use !ISNULL() the each column i have in my prescription table to test for missing columns and the Data conversion for changing the data type for the date column. Data conversion tranformation follows after the Split Condition Trans.

When i execute the package, it executes successfully but it doesnot write any rows to the destination table in SQL SERVER database.

Help out me bro.

Ronald

SSIS package "Conditional.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (322)" wrote 0 rows.

SSIS package "Conditional.dtsx" finished: Success.

|||How many outputs does your Conditional Split have? Which outputs are connected to Data Conversion transform and SQL Destination? Have you monitored the data flow during execution - do any row flow out of the output you are using?|||

My conditional Split has 14 outputs.

The conditional Split default output is connected to the data conversion.

Yes, I have monitored the data flow during execution. No, it does not.

|||So it probably means that each row satisfies at least one of the conditions, and no row falls back to the default output?|||

So, how do i go about this to make sure that, it writes rows to the destination table in SQL SERVER

Regards,

Ronald

|||

When i use case12 (MedicineCode) as an output to the Data conversion.

When i execute the package, it writes 57 rows to the destination table instead of 58 rows.

Ronald

SSIS package "Conditional.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [322]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 3. The destination column (VisitType) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 3. The destination column (VisitType) is defined as NOT NULL.".

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (322)" wrote 57 rows.

Warning: 0x80019002 at Data Flow Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: Data Flow Task

Warning: 0x80019002 at Conditional: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Conditional.dtsx" finished: Failure.

|||

Ronaldlee Ejalu wrote:

So, how do i go about this to make sure that, it writes rows to the destination table in SQL SERVER

Each output of conditional split forms a separate data flow. If you want to insert this data, you need to either

1) have one SQL destination per output, or

2) connect the flows together with Union All transform, then have a single destination

CONDITIONAL SPLIT and Bulk load Insert Error