February 3, 2010 at 12:06 pm
Hi
I am pulling data from an AS400 into a SQL 2000 table.
Create table TEST
(
testdate varchar(50)
)
The Data that I am pulling into the table is of type datetime.
When the data is inserted into the table it looks like this: 2010-01-01
As soon as I pull the same data into a SQL 2005 table(that has the same table definitition), the data then looks like this: 2010-01-01 00:00:00
Is there any way I can get it to not show the time.
I also need the datatype of the column to stay as a varchar column and not be a datetime column
February 3, 2010 at 12:28 pm
I have several questions..
1. How are you moving the data? SSIS, DTS, Linked Server?
2. "When the data is inserted into the table it looks like this: 2010-01-01", How do you know that?
Observations:
1. The datatype is varchar so whatever is inserted is what will be stored, the time portion would have to be part of the insert.
2. The datatype isn't changing, the data is just text, it just so happens the text includes a time..
CEWII
February 3, 2010 at 12:40 pm
I Am using SSIS and DTS Respectively
When I use DTS and draw the data into the table it does not add the 00:00:00
When I use SSIS and draw the data into a table on a SQL 2005 that has the same table definition server it adds the 00:00:00
February 3, 2010 at 12:44 pm
This should work to convert the import to what you need
DECLARE @Sub VARCHAR(50)
SET @Sub = REPLACE((CONVERT(VARCHAR(50),GETDATE(),102)),'.','-')
SELECT @Sub
Result: 2010-02-03
February 3, 2010 at 12:45 pm
I'm going to guess and say that the field in the pipeline in SSIS is defined as a datetime? Adjust it's type to be a character type (not text though), you should be able to do this with the advanced editor.
CEWII
February 3, 2010 at 12:47 pm
Also
if i preview the data in DTS it shows the date like this 2010-01-01
When i use a dataviewer in SSIS it shows the date like this 2010/01/01 12:00:00AM
This happens for the exact same query, querying the exact same source.
And when I create a table from the source in both SSIS and DTS it gives me a datetime type for the column
February 3, 2010 at 12:55 pm
I'm not surprised by what you are saying. It is getting a datetime in both SSIS and DTS, however it seems DTS is dropping the time when it does the insert and SSIS does not, unfortunately in my view SSIS is doing the right thing.. However, with that said you have some choices, you can manipulate how the date is interpreted as it comes into the pipeline, or you can adjust the data IN the pipeline with a derived column or data conversion task. You can even modify the query to convert the data into character data, however I am unfamiliar with the syntax for an AS400 database to do this.
CEWII
February 3, 2010 at 1:00 pm
Yeah what you are saying is true.
I have manipulated the data as it comes in by converting to char and it works fine
My problem is, is that I am migrating from 2000 to 2005 and need the data to stay the same.
I am migrating over 600 dts packages so changing the source query for each one that is affected by this is quite a story 🙂
Though there might be a short cut of some sort.
Thanks for the help anyway
February 3, 2010 at 1:05 pm
With the as400 v5r3 and above there is a validate metadata function in the ado.net connection used by SSIS. When this is mapped to a SQL Server datetime field it automatically adds the time whether you want it or not. This is solve in 2008 where we have a date field.
I think the formatting solution shown above is the only safe one I have found and I do not recommend turning of the metadata validation as the as400 people are not always good at notifying us of changes.
February 3, 2010 at 1:30 pm
If you are migrating that many packages you might want to look at a tool like DTSxchange, I think it is called, by Pragmatic Works.
CEWII
February 3, 2010 at 1:37 pm
Rowan-283474
A rather over powering number of DTS packages.
Do all packages import the data into a single table or multiple tables?
If a single table is it a staging table and from the staging table is it inserted into the final table(s) If so, there might be a "short cut".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply