Solved

Import csv to SQL 2008, time column problem

Posted on 2013-01-14
4
1,664 Views
Last Modified: 2013-01-14
Am importing csv file to SQL 2008 using SQL Server Import and Export Wizard.
Only problem I seem to have is the time column, cannot use datetime.

SQL column is time(0)
CSV value is HH:MM:SS

Get error:
Found 2 unknown column type conversion(s)
The package will not be run.

Source Information]
Table: C:\Documents and Settings\jhinson.WAVERLYDOMAIN\Desktop\TermsCodes.csv
Column: Created_Time
Column Type: string [DT_STR]
SSIS Type: string [DT_STR]
Mapping file (to SSIS type):

[Destination Information]
Destination Location : (local)
Destination Provider : SQLNCLI10
Table: [dbo].[TermsCodes]
Column: Created_Time
Column Type: time
SSIS Type: database time with precision [DT_DBTIME2]
Mapping file (to SSIS type): c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

[Conversion Steps]
Conversion unknown ...
SSIS conversion file: c:\Program Files\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

On preview everything looks fine.
0
Comment
Question by:jhinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 500 total points
ID: 38776066
I tried it also and got the same error as you, with a time(0) column and HH:MM:SS formatted data in a CSV. Then I just converted the CSV to an Excel file and it imports fine, same data. Not sure how this helps you, it looks like it just is some kind of glitch in the importer.

EDIT: OK I figured out how to make it import CSV, On the screen for choose a data source there is an advanced tab on the left hand bar, click that and it opens a screen where you can define data type for each column in the CSV, and set the time column to "database time with precision".

Advanced tab
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 38776157
Let me know if that works.
0
 

Author Closing Comment

by:jhinson
ID: 38776238
That worked for the time. Thanks.
It appears there will not be a straight forward solution for me.
Am extracting files from an AS400, dumping to Excel (only choice is Excel 97).
Excel treats some columns as numeric when they are text, but mostly numerals.
The only reliable solution seems to be to dump to a txt file, open Excel 2010 on desktop and save as xlsx file.

But your solution did work, thanks
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 38776302
If you need an automated way to do it, you could try using a BULK INSERT statement in a stored procedure and dump the whole import file (raw from AS400) to a table that is all VARCHAR(255) columns with same number of columns, then have a stored procedure that does the converts from the "stage" table into the permanent table using the proper CONVERT for each column (and also will allow you to do some error checking and error handling).  And you can schedule this with SQL Server Agent.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question