January 30, 2012 at 7:45 am
Hi,
I've got data from Perfmon into a CSV file and i''d like to design an SSIS package to import this data into a database. The first problem i've stumbled accross in trying to import this data is a date conversion problem.
See the screenshot attached.
When trying to convert a date in this format: 01/27/2012 14:43:04.790 to a normal SQL Server datetime data type i get an error that reads: The value could not be converted because of a potential loss of data.
I don't understand why the conversion won't happen, given that
SELECT convert(datetime,'01/27/2012 14:43:04.790')
seems to work just fine in SSMS. Can anyone help me work out why this import won't work?
Thanks,
Simon
January 30, 2012 at 8:01 am
That's a US format date. It could be that the account that is running your package interprets it as a European format date. Make sure that the language for the login on SQL Server is English (ie US English). Or you could try setting the locale in the connection string - I'm not sure whether that's possible or not.
John
January 30, 2012 at 8:13 am
Thanks for that John. All the components in the data flow are set to US English, my login is set to a default language of English, but doesn't specify the variant i.e. US/UK. I'm running SQL Server 2008R2 Developer edition.
The collation on the database that it is being imported to is: SQL_Latin1_General_CP1_CI_AS. I even tried changing my PC regional settings to US English from UK but that doesn't seem to have allowed me to get passed this error either.
January 30, 2012 at 8:58 am
Does it work if you import into a datetime2 type? Wondering if it's having a issue with the nanoseconds.
Edit: Scrap that, you need to use DT_DATE type, DT_DBDATE is day, month, year only. Hence the data loss warning.
January 30, 2012 at 10:14 am
Hi,
Just as an update it does appear to be a US/UK date related problem. A friend of mine has managed to get this package to run on his machine but there seems to be something strange going on, on my PC.
Thanks for your help so far. I'll crack it somehow. I've set all the components i can find to US English including:
The package
The data flow task
The flat file connection
The ole db connection string
Can't seem to find any other places to set it. If anyone thinks of anything let me know.
Thanks,
Simon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply