I am using the SQL Server Import Wizard to import data from a Pervasive SQL database. The problem arises when I try to import the dates from Pervasive. I have tried mapping the date field as every possible data type (date, datetime, datetime2, varchar, sql_variant, etc.), but the wizard fails on all of them. Is there some other way to convert the date field to make it compatible with SQL Server so that I can import it? Thank you in advance.

Export the data (with the dates as is) to a flat file and then from this flat file to whatver destination your requirement is. As an aside: please always post errors and sample data.Arthur My Blog

It appears that Pervasive may store dates in decimal data type format. What you may need to do is use the option "Write a query to specify the data to transfer" instead of accessing the table directly. This way you should be able to use SQL Servers CONVERT function: CONVERT(datetime, CONVERT(VARCHAR(8), <pervasive column name>)) Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Thank you for the idea Jeff, I tried using the CONVERT function and it didn't work, however the CAST function is parsing just fine. However, there is some bad data (some test strings in the date field) so it will not import using the wizard. So I created an SSIS package that outputs the rows that have bad data. Now I am getting new errors: Error: 0xC0202009 at Data Flow Task 1, Source - Query [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Pervasive PSQL OLE DB Provider" Hresult: 0x80004005 Description: "[LNA][Pervasive][ODBC Engine Interface]Error in row.". An OLE DB record is available. Source: "Pervasive PSQL OLE DB Provider" Hresult: 0x80004005 Description: "[LNA][Pervasive][ODBC Engine Interface]Invalid date, time or timestamp value.". An OLE DB record is available. Source: "Pervasive PSQL OLE DB Provider" Hresult: 0x80004005 Description: "[LNA][Pervasive][ODBC Engine Interface]Expression evaluation error.". Error: 0xC0047038 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. I am brand new to SSIS, can you help me interpret this? I have searched online, but either nothing answers how to fix it or I just don't understand. Thank you

Let's go back to the Import Wizard for a few minutes. If there is bad data in the DATE column, you can just add a WHERE clause to your SQL WHERE ISDATE(CAST(<pervasive column name>) AS VARCHAR(8))) = 1 ISDATE returns 1 if the value is a valid date, 0 if it isn't Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker