string to DATE failing when appending to a table.
-
Hi All,
I ran and import from a flat file with a string date format 'dd/mm/yy', and converted to 'Date' type duing the import, and it ran fine while creating a new table. When i tried to 'append' the same flat file to the created table, it has failed on the copying stage, with the errors, description:
"Conversion failed when converting date and/or time from character string.".
even though, both conversions contained the same source and destination types. (Note there are no indexes, or identies, and when exported the dataset from the file as a flat file, and appended it, it worked fine.) Also when i use the DATETIME type, it works fine both in creating the table, and appending the data.
Is there a step i'm missing here.
Thanks in advance for your comments.
Regards
Gerry Abbott.
-
We're missing a few critical details here. When you say you ran an import of a flat file, exactly what process did you use? You then say you tried to append the same flat file to the newly created table... again, how, exactly? and in very specific detail, please. We can't know exactly what you mean because those words could mean a range of very different processes. It may simply be that what you expect out of a given process isn't something that would be possible to repeat because of the exact sequence of events involved, or some kind of constraint that is a natural byproduct of that sequence.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, October 24, 2018 8:34 AM
Thanks for the reply. I hope this quick video demonstrates the issue (easier than creating a sequence of printscreens. You should be able to scan it quickly by changing the speed of the video.
https://youtu.be/7RSxjRgbi6k0:00 source data file.
0:20 TASK IMPORT.
0:53 SELECT DATA SOURCE
1:05 CHOOSE DESTINATION
1:30 EDIT MAPPINGS, VARCHAR -> DATE 5 COLUMNS
2:18 RAN THE PROCESS.
2:35 VIEW THE DATA.
3:15 Repeat of the process above to append to the same table
5:00 RAN PROCESS. Failed to copy. display error message.
5:47 DROPPED THE NEW TABLE, to restart the test using VARCHAR -> DATETIME
7:53 Displayed the 4 imported records.
8:07 Ran the import again, into the existing table dbo.Import1 (same mappings VARCHAR - > DATETIME)
8:55 RUN PROCESS
9:10 DISPLAYED RESULTS 8 RECORDS. - FishD - Wednesday, October 24, 2018 9:30 AM
Sorry, but posting a link to a video isn't going to cut it. There's no guarantee that those instructions on "how to" are either a) accurate, b) complete, or c) sufficiently detailed that any two given people will actually perform the task identically. You are going to have to be specific. You may not need screen shots, but you do need to provide the EXACT code that you tried. It's not worth troubleshooting your problem until you do. You should post all the DDL, too, meaning CREATE TABLE and INSERT statements for sample data. If that's too much to ask, then this is not the right forum for you. You need to help us help you. Don't be so afraid of work that you avoid posting code just because it's "easier for you". We do expect you to do some of the heavy lifting...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
OK, I'll try again in the hope that it provides more calrity on the issue I am having.....
I attach 3 files.
- The csv data file, Import1.csv (attached here as a txt file).
- The SSIS file, ImportSSIS.dtsx (attached here as a txt file).
- The SSIS file, AppendSSIS.dtsx (attached here as a txt file).
Both of the SSIS processes use the same datafile as a source. (you will need to change the source data file path to your local location if you wish to test the dtsx files, line 44).
I added the script at the end to create the database. However I created it by right clicking on databases, and selecting 'New database'.
Server Version is Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
Jun 10 2015 03:35:45
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 15063: )A description of the steps follows,
The create table from file process. From SSMS,
RIGHT CLICK 'TESTDATABASE'
CHOOSE TASKS, IMPORT DATA.
SELECT 'FLAT FILE SOURCE' as the datasource,
BROWSE to the file to be imported. (Import1.csv, attached in the post as Import1.txt),
on datasource page, default values, text qualifer none. format delimited.
column names as first row.
NEXT, NEXT,
SELECT microsoft OLE DB Provider for SQL Server. TestDatabase as the database
NEXT
Screen displays C:\users\Gerry\Documents\Import1.csv as source.
Screen displays [dbo].[import1] as the destination on server OFFICE.
SELECTED EDIT MAPPINGS.
CHANGED MAPPINGS ON 5 VARCHAR COLUMNS TO date. CLICKED OK
'Create destination table' check box checked. CLICKED OK.
CLICKED NEXT, NEXT.
'Run immideately' selected.
'Save SSIS package' checked. (Saved package is ImportSSIS.dtsx, attached to post as ImportSSIS.txt)
CLICKED OK.
process completed successfully, and checked the table contained records. Screenshot 1 below
screenshot 1The append to table from process file. From SSMS,
RIGHT CLICK 'TESTDATABASE'
CHOOSE TASKS, IMPORT DATA.
SELECT 'FLST FILE SOURCE' as the datasource,
BROWSE to the file to be imported. (see screen shot 1)
on datasource page, default values, text qualifer none. format delimited.
column names as first row.
NEXT, NEXT,
SELECT microsoft OLE DB Provider for SQL Server. TestDatabase as the database
NEXT
Screen displays C:\users\Gerry\Documents\Import1.csv as source.
Screen displays [dbo].[import1] as the destination on server OFFICE.
SELECTED EDIT MAPPINGS.
No change to mappings.
'Append rows to the destination table' check box checked. CLICKED OK.
CLICKED NEXT, NEXT.
'Run immideately' selected.
'Save SSIS package' checked. (Saved package is AppendSSIS.dtsx, attached to post as AppendSSIS.txt)
CLICKED OK.
Process did not completed successfully, step 'copying to dbo.import1' shows errors. (see screenshot 2 for error message.)Screenshot 2
USE [master]
GO
/****** Object: Database [TestDatabase] Script Date: 25/10/18 00:16:24 ******/
CREATE DATABASE [TestDatabase]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestDatabase] SET COMPATIBILITY_LEVEL = 120
GO -
I have seen this issue where a change of data type to datetime solves a insertion issue with date data. I just chalked it up to a Sql Server anomaly.
I might try some experimentation on this to see if there is something behind it all.----------------------------------------------------
-
Cheers,
I'd be interested in what you find. -
Using your data, I was able to successfully import to a table with date data type. Ahead of the import, I specified the three columns shown as [DT_dbDAte] types. I saw no warning messages. I then tried again without doing this. And it worked again (maybe because I already had data in the table). So specifying the data type helped me to avoid the warnings and imported.
----------------------------------------------------
-
Many thanks Martin,
That was the missing step, setting the date type in the advanced tab of the source data. I had to do it for both the table creation step, and the append records step. That' not a problem, since it can be automated. -
Glad to know that it worked for you.
Cheers.----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply