Date data type used in data service

[Former Member]

After data services jobs are imported to new environement, Windows Server 2008, it failed because of below error.

<[Microsoft][ODBC SQL Server Driver] Optional feature not implemented>.

I found one recommendation to change "date" data type to "datetime" and it resolved an issue.

However, it caused anither issue.

when a template table is recreated, a column updated as datetime is populated as datetime2. How can I resolve this issue?

[Former Member]

Try to delete and remap the column.

Thanks,

[Former Member]

Whats your DS version?

Change the connection from ODBC to SQL Client if the version is 4x.

[Former Member]

How can I check the version of DS?

I do not see the version info from help at the Data Services Designer.

In addition, from the Data Service Designer, it was disabled to change database type from ODBC when I choose edit for datastore. Since it has many associated Tables and Template Tables, is there a easy way to swap the database type?

thank you in advance

[Former Member]

Menu > Help> About Data Services.

You will see the version 14.1. etc

Not at datastore level, you have to change at system level for 32 and 64 bit.

[Former Member]

I think it is old version.

     - Designer      12.2.3.0

     - JobServer     12.2.3.0

     - JobEngine     12.2.3.0

     - Repository     12.2.2.0000

[Former Member]

Ok thats the problem. Change the datastore connectivity from ODBC to SQL SERVER 2008 or which ever database you have. That will solve the issue.

You cannot edit the datastore, need to delete one and create it.

[Former Member]

Yes it should use SQL Server 2008.

I found that there are couple datastore which has a version setting as SQL Server 2005.

Do I need to replace all these to use SQL Server 2008? Is there a easy way to do so?

Frankly I am not sure whether a problem is related with this database connection issue or not. There is another issue that all date column is set to '1900-01-01' durign Data Flow even though these are actual date value. For instance, when a dimension table for date is created, all date field is set as '1900-01-01'. When a data type for column was date, it was okay. However, after updating it to datetime at template table, this weird issue occured. So I reviewed the template table and found that a column supposed to be a datetime is created as datetime2. The issue that sets datetime column as '1900-01-01' is related with the issue that data type changed to datetime2?

Thank you in advance!

[Former Member]

Change only the datastore that creates problem. Do you have access to DB tables? If so, edit the table and change the datatype to datetime and see.

[former_member187605]

You don't have to delete the datastore, you would have to drop all your tables first. Just create a 2nd configuration with the correct settings and make that the default.