Hello,
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.
ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,
Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.
I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.
At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.
I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.
Thanks again,
JB|||Yeah, you've got bad data...
I would load the data to a table of a varchars...like you have now
Think of it as a staging table
Then audit the data...
For example
Col030 should be a date
Find out all the rows with bad dates
SELECT * FROM myTable99 WHERE ISDATE(Col030)=0
Will show you rows sql doesn't consider a date...
Same with numerics
ISNUMERIC(col1)=0
You won't be able to load those rows to your final destination table
either fix the input file, or ignore the rows...
in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...
and wait a week while they try to figure out what to do...
Golf, Tennis, Long lunches...whatever you want...|||Brett,
You are correct it was bad data. That should have been the first place I looked.
I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.
Anyways, the problem is solved.
Thanks,
JB
Showing posts with label importing. Show all posts
Showing posts with label importing. Show all posts
Wednesday, March 28, 2012
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Problems importing data into sql server
I have a table for authors (for our bookstore) that has several fields
(firstname, lastname, etc.) and an author_id field (set as identity)
(firstname, lastname, etc.) and an author_id field (set as identity)
I'm trying to import a spreadsheet into this table, but keep getting
error messages that say I can't import data into the author_id field
(the identityf field).
Can someone suggest what I can do to overcome this?
Thanks,
Bill1) Import the data into a new flat table (ie: without indexes).
2) Run a report on the key field (identityf) -- there must be no duplicates.
select indentityf, count(*)
from newtable
group by identityf
having count(*)>1
should yield no results.
3) Correct the spreadsheet.
4) reimport the spreadsheet.
"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
> Thanks,
> Bill|||"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
<snip
Use the DTS. On the screen that allows you to select the tables, click the
Transform button. On this screem, you'll see 'Allow Identity Insert'.
BV
www.iheartmypond.comsql
Problems importing data from an ODBC source
I'm having an odd issue importing data from an ODBC source using a DataReader Source.
There's data in the source table, and if I use a non-SSIS tool to query the source, all the data is visible. However, if I use the SSIS DataReader source, It returns no rows, and throws no error.
Any ideas for figuring this one out? This is affecting about 10% of the tables from this data source, and it's our ERP application, so there's limits on what I can do to the source schemas.
Thanks!
What types of colums are contained in these tables?Problems importing backup DB into Express
I am trying to import (unless there is a better way) a database (which I made a backup of) from a SQL Server 2005 v8.0 machine into my SQL Server Management Express 2005 machine so that I can have a full complete copy of the backup database in express. The purpose is so that I can treat it as a template, get rid of tables, views, columns I dont need and repopulate them with different data to compliment my other project, which involves auto tagging of .mp3's.
As an analogy: copy an Excel spreadsheet, open it in Excel, delete cells you dont need, keep the rest but fill in with different data. Kinda like that but with SQL.
I can't figure out exactly how to do this with Express 2005. How do you import or otherwise a backup database from SQL Server 2005 v8.0 and get it into Express?
My backup file is called: WebBackup_KUOW_Program_Full
I've tried renaming it using both .mdf and .bak when I Right-Click Databases and select Restore. Fill in everything then click ok. I keep getting this error message:
TITLE: Microsoft SQL Server Management Studio Express
Restore failed for Server 'INTRA\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\kuow_program.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
BUTTONS:
OK
Can anyone help me with this please! Keep in mind I'm not a SQL guru. Thanks in advance for any assistance.
Thats pretty easy, the original data and log file is stored in the backup, if you want to store the file in another path than it was originally placed in (e.g. if you don′t have that drive letter on the new machine) you will have to change the path in the restore dialog. The data and logfiles will then be placed at the specified location.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Problems importing a datetime from a flat file
I have a flat file with a datetime field as follows
12419,1,'T','P',229.72,2,'N',2004/may/05 19:47:42.546001
12419,1,'T','R',605.38,76,'N',2004/may/05 19:47:42.546000
12419,2,'T','P',110.49,2,'N',2004/may/05 19:47:42.546003
12419,2,'T','R',215.53,11,'N',2004/may/05 19:47:42.546002
12419,3,'T','F',9.29,1,'N',2004/may/05 19:47:42.546005
12419,3,'T','R',696,38,'N',2004/may/05 19:47:42.546004
I can NOT get last field i.e. the date time field to import into SQL werver 2005 using import wizard.
I can manually enter the value in the table so I know it can handle data in the format it is in.. (table field is date time) but I cant get import wizard to read the file and bring in the data.. It bombs at coloumn 7... which is the date field.. It works fine if I delete the milisecond portion i.e. anything after the dot but I need to keep that information.
Any help will be greatly appreciated.
What type does the SSIS package think the column is? DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?
What errors so you get?
Please provide more information.
I strongly suspect that the value is not in a format that SSIS can understand. Your ability to type the value into a column is irrelevant - the informaiton is not stored this way
-Jamie
|||
I have tried with all column options i.e. DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?
In every instance it comes back with error message saying
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 4" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
When I remove the miliseconds i.e. anything after the decimal point in date time field it works fine.. It also works fine if I have the datetime in the following format
'2004-05-05 19:47:42.547000000' Instead of 2004/may/05 19:47:46.562003
Hope this helps
Thx
Amit
|||This behaviour is as expected. SSIS will not recognise yyyy/mon/dd hh:mi:ss:XXXXXX. That is not a valid format.
You will need to import it as a string and do a manual conversion using the Derived Column component.
-Jamie
|||
Thank you for your response Jamie. You confirmed my fears. I find it rather surprising that SSIS will read upto seconds but will not read milliseconds portion.. Anyway.. weird are the ways of microsoft and/or other software vendors.. :) Every software has its quirks.. Wish they didnt..
Thank you though for all your help
Thx
Amit
|||Well those aren't milliseconds. A millisecond is a thousandth of a second hence it will read to hh:mi:ss:XXX but not hh:mi:ss:XXXXXX and I dare say you won't find any other tool that will either!
-Jamie
Subscribe to:
Posts (Atom)