Solved

Convert JDE Julian Date in SSIS

Posted on 2011-09-28
36
1,510 Views
Last Modified: 2012-06-21
I have a JDE julian date that I need to convert to short date in a derived colums data flow task.

An example value of the date in the sql database is 111222. (08/10/2011)

The JDE julian date is 6 digits.  I can create a function in sql server to do this but the
dba doesnt want any functions in that particular db.

So I need to figure out how to do this in the IDE for SSIS.  Im not sure if there is a place to write
a custom function in the IDE or if I have to use the built in functions to try and convert.

See attached screen.
Capture.JPG
0
Comment
Question by:rochestermn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 14
36 Comments
 

Author Comment

by:rochestermn
ID: 36719939
Seems to me I need to use a script component in SSIS to handle the transformation of the julian date.

All the other fields should go straight in with little transform.  

Ive never used the script component before so im trying figure it out.   Im assuming you have to use either the script component or derive columns not both in the same data flow.


Capture.JPG
0
 

Author Comment

by:rochestermn
ID: 36815891
Im not allowed to use the script component.

So somehow i have to figure out how to convert a julian date back to a regular date.

The julian date is lets say 111222.

All that I have available to me in SSIS is dateadd, datediff, datepart, day, getdate, month and year functions.  Most of which require a date field as a parameter.

If anyone has any ideas I appreciate.  I am using two derived tasks to split the julian date
into  century = 1, year = 11, and day =222.

Thanks
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36816388
you can do this in two steps, meaning two derived columns thought it can be achieved using 1 derived column... the problem is it errors out while converting the string to date when try to do it using only one derived column ..so will have to use 2

1st Derived Column (OutputColumn as gldate, inputcolumn as Jdate)

(DT_STR,20,1252)(SUBSTRING((DT_STR,10,1252)Jdate,1,1) == "2" ? "20" : "19") + SUBSTRING((DT_STR,10,1252)Jdate,2,2) + "-01-01"

2nd Derived Column

DATEADD(D,SUBSTRING(@gldate,4,3),(DT_DBDATE)(gldate))

Let me know, if you have any issues..

p.s.i have used a case to check for century and the code is based on the sql here
 http://www.dbforums.com/microsoft-sql-server/1646454-sql-day-year-cyyddd-datetime-mm-dd-yyyy-conversion.html
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:rochestermn
ID: 36816512
Awesome response.  I will try to make this work thank you sir.
0
 

Author Comment

by:rochestermn
ID: 36816575
Here are the errors from derived #2.  Also to note the day parm in dateadd is "day"

See attached image.  derived #1 was free of error.

thanks again.
Capture.JPG
0
 

Author Comment

by:rochestermn
ID: 36816577
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36816606
change the expression  to DATEADD(D,SUBSTRING(gldate,4,3),(DT_DBDATE)(gldate))


i just changed @gldate to gldate
0
 

Author Comment

by:rochestermn
ID: 36816643
See the new errors
Capture.JPG
0
 

Author Comment

by:rochestermn
ID: 36816777
Looks like the 2nd part of the formula needs to be a numeric type.

so if you use substring it has to be cast I think.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36816834
My bad ..should have tested it before posting it..the below solution works

In Derived transformation 1 .. Add another column to derive the DayofYear - (DT_I4)(SUBSTRING(gldate,4,3)) and use this column in the Derived Transformation 2

DATEADD("D",DayOfYear,(DT_DBDATE)gldate)
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36816849
It should be jdate instead of gldate in Derive transformation 1

 DayofYear - (DT_I4)(SUBSTRING(jdate,4,3))
0
 

Author Comment

by:rochestermn
ID: 36816888
Ok I will try this adjustment and report back.  Thank you.
0
 

Author Comment

by:rochestermn
ID: 36816950
How do you test these expressions anyway?  Is there some way in the IDE?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36817012
in a package using two derived column transformations as i suggested
0
 

Author Comment

by:rochestermn
ID: 36892053
I mean how do you test the results or your function?  Im totally new to SSIS.  As a programer im used to having a way to test my code but dont see a way in SSIS.  For example you can type in function syntax  but how do you know its going to work if you cant execute it?

Ive almost got it but there are a few errors about converting unicode to string.
Error      1      Validation error. Data Flow Task OLE DB Destination [454]: Column "col1" cannot convert between unicode and non-unicode string data types.        JDE_Export_EBMS.dtsx      0      0      

Thanks.
0
 

Author Comment

by:rochestermn
ID: 36892137
Ive gotten rid of all errors.  I just need to know how to run it or preview it to see the results.

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36892187
convert the column in one of the derived transformation tasks by using (DT_STR,Length,1252) (col1)  ,name it accordingly in the column provided and map this to your destination.. Now, to look at the output all you need to do is rt click on the precedence container (arrow joining two tasks) and then select data viewers and Add a data viewer .

