Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data Conversion and Derived Column issue

Hi all of you,

I think that I've done a big mess on my work... I've got plain file which must be loaded into a sql table. Up to there no problem, I use Derived Column due to columns needed be transformed with NULL, RIGHT, LEN, and so on...

But in this last package I've done half of work using Data Conversion but I've got five columns which would need be transformed but I don't know how can I do such thing. I can't connect a Derived Column from Flat File Source task, of course, it's already data conversion...

Let me know if you need further details.

Previously I think, silly idea, that it could be unified or better, that Data Conversion task allows me make transformations...

Thanks a lot for your ideas and thoughs,

Can't you just connect the output from the Data Conversion transform to the Derived Column transform, then connect it to your destination component?

|||

Yes, you're totally right. Everything's fine.

thanks