an interesting bug for SSIS

I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!

How can this happen?

I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.

Thanks.

Please provide more details about the package and the ODBCsource. When you say 'Time' does that mean the date is no part it?|||Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.|||I have no idea what a D3 database is; in my case I had to deal with an old PErvasive DB that had a similar case. It had separate columns/data type for the date and the time. I solved it by within the SQL query I use to extract the data. There was a native sql function that allowed me to 'concatenate' the date and time columns into a single column; then the data reader component could see it as a dateTime data type. Conclussion: try to resolve it right on the query.|||

Thanks.

I don't want to "concatenate" the date and time column and that table doesn't has date column at all!

The problem is table created by that package is with wrong data type. But DTS package in SQL 2000 created table properly. This is not about setting the query to select the data.

Can you show me how the query looks like, please?

|||

Qiuwei wrote:

Thanks.
Right. "Time" means date is not part of it.

My package detail:
ODBC source: it is D3 data base. I am using .NET Providers\Odbc Data Provider to connect to the data source.

destination: SQL2005.

Actually, all "Time" field is imported as "bigint"

Cheers.

This is most likely the provider stack. Are you using a different provider to what you used in DTS?

-Jamie

|||In my SQL2000 DTS package, I use "Other(ODBC Data Source)"

Thanks.|||

Which ODBC provider?

It could be that the dotnet provider for ODBC doesn't play very well with whatever ODBC provider you have. You may want to look into getting a different ODBC driver.

-Jamie

|||

Thanks.

Sorry for the late response (toob busy these days).

To get around this problem, we make the source data (time) as varchar type and imported as nvarchar.

Question is that why SQL server doesn't have "Time" data type which is quite useful?

Cheers.

|||

Qiuwei wrote:

Question is that why SQL server doesn't have "Time" data type which is quite useful?

many many people ask the same question. Which is why it (plus some other new things around dates and times) will be coming to a version of SQL Server near you very soon.

-Jamie

|||

That will be great!

Thanks.