.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 Conversion Issues in SSIS

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


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.



View Complete Post

More Related Resource Links

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

ASP.NET DateTime Conversion strange behaviour



I am working on ASP.NET web application where we are using javascript calendar to accept the date in textboxes. The date format is dd/mm/yyyy. The application is running in US and UK environments.

I am facing issues in US environment while storing the dates so I have added 2 keys in config file viz culture which will be either en-US or en-GB depending on the deployment location and baseCulture which will always be en-GB.

In the code, I have added a condition which checks if culture and baseCulture values are not matching then do the conversion of date in US format.

But I have found that the code converts the date from dd/mm/yyyy to mm/dd/yyyy when the key is baseCulture (en-GB) and it throws FormatException when key is culture (en-US).

I am bit confused by this behaviour. Is this normal? Can any one please explain more about this? Note: I am working on a machine which has en-US as language and culture

<add key="culture" value="en-US" />
<add key="baseCulture" value="en-GB" />
// This line works and converts the date from 26/08/2010 to 08/26/2010
// Here the baseCulture is en-GB.
DateTime dt = DateTime.Parse(txtCurrentDate,baseCulture)

// This line throws FormatException. culture 

Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

Issues with SSIS and SQL 2008 - Integration services not running

Hey guys, I installed SQL 2008 with complete BI studio including Integration Services. When i check in my services - i cant see my integraiton services in there When i try to connect to Integration Service using localhost, I cant connect it And when i try to run setup of SQL 2008 - It shows that Integration Services already installed and that what i checked when i fetched my Installation tool report from SQL Server to verify i have it installed. Why can i access Integration services, if i already have it, Please suggest with ideas, checks i can make to ensure every setting is in place. Thanks. Would appreciate for quick response.  

Data Conversion - string to datetime help!!!

Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

Need help in datetime conversion excluding invalid records

I have varachar(50) field which have date in yyyymmdd format Now i have to filter the data which are greater then server date I have following query Select * from MDB_CONTENT_INFO Where convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate() Problem ist aht i may have some invalid data other then valid one. I am getting error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. or Conversion failed when converting date and/or time from character string. I wanted to exclude those records but currently getting error for the query asKamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

Sql DateTime2 to .NET DateTime conversion results in out-of-range exception

I posted it in C# General because I thought that the subject was not Sql Smo related, although I use Smo extensively even in this routine for connection. Unfortunately after a few days no constructive suggestion has been advanced. Two posts the thread collected are not helpful. I am wondering if Alok or someone else with the status of MSFT Sql Server developers take a look at this very importan problem for me which became a stumbling block.Thanks. AlexB - Win_7 Pro64, SqlSer64 WinSer64

Data conversion in SSIS

I am creating an SSIS package, My source DB is ORACLE and destination is SQL Server 2005 I am moving the contents of a Table AO in Oracle DB to  Table B in SQL Server2005 A specific Column in  "Renvenue " in AO table is of data type NUMBER  , it contains  decimal values like 1040.334, 19191.33, 454545.22 I have a corresponding column 'Rev" in Table B ( in SQLserver DB) of datatype Numeric(18,2)   I have created a SSIS package using Data source Reader, DataConversion , OLEDB Destination  in the dataflow task Everything is working fine, but I am getting incorrect data for example 1040.334, 19191.33, 454545.22   for these values I am getting 1040.00 , 19191.00 , 454545.00 the decimal values are not getting transferred to the destination table. I tried converting the datatype to Numeric {DT_NUMBERIC] 18 -2   and even declimal(DT_DECIMAL] scale 2 but still not getting the decimal values   Can some one tell me where am I going wrong...      

Issues with running SSIS packages programmatically

Hi, I have spent days trying to solve this problem and still stuck with this and I have posted some questions already in forums, but didn't get satisfactory answers. I am trying to be more clear this time and hope to get a better answer. I have gone through this article already http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx and here are my issues (I need to run the SSIS package from ASP.NET) option 1 is not suitable for me, because it may recycle worker process if it consumes memory option 2 is also not suitable because of security issues in creating a new process and passing the context to new process looks very complicated for me (according to the support article) option 3 is not suitable because using SQL Server Agent to run SSIS package is not allowed by the company I am working for(I guesss it requires installation of db engine on application server, not sure). but SSIS is installed on the application server. option 4&5 will have the same issues as options 1&2. I guess the only option left now is to create a windows service and start the service from ASP.NET. but will this allow running multiple packages in parallel? OR is there a better alternate solution for this? please let me know. Thanks.

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.

Help to resolve an error "The conversion of a char data type to a datetime data type resulted in an

Why am I getting an error when executing this: select convert(datetime, '2010-09-12T18:11:48', 120) The message is: "Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I don’t get the error if I remove “T” from the string. But I need it to work with “T”.

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.

VB6 to VS2008 Conversion Issues


I have a VB6 component which is used by VC++6 component and other VB6 front end applications. 

I have converted VB6 component to VS.NET 2008 and built the DLL and TLB, then registered the same using RegAsm. But when I run application, the application is not getting loaded by saying DLL failed (invalid procedure or call).

I understand that we are calling a managed dll from unmanaged environment (VC++ and VB).

Can anyone help me on this issue at the earliest?

Many Thanks, SKampel

SSIS Data Conversion without using the Data Conversion Transformation

how can i convert a record from a flat file to a different data type without using the data conversion task?

Migrating SSIS Pacakages to Prod having issues with PreValidation

The packages I migrate to Prod have Sql Server Configuration built in and Delay Validation turned on wherever it is available. The packages know what server to find the configurations by looking at the value in a global variable (ServerName) that I have created.
It works great from within Visual Studio.

When migrating to Prod, I encounter 2 issues:
1) When I build the packages, the ServerName variable and datasources are pointing to Development Servers. When the DBA attempts to set up the sql agent job in Production, the packages display an error because Production does not have visibility to the Development server. Why is it validating this? Isn't this the whole purpose of setting up configurations? How do you get around that.

2) So, I built the packages changing the server to a server that Production can see. The only server available to me is our Clone server. This server mirrors our Production server and gets refreshed once per day. So, the DBA is able to set up the sql agent job and override the Data Sources. When the job is executing it fails because the Clone server doesn't have the db objects until the next day when it is refreshed. Why is it validating the values that it was built with rather than the values that we have used to override the Data Sourc

Uniqueidentifier field failing during conversion in SSIS



I am trying to convert a table from one schema to another and the destination table (SQL CE) contains a uniqueidentifier field. This field can accept nulls and is optional. I am not supplying any values for it.

But when I run the package it throws an error.

SQL Server Compact Destination [107]: Data conversion failed. [ OLE DB status value (if known) = 2 ]

If I map a dummy value for this column then the error doesnt occur. Any help would be appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

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
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