.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

DateTime Validation in SSIS using conditional split

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server


I am facing issue with null validation for DateTime. I have input csv file which contains DateTime field as Date datatype. In the destination database table, I am storing this field as datetime. I am using conditional split feature of SSIS package and try to validate this field before inserting into the database.

The value which is going into the db is "1899-12-30 00:00:00.000". I dont know why?

I have tried various expressions like ISNULL(DateTime), LEN(DTSTR, DateTime) == 0, ISNULL(DateTime) ? False : DateTime. Nothing is working.

Req is simple, if date is valid insert in db otherwise write is as bad data in error log file.

Can anyone provide suggestion on this.




View Complete Post

More Related Resource Links

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Using User Variables (ONLY) in SSIS Conditional Split

After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

Adding exception table to incremental Load with SSIS Lookup task and conditional split



I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.


Below is what I have put in the conditional transformation editor for my first output condition:


SSIS Conditional Split


 i created a small DTS flow which is fetching data from OLD DB source to two  flat files using condition given in Conditional Split Transformation


Conditional Spilt has two condition according to source table column as given

Case1      Department == "IT"

Case2      Department == "Management"

Case3      Department == "Sales"

now as per this Case "IT", "Management" and "Sales" i am generating the flatfiles in three location C:\IT , C:\Management , C:\ Sales folder respectevly


this package is running fine but here the logical problem occurs, All the files are generating with accurate records in there respective folders but simultanuasly two other file also generating in all folders. which is not required,

it seems the condition which get satisfied executes and generate the file in there respective folders and fill the record in that file but other 2 case2 is also executing simultaneously and generating blank files

Assistance is highly appreciable.

Thanks in advance






Conditional Validation



i have been looking for a way to conduct conditional validation for a web page i am developing and it seems like there isnt a real clear cut way to do this dynamically. from what i can tell a validation method could be developed in the aspx.cs file that is called to make sure the required fields are filled in for particular cases, but is there any other way? i was hoping to do it dynamically (as i mentioned before), does anyone have any examples i could look at?

here is what i am trying to accomplish:

There will be a drop down box, if a certain value is selected from that drop down box, another drop down box then becomes a required field. if any other value is selected then the other drop down box is only optional.

I also have another situation where i have a drop down box, when a certain value is selected, their must be input in the text and dropdown boxes next to it (it would be pretty cool to hid the boxes until needed but i am not sure how hard that would be). if any other value is selected from the drop down box, no input can be excepted in the other two fields.

Thanks for that help!

Data validation for datetime parameter in SSRS

Hi,   I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:   Public Function Validate( ByVal startdate As String) As BooleanIf IsDate(startdate) = True Then Return TrueElse Return FalseEnd IfEnd Function   And my report has a textbox which has the expression property set to; =Code.Validate(Parameters!startdate.Value) the textbox on the report has to display if the entered date is valid or not.   But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.   Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.   Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

MVC Validation Pattern Question, Especially for Variable Length Lists and Conditional Validation


I've spent a lot of time exploring the various patterns of MVC validation. In particular, I am interesting in learning best-practice for validating a view which posts an enumerable collection back to the controller. One approach that is appealing involves posting the form via Ajax to check validation (http://jvance.com/blog/2010/02/20/MakingAnAjaxFormWithJQueryInASPdotNETMVC.xhtml). Obviously, the negative is that this is not true client-side validation since there is a "hidden" server round-trip, but the effect is comparable, and obviously client-side validation alone will never stand--server-side always needs to be done as well.

The CodePlex repository for this demo: http://mvcajaxvalidation.codeplex.com/SourceControl/list/changesets

The main reason I started heading this route is because in the course of writing lots of custom data annotation model validators (think "RequiredIf" or "RequiredWhenContains") it became very evident that the wiring to the client-side gets complicated. I was mostly able to pull it off using the custom validators' GetClientValidationRules() plus jQuery validator extensions (.addMethod('requiredif'...), ho

Convert SSIS DateTime to a String


Being a newbie to SSIS I'm not sure of the most efficient method of converting a DateTime object to a String.


I'm from a C# background where this would be easy using DateTime.ToString("YYYYMMdd"). I want to use the date in a file name so don't require most of the parts.


I'm sure I could do this using a script task to produce a file name for each row of data in my table and add that filename to the dataset but it seem like overkill to do something that should be simple. Also as I'm supposed to be getting to grips with SSIS I shouldn't keep running back to what I know.


