Concatenating fields: destination

Showing posts with label destination. 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 to FlatFile

Hi,

I have a Conditional Split to FlatFile Destination.

How can I put the result, that goes in the FlatFile Destination, in a variable also (like in Recordset Destination).

Do I have to runs this thing twise (and put the first time in FlatFile Destination and the second time in Recordset Destination)?

Thank you.

Why do you want to put the results in a variable?

A multicast transformation will let you split the data flow into as many branches as you want.|||

Take a look at the Multicast and conditional split component in the data flow. You could 'branch' the data pipeline and send rows to multiple destinations in one pass.

Sunday, March 11, 2012

conditional split problem

I have a dataflow where i transfer data from textfile to oledb destination

I have a conditional split in between and check if incoming fields are empty.

in the conditional split i have

ISNULL(column1)|| ISNULL(column2) || ISNULL(column3)|| ISNULL(column4) ||ISNULL(5) || column1 == " " || column2 == ""||column3 == " " || column4== " "||column5==" "

this is what i have in my conditional split to check if they are blank.

it dosent show them as blank at all..

what am i doing wrong?

Are you looking at the wrong output from your Conditional Split?

-Jamie

|||

What is the precise definition of blank for your columns? From the expression, it looks like the answer is NULL or empty string or possible one space?

If blank can also mean "just spaces", one thing i can suggest is instead of

columnX == " "

try something like the following, which works for any number of "just spaces":

LEN(TRIM(columnX) == 0)

Mark

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