Concatenating fields: column

Showing posts with label column. Show all posts

Thursday, March 22, 2012

Conditionals on derived columns

Hi,

Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

----------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END

FROM
ResidentData B

WHERE
(AgeCalc >= 18)
----------

How do I do conditionals on the "AgeCalc" derived column?

Thanks.How do I do conditionals on the "AgeCalc" derived column?

Thanks.

You have to write the expression over again:
WHERE
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END >= 18

Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

Regards,

hmscott|||Thanks for your help - I will test the solution and see what the performance is like.

The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.|||select *
from (
SELECT ...
, AgeCalc =
CASE WHEN dateadd(year
, datediff(year, B.DOB, B.DateIn)
, B.DOB) > B.DateIn
THEN datediff(year, B.DOB, B.DateIn) - 1
ELSE datediff(year, B.DOB, B.DateIn)
END
FROM ResidentData B
) as T
WHERE AgeCalc >= 18|||Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Conditionally Formating Subtotal Output

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.

I have also found that the autogenerated subtotal feature has much to be desired.

Tuesday, March 20, 2012

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

Thanks again!

Marty
|||

Hello Marty,

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditionally adding a column to my custom component (part 2)

A month or so ago I instigated this thread- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=243117&SiteID=1 which talked about how to conditionally add a column to my component depending on the value of a custom property. If the custom property is TRUE then the column should appear in the output (and vice versa).

Bob Bojanic said I should use the SetComponentProperty() method to do this and that is working pretty well. However, it bothers me that SetComponentProperty() could be called, the column will then be added, and then the package developer could press 'Cancel'. In this instance the value of my custom property would be inconsistent with the presence of the extra column.

How do you get around that?

Thanks

Jamie

This is what component views are for. Call the GetComponentView method to create a view of the component. Then make all your changes to the component. If the user selects OK then call commit on the view. If the user selects cancel then call Cancel on the view. The view keeps track of all changes and commit saves them to the component and cancel discards all the changes.

Thanks,

Matt

|||

To get OK/Cancel functionaility in a component UI, for free, make sure you make all changes through the CManagedComponentWrapper instance of your component, not the component direct. I go on about this in the book, download the samples to see an example.

metaData is IDTSComponentMetaData90 normally passed on the form Ctor or your own setup method , comming from the UI class. Then call "CManagedComponentWrapper wrapper = metaData.Instantiate();" to get the wrapper.

Update - Matt are you sure about GetComponentView, was that not dropped or at least you no longer need to do it, sometime after beta 2 I think. The bool result from IDtsComponentUI.Edit is now sufficient to commit or rollback the changes made through the wrapper.

|||

Darren,

Yes, I am sure but the caveat here is that I always think in native code and everyone else thinks in managed code. For native code you need to use views. For managed code it does the views for you without your knowledge (please disregard the man behind the screen ).

Thanks,

Matt

sql sql

Conditionally adding a column to my custom component

Hi,

I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.

Depending on its setting, I want to include (or not include) a column in the output.

Any advice on how to go about doing this (with some sample code) would be much appreciated!

Here's how I'm declaring the property in ProvideComponentProperties()

IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New(); IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; IncludeErrorDesc.Name = "Some Name"; IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName; IncludeErrorDesc.Value = Convert.ToBoolean(false);

Thanks in advance

-Jamie

Implement SetComponentProperty method in your component and if the property is set to true add your column, otherwise find it in the collection and remove it.

I do not have a time to build you a sample, but give it a try and let us know if it does not go well.

BTW, you do not need the following line from your sample:

IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;

Thanks.

|||Hi Bob,
I nevre replied to this. Just wanted to say thanks for this - it worked a treat!
-Jamie|||

You are welcome, Jamie. I am glad it worked out.

Monday, March 19, 2012

conditional update within value

I have a column of data in SQL Server 2000 that I need to replace
values within it with new values. I know how to use CASE statements to
do conditional updates but not how to do this. Here is an example, not
the real example as the values relevant to my company would mean little
to anyone.
If value contains "name", replace it with "fullname"
If value contains "address", replace it with "fulladdress"
and so on...
What I want to do in the field is the following:
Field value now: abc##name##123
Field after change: abc##fullname###123
Field value now: asdlfkjlsdkafjnameasldfjk123
Field after change: asdlfkjlsdkafjfullnameasldfjk123
Field value now: adlsfkjaddresslksdfj34
Field after change: adlsfkjfulladdresslksdfj34
And update all rows in the approriate column with the above logic.
Any ideas?
Thanks.
JRYou don't need a Case statement to do this, you can use
Update #t Set foo = Replace (Replace (foo, 'address', 'fulladdress'),
'name', 'fullname')
Where foo Like '%name%' Or foo Like '%address%'
You could also do it with a Case statement like
Update #t Set foo = Case
When foo Like '%name%' Then Replace (foo, 'name', 'fullname')
When foo Like '%address%' Then Replace (foo, 'address', 'fulladdress')
Else foo
End
Where foo Like '%name%' Or foo Like '%address%'
Please note, however, that depending on your data, those two statements may
do different things. If a row has both "name" and "address" in that column,
the first update statement will change both name and address, but the Case
statement version will update only name to fullname, but won't change
address in that row.
Tom
"JR" <jriker1@.yahoo.com> wrote in message
news:1142706489.831624.92670@.j33g2000cwa.googlegroups.com...
>I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>|||You might want to have a look at STUFF as well, although REPLACE may well do
the trick.
The thing about CASE expressions is that they are 'falling rock' ie for the
first WHEN condition it finds to be true, it will return the THEN bit and
exit the statement. So if your string has multiple bits that need to
replacing, you'll need to run the UPDATE multiple times.
Hope that helps.
Damien
"JR" wrote:

> I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>

Conditional update

I want to update a varbinary(max). If the column is null, then I will just
set it, but if not null, then append with a .Write. Something like:
row = select...
if (row.Document = null)
Update myTable
Set Document = 0xFF
where FileName = 'Text99.txt';
else
UPDATE myTable
SET Document .WRITE(0xFF, null, 0)
WHERE FileName = 'Text99.txt';
What is the pattern to do this sort of thing? TIA
William Stacey [MVP]William Stacey [MVP] wrote:
> I want to update a varbinary(max). If the column is null, then I
> will just set it, but if not null, then append with a .Write.
> Something like:
> row = select...
> if (row.Document = null)
> Update myTable
> Set Document = 0xFF
> where FileName = 'Text99.txt';
> else
> UPDATE myTable
> SET Document .WRITE(0xFF, null, 0)
> WHERE FileName = 'Text99.txt';
> What is the pattern to do this sort of thing? TIA
Update MyTable
Set Document =
CASE ISNULL(Document, -99)
WHEN -99 THEN SOMETHING
WHEN Document THEN SOMETHING_ELSE
END
Where FileName = 'Text99.txt'
Even easier would be to write a stored procedure and set the new column
value accordingly using a local variable.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
William Stacey [MVP]

Conditional table entries and sums

What I have is 3 columns. The first and second are data from a database, the
third is a conditional difference of the two. That is, column 3 is C1-C2 if
that is > 0, else it is 0.
So I have:
C1 C2 C3
1 2 0
2 1 1
I can get that to work fine, using Iif(). The problem is that I need a total
row at the bottom of the table. Columns 1 and 2 are easy to sum, but how can
I sum the conditional entries of column 3?
Thanks!You can add a calculated field to your dataset. Check out RS Books Online
for instructions. (In the report designer, right-click in the Fields pane.)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RPH" <RPH@.discussions.microsoft.com> wrote in message
news:FC250591-C2B6-4399-AAC4-4515217CEAFA@.microsoft.com...
> What I have is 3 columns. The first and second are data from a database,
> the
> third is a conditional difference of the two. That is, column 3 is C1-C2
> if
> that is > 0, else it is 0.
> So I have:
> C1 C2 C3
> 1 2 0
> 2 1 1
> I can get that to work fine, using Iif(). The problem is that I need a
> total
> row at the bottom of the table. Columns 1 and 2 are easy to sum, but how
> can
> I sum the conditional entries of column 3?
> Thanks!

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 questions

I have a zipcode column that contains xxxxx-xxxx, i want to use conditional split so that i can take the last 4 digits and put them into a different column, I tried to use the SUBSTRING ("ZIP", 6, 4) but it returns an error, any ideas on how i can split it?

Thanks.

Actually, I think you want the derived column transform, not the conditional split. Your SUBSTRING should work fine.|||

I think you want a Derived Column Transfomation too, but you have a couple of mistakes, assuming this is a SSIS expression-

You need to remember the SSIS expression syntax is C style and therefore zero based, so index 7 is the start of the last section, I assume you are skipping the hyphen.

You have used double quotes, this makes it a literal.

Try SUBSTRING(Zip, 7, 4)

Both points don't count if you are still trying to do this on SQL, but I assume you wanted a SSIS solution.

As a rule it helps a lot if you give the details of the error, things like error message, as save a lot of guess work when trying to help you.

Sunday, March 11, 2012

Conditional Split Question

Hello,

I am have an ID column that sometimes contains all numeric characters and sometimes contains all digits. I would like to the records with all digits (0-9) to continue downstream in my Data Flow. I would like the records that contain characters other than digits to be logged to a table.

This sounds like a job for the Conditional Split transformation, but I don't see a way to easily test for a numeric value. For example, I would like to use something like ISNUMERIC([MyIDField]) for testing the values in my Conditional Split, but I don't see a way to do this.

Do I have to create a Derived Column transformation prior to my conditional split that populates a "numeric" ID column for each of my records then test this Derived Column in my Conditional Split? Seems like more work than I would to see for something as simple as testing for a numeric...

TIA...

Brian

"numeric characters and sometimes contains all digits"... digits are numeric? Anyway I'd use my Regular Expression Transform http://www.sqlis.com/default.aspx?91. It will handle the test and split, and regular expressions are great for validating things like this.

|||

Brian,

You are correct, there is no ISNUMERIC() function in the expression evaluator. However, in your case, there is a fairly decent workaround I think.

If you are SURE that the string is never a mix of numeric and character data, you could use the following expression to direct alpha strings (where Col is the name of your input column:

FINDSTRING("0123456789", SUBSTRING(Col, 1, 1), 1) == 0

This expresssion will be true if the first character of Col is an alpha character.

Hope this helps.

Mark

Conditional split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

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