My current approach is to derive a column and build up an expression to convert the date into a string. The only problem being that it doesn't work.

The expression I'm working with is:

(DT_WSTR, 50)([OrgName] ) + "_" + (DT_WSTR, 50)( [PayrollName] ) + (DT_WSTR, 4)(YEAR( [ProcessedDate] )) + (DT_WSTR, 2)(MONTH( [ProcessedDate] )) + (DT_WSTR, 2)(DAY( [ProcessedDate] )) ".txt"


Can anyone see where I'm going wrong?


All comments greatly received.

how to use split in iif conditional expression in ssrs 2008 r2



how can i use conditional iif combined with split?

i'm trying to get values from check boxes(from sharepoint custom list), the condition is like this :

if the value is null/blank, the return value would be "-"

if the value is not blank, the return value would be the value of the check box field that already splitted from the ";#" character

as you might know, values from check box is written along with ";#" character before and after the value itself.

i tried to use this expression:

=IIF(Fields!Action__For_User_Access_.Value Is Nothing," - ",(Split(Fields!Action__For_User_Access_.Value, ";#")(1)))

the split worked just fine, but the conditional iif didn't work.

can anybody help me?

i used that expression in the calculated field.

btw, i'm using report builder content type from ssrs 2008 r2


thx in advance.



Conditional Split



I have a problem using conditional split,

I've got the raw txt data that i need to insert into sql database, having set the delimiter as ",", i encountered a problem;

Raw data example,



00101,Andrew, Jackson, Nurse


Data inserted to SQL ended up to be...

ID Name Job
00100 Harris Student
00101 Andrew Jackson ,Nurse

But when i initiate the conditional

If it is inserted correctly to the SQL Database,

It should be... (Take note of "Jackson")

convert datetime to Numeric in ssis

I have a source with datetime datatype and destination as numeric. how can i do that using dataconversation or anything with help i tried with convert funtion its not working . can anyone help . thanks

DateTime Conversion Issues in SSIS



I have a Weird issue with SSIS. I have some input data of dates like '01/01/1999' and '01/01/0207. First one is a valid date and later is invalid. I have to use DataConversion inorder to convert it to datetime and re-direct the errored row to Flat-file destination.

Now the Issue is:

If I convert to DateTime (DateTimeStamp) in Data Conversion, it is treating both the dates as Valid and failing in my SQL Destination as the later is an invalid date.

If I convert to DateTime2 (DateTimeStamp2 with Precesion) in Data Conversion, it is treating both the dates as Invalid?

How do i fix this in SSIS (In a simple way) to throw 1 as error and other as Valid? Any help is appreciated.



Conditional split?


Hi All,

I have a package which imports several files on a regular bases . For the import I use a staging table. Now I want to split that stagingtable into two other (existing) tables with keeping the relation ( third table??) . In each record there is a dealerID which has to be injected also in the other two tables (the splittables).

How can I use a conditional split to extract the right columns to the right table? I only see that i can enter a expression for each column, but in fact i only want to split the table by "fingerpointing" the right columns. is that possible? And how should i put the relation between them in 'relation' table?


I hope someone van help me,


Thanks in advance,




conditional validation


I want to make either last name or first name required. Below is the code and it does not work as expected.
Once a RequiredFieldValidator is enabled by the Page_Load() method, there is no way to make it execute that method again to disable the RequiredFieldValidator. How can that be fixed?

Let's say after the first time the page loads, I click the Find button without entering any name.
That causes RequiredFieldValidator1 to be enabled by the Page_Load() method.
Now I enter first name and click Find; it does not execute Page_Load() and thus requires last name.
It should not require last name. How do you set RequiredFieldValidator's Enabled attribute when the server code is not executed?

Last Name: <asp:TextBox ID="LastName" runat="server"></asp:TextBox>
First Name: <asp:TextBox ID="FirstName" runat="server"></asp:TextBox>
<asp:Button ID="Button1" Text="Find" runat="server" OnClick="Button_Click" />


SSIS Package Validation Taking Long Time



The package that I am workin on used to open in BIDS real quickly and also when running it through BIDS. Now, it is taking a long time to open in BIDS and also when I am executing it using DTEXEC it is taking more than 60 minutes just for validation.

I dont consider it as heavy as it has fewer tasks compared to other packages and they open really quickly. And also I can open the package to work offline, but have issue with it while executing the package using DTEXEC.

I have tried DelayValidation=TRUE, but it is not much of a help.



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend