Timestamp Inserts into SQL Server results in Warnings
Posted: Tue Aug 04, 2009 1:38 am
This is a complex issue to describe relating to treatment of timestamp data, but after hours of A/B testing hopefully I can be concise.
to this point, I have exhausted all conceivable options and still this is not working without warnings. Basing new development on the exact same approach used in the past by other developers is not working....
This is my first time working with SQL Server as a target.
I am working in a SQL Server environment, Source & Target, using also a SQL Server Staging area. ODBC has been selected as the standard connection for SQL Server. I am using Hash Files as intermediary and staging points
Extraction from Source to staging:
I extract datetime format from SQL Server, DS metadate defined as char(16) & Data Element SQL.SMALLDATETIME using SQL Server SQL (example):
convert(char(16), "TimeSheetDateFrom", 20)
The data passes through the flow jobs as Char(16) until the job that writes to the target SQL Server DB at which point I run into a problem...
1. If I define target metadata as Timestamp (23,3), Data Element not defined (no selection made), I get :
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][SQL Native Client]Invalid character value for cast specification
2. If I define target metadata as Char(23,3), Data Element SQL.SMALLDATETIME, I get a successful load, no warnings. However, if I try to view data, I get a Metadata mismatch warning :
MetaData mismatch on COLUMN.SCALE Expected = 3 Actual = 0
If I re-define the target metadata again as Char(23) Data Element SQL.SMALLDATETIME, I am able to view data without error, but get load warnings :
MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 3
At this point, I am out of options. I have faithfully tried to match exactly what has worked in the past for past development, but in my jobs I am getting these errors....
- Extract from ODBC source to ODBC target in Staging is not giving errors (view or load).
- Extract from ODBC source (staging) to the flow (4 jobs) yields the above load problems (view AND load, depending on configuration of the metadata) at the ODBC target
Any ideas would be welcome and appreciated.
to this point, I have exhausted all conceivable options and still this is not working without warnings. Basing new development on the exact same approach used in the past by other developers is not working....
This is my first time working with SQL Server as a target.
I am working in a SQL Server environment, Source & Target, using also a SQL Server Staging area. ODBC has been selected as the standard connection for SQL Server. I am using Hash Files as intermediary and staging points
Extraction from Source to staging:
I extract datetime format from SQL Server, DS metadate defined as char(16) & Data Element SQL.SMALLDATETIME using SQL Server SQL (example):
convert(char(16), "TimeSheetDateFrom", 20)
The data passes through the flow jobs as Char(16) until the job that writes to the target SQL Server DB at which point I run into a problem...
1. If I define target metadata as Timestamp (23,3), Data Element not defined (no selection made), I get :
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][SQL Native Client]Invalid character value for cast specification
2. If I define target metadata as Char(23,3), Data Element SQL.SMALLDATETIME, I get a successful load, no warnings. However, if I try to view data, I get a Metadata mismatch warning :
MetaData mismatch on COLUMN.SCALE Expected = 3 Actual = 0
If I re-define the target metadata again as Char(23) Data Element SQL.SMALLDATETIME, I am able to view data without error, but get load warnings :
MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 3
At this point, I am out of options. I have faithfully tried to match exactly what has worked in the past for past development, but in my jobs I am getting these errors....
- Extract from ODBC source to ODBC target in Staging is not giving errors (view or load).
- Extract from ODBC source (staging) to the flow (4 jobs) yields the above load problems (view AND load, depending on configuration of the metadata) at the ODBC target
Any ideas would be welcome and appreciated.