You can add it b/w the derived column and the destination to see all your columns before loading them into the table ..

Also, change the column data type according to your destination column.. if its NVarchar convert it using (DT_WSTR,Length)(col1), if varchar use the above exp

http://sqlblog.com/blogs/andy_leonard/archive/2010/03/08/ssis-snack-grid-data-viewer.aspx
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36892200
if you want to run the complete package ..Rt click on the package and select execute..

If you wan to run a Task (data flow task etc)..rt click on it and select Execute
0
 

Author Comment

by:rochestermn
ID: 36892295
I dont see an option to do that.  Also if I click Debug/Start Debugging it is disabled.
ie.  pressing F5 doesnt do anything.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36892359
Is the task disabled ? if its sql 2008 you can rt click on the empty space in the data flow task and run the task.. do you see the solution File and package in solution explorer window ?
0
 

Author Comment

by:rochestermn
ID: 36892729
I do not know but i am editing in the visual studio 2008 business intelligence IDE.

See attached image.
Capture.JPG
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36892838
hmm.. i see that you are missing the Project itself and is open in the design mode..which only allos you to edit the package, save the changes and if you want o run..you will need to run it by double clicking the .dtsx file from physical location..it would just run and states about the output..

you can actually follow the below link and run it from Visual studio
http://blogs.msdn.com/b/michen/archive/2007/03/15/ssis-debug-disabled.aspx
0
 

Author Comment

by:rochestermn
ID: 36892915
Ok.  I will try that thanks i had no idea.  Will let you know when this is good to go.
0
 

Author Comment

by:rochestermn
ID: 36892941
Ok this explains the missing project.

I had saved the .dtsx file out on the network and was editing it directly instead of opening the solution file.  See attached screen.
Capture.JPG
0
 

Author Comment

by:rochestermn
ID: 36892953
When I executed it ended but the data didnt go in the table I dont think.
Capture.JPG
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36892987
of course it did not because there were validation errors at the destination..  click on Progress tab beside Package explorer and look for error messages
0
 

Author Comment

by:rochestermn
ID: 36893411
Ok these are the errors.  
Capture.JPG
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36893561
open your OLE DB destination and review your mappings..as the error describes column meta data is Invalid.. check the data types of your source and destination Columns in the Columns tab of your destination task ...they nned to be same, if you change them .. rempa them again
0
 

Author Comment

by:rochestermn
ID: 36894300
Ok it ran but my dates are all in 1900s.


Capture.JPG
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 36894374
That is bcz of the Expression here - (DT_STR,20,1252)(SUBSTRING((DT_STR,10,1252)Jdate,1,1) == "2" ? "20" : "19") + SUBSTRING((DT_STR,10,1252)Jdate,2,2) + "-01-01"


This is a If else condition it is checking for 1st character and If its 2 then it returns 20 or else 19 ... change it to "1" ? "20":"19" or as you like
0
 

Author Comment

by:rochestermn
ID: 36903615
How do I change the dateformat to short date in this format:  10/3/2011?

Thanks again.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36903775
It is in a short date Format .. you cannot really mention the format of the date Type Field, I mean SQL treats all the dates as in one single date format .. my sentence might really not make sense but what I mean can be illustrated by an example below...Run this script in your SQL browser Window and see the output results for yourself..

you can the run the package as is ..and sql wll make the changes... Now, if the data type is not Date then we will need to change that in the Derived Transformation by replacing "-" with "/" and interchange the date format to DD/MM/YYYY
CREATE TABLE #D
(
D Date
)

INSERT INTO #D VALUES ('2011-03-03')
INSERT INTO #D VALUES ('02/02/2011')

SELECT * FROM #D

DROP TABLE #D

Open in new window

0
 

Author Comment

by:rochestermn
ID: 36920975
Point well taken.  Its fine.  Thanks for all your help you have done an amazing job for me and gone the extra mile and a half when most people typically dont.

Thank you very much sir!
0
 

Author Closing Comment

by:rochestermn
ID: 36920986
This guy went way above and beyond what is normal im very greatful for his help.  Awesome.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36924157
I'm glad I was able to help you
0
 

Author Comment

by:rochestermn
ID: 37228455
Im having difficulty getting this date in the format mm-dd-yyyy.  The fomula below puts it in yyyy-mm-dd

Can someone help?


(DT_STR,20,1252)(SUBSTRING((DT_STR,10,1252)Jdate,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_STR,10,1252)Jdate,2,2) + "-01-01"
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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