Solved

how to drop bad dates from excel file

Posted on 2011-09-22
1
228 Views
Last Modified: 2012-05-12
Hi, what is the best way to drop a bad date within a ssis pkg.  I load data from an excel file into a sql server but sometimes the data is dirty, like a date field will come in ##### or 0222-01-01 00:00:00.000.  For these rows I want to load into tbl and the good rows into another tbl.  
I tried using the derived column task to make sure the data is actually a date, but get the following error.
Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s).        
0
Comment
Question by:elucero
1 Comment
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 36582722
Are you getting the error at the source or at the Derived column? What expression are you using in the Derived Column...

I Think it is enough to check the year pattern of the Date column using a range like [2][0][0-9][0-9] Or [1-9][0][0-9][0-9] or use Year(Date column) >= 2011
which will eliminate the bad records , you can also check for # using the Codepoint function in the expression
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question