September 12, 2007 at 2:03 am
Hopefully the forum can help me here.
We have a database we are moving from SQL Server 2000 to 2005. That much is fine.
However we have an existing DTS process to take data from a user supplied CSV file to import into a specific table. Taking the same CSV file and trying to run the Import wizard on SQL Server 2005 and we get data conversion errors with code 0xC0209077.
In terms of the Import wizard, we are simply following the prompted screens with all the correct values (e.g. Comma delimeted, CR/LF line feeds etc, "-ed text.
Anyone got any ideas on what might be wrong?
Many Thanks.
September 12, 2007 at 6:00 am
With BI-dev studio you can create a new project (BI / Integration services project) and then migrate the existing DTS-packages !
Project / Migrate DTS 2000 package
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2007 at 7:46 am
Been doing some further playing.
If we set the source file as an Excel file (.xls) rather than a CSV file. The method the business use to supply changes to the content remains the same.
Using the Import file wizard flagging source as Excel and other things being as you might expect, the import works fine.
Just got to get the business to a) Work with xls rather than csv and for them to ensure they don't use text values larger than the target column (bit of truncation going on!)
Many thanks for the reply anyway.
September 12, 2007 at 11:51 pm
You can also install the backwards compatibility tools for SQL 2005 that will allow your DTS packages to run within SQL 2005...
Joe
September 13, 2007 at 12:31 am
Indeed, that will work.
But the guidelines are to migrate the packages to SSIS properly.
They will migrate, even if that means having an incorporated dts2000 package!
If you use the migrations wizard, you'll see some packages will get migrated pretty easy and you don't need to modify anything, so you can focus on the packages the wizard says it could not migrate, so it incorporated the dts2000 version.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2007 at 5:08 pm
I've written quite a few SSIS packages to import/export flat-files to/from SQL Server 2005, but I don't recall receiving that error. Googling it, I found this info:
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE - The data value cannot be converted for reasons other than sign mismatch or data overflow.
I've found the following to work on even the most difficult of files and recommend giving it a try.
Depending on the size of your file, number of columns, etc., it may take some time for it to make its suggestions. Once it finishes, click on each column to view the data type and length (where applicable). Comparing the suggested types/lengths to the destination table of your failed import attempts, may provide the clues you're looking for.
Hope this helps,
Shane
Edit: Adding this...
Out of curiosity, I looked through some of the other results from Google. That error seems to come up often when the input contains dates. e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=349950&SiteID=1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply