Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS Loading Data from flat file connection

Posted on 2009-02-17
11
Medium Priority
?
368 Views
Last Modified: 2013-11-10
Im trying to use logic for a derived column. This is what I have...
[Column 53] == "00/00/0000" ? "01/01/1801" : [Column 53]
Its obviously wrong but I dont think by much.
Logic:
If [Column 53] = "00/00/0000" Then
   "01/01/1801"
ELSE
   [Column 53]
0
Comment
Question by:healthcheckinc
  • 6
  • 5
11 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23660597
The expression looks correct if your Derived Column is a DT_WSTR.
0
 

Author Comment

by:healthcheckinc
ID: 23660668
Well I tried that but why couldnt I keep it as a date. I would be setting it to "01/01/1801" if it = "00/00/0000" else it would be the vaild date??
0
 

Author Comment

by:healthcheckinc
ID: 23660674
it didnt work for DT_WSTR either though...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:HoggZilla
ID: 23660816
Please post a sample set of the date values vound in the file.
0
 

Author Comment

by:healthcheckinc
ID: 23661032
They are either a valid date 02/04/2008 or invalid date 00/00/0000. Is a derived column the best thing to use? What would your syntax be in the expression for this?
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1000 total points
ID: 23662741
Would a NULL date column be a good answer. If the file has zeros, return a NULL? OR do you want the date to be 1801?
0
 

Author Comment

by:healthcheckinc
ID: 23662774
I actually just made the row a string and made it a blank string if it had all zeros...It was the datatype that was messing me up I guess.... Thats waht you get for letting SSIS Suggest your data types for yuo. Thanks for your help and I will give you the points anyway...
0
 

Author Closing Comment

by:healthcheckinc
ID: 31547813
thanks
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23662836
Cool
Here is the Derived Column code to return a NULL date value or the actual date value.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)[Column 0]
First I get rid of all spaces, just in case. Then, if the value is zeros I set it to a NULL db_date value. If it is not zeros, I convert value to a db_date. In my example I tested, here are the values I get from a text file:
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
Here is the output. First the Original column, then the Derived Column second delimited by a comma.
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,

As you can see, the zeros are now NULL. Good Luck. Hogg
0
 

Author Comment

by:healthcheckinc
ID: 23662862
thanks man....I will need you in the near future so I will talk to you again...
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23662864
Actually, don't forget to get rid of the spaces in the last part either. Screen shot attached.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)TRIM([Column 0])

derived-with-null-date.bmp
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

810 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