November 4, 2014 at 2:22 am
Hi folks,
I've been running some SSIS packages to extract data from SAS using SAS Local Data Provider 9.3 as the connection provider. I'm able to extract the data but I'm getting some problems with certain values. One of the fields is in the format of 01Jan2014. The format of this column in SAS is defined as DATE9 (I'm not sure how that corresponds to any SQL formats). However, when I pull this data into an nvarchar column the value is displayed as a numeric e.g 19642.
I'm guessing that this is simply down to either the data provider or how SQL views this format. Initially when I connected to the data source (i.e. Data Flow Task, OLE DB Provider) it picked up this column as a decimal. I altered the output column value in the Advanced Editor to String or even Unicode string but it still came out the same way.
There are also a number of NULL values in the source table for this column and I know that SSIS can prove awkward around such values.
I'm going to try to alter the value on the source end to see if this resolves the issue but this is going to make a lot more work for me than I anticipated.
Has anyone come across such issues before?
November 4, 2014 at 1:55 pm
As I recall - you'd have to convert the SAS date values. per the 9.2 version of SAS documentation online, SAS stores its dates in # of days relative to 1960-01-01, so 0=January 1,1960.
Assuming that's true then "SQL date value" = dateadd(day,'1960-01-01',<datevalue from SAS>)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2014 at 2:30 am
Cheers Matt, worked a treat.
Don't suppose you have any experience with setting up a SAS data set as a linked server by any chance?
November 6, 2014 at 10:29 am
mitzyturbo (11/6/2014)
Cheers Matt, worked a treat.Don't suppose you have any experience with setting up a SAS data set as a linked server by any chance?
Sorry - do not. I have had to extract some data from SAS in the past - but not enough to be any real help. It's one of those sources I have to "hit the books" on when I have had to interact with it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply