I import data from xls file into a table using saved DTS Package. The lenght
of one field exceeded 300. I changed the lenght of the field in the source
table to 1000, but still there is an error as below:

Error at Source for Row number 156. Errors encountered so far in this task:
1.
Data for source column 7 ('CoolumnNane') is too large for the specified
buffer size.



I tried bulk insert:
BULK INSERT [DataBase].[dbo].[TableName]
FROM '\\ServerName\FolderName$\FileName.xls'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
DATAFILETYPE = 'char'
)

with such result:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (STATUS).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 31.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.



I also tried openrawset:
insert into [DataBase].[dbo].[TableName]
select * from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\ServerName\FolderName$\FileName.xls ';HDR=YES;IMEX=1'
,'select * from [Sheet0$]'
)

with such result:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].

The file: \\ServerName\FolderName$\FileName.xls is visible for the sql
server (exec master..xp_cmdshell N'dir
\\ServerName\FolderName$\FileName.xls' )


Kind regards.

Curiuos

Why not use the wizard to create the exact DTS package you need to import
XLS files?


below is the VB code from within a DTS file used to import XLS files that is
generated from SQL. Seriously thou. it is easier to just use the wizard to
set these up.

its very simple -
1. right click anywhere in the table menu and select import.
2. choose import from XLS file
3. point to the XLS file you want to import and click next
4. change the table location and make sure the transformations are in
place.
5. click next next next until you get the option to save as DTS.
give it a name and click finish.

its that simple.

if this isn't the solution your looking for then you need to be more
specific in your request.

hb


'************************************************* ***************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: TEST_TEST.bas
'Package Name: TEST_TEST
'Package Description: DTS package description
'Generated Date: 15/11/2006
'Generated Time: 10:51:36
'************************************************* ***************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "TEST_TEST"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0 ")

oConnection.ConnectionProperties("Data Source") =
"\\myserver\myfolder\myfile.xls"
oConnection.ConnectionProperties("Extended Properties") = "Excel
8.0;HDR=YES;"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "\\myserver\myfolder\myfile.xls"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your
password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "MYDATABASENAME"
oConnection.ConnectionProperties("Data Source") = "MYSQLSERVERNAME"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export
Wizard"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "MYSERVERNAME"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "MYDATABASENAME"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your
password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from excelsheet1 to [mydatabase].[dbo].[table1]
Step"
oStep.Description = "Copy Data from hcc$ to [mydatabase].[dbo].[table1]
Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from hcc$ to [mydatabase].[dbo].[table1] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Copy Data from excelsheet1 to
[mydatabase].[dbo].[table1] Task (Copy Data from hcc$ to
[mydatabase].[dbo].[table1] Task)
Call Task_Sub1( goPackage )

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing
package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'------------- define Task_Sub1 for task Copy Data from excelsheet1 to
[mydatabase].[dbo].[table1] Task (Copy Data from excelsheet1 to
[mydatabase].[dbo].[table1] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copy Data from excelsheet1 to
[mydatabase].[dbo].[table1] Task"
oCustomTask1.Description = "Copy Data from hcc$ to
[mydatabase].[dbo].[table1] Task"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "select `IP address`,`NetBIOS Name`,`MAC
address`,`F4` from `excelsheet1`" ' these are my field names, you could add
your own.
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[mydatabase].[dbo].[table1]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1( oCustomTask1 )


goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("IP address" , 1)
oColumn.Name = "IP address"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("NetBIOS Name" , 2)
oColumn.Name = "NetBIOS Name"
oColumn.Ordinal = 2
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("MAC address" , 3)
oColumn.Name = "MAC address"
oColumn.Ordinal = 3
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("F4" , 4)
oColumn.Name = "F4"
oColumn.Ordinal = 4
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("IP address" , 1)
oColumn.Name = "IP address"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("NetBIOS Name" , 2)
oColumn.Name = "NetBIOS Name"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("MAC address" , 3)
oColumn.Name = "MAC address"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("F4" , 4)
oColumn.Name = "F4"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub




Regards
hb. (a mear MCP at the moment)
:O(


"Curiuos" <curious (AT) mail (DOT) pl> wrote

Hello,
I created my DTS local package using a wizard.
It worked properly for half a year until the lenght of one text field in
excel file exceeded 300.
I changed the lenght of field in a source table to 1000 (much more then
necessary), but got sitll the same error:

Error at Source for Row number 156. Errors encountered so far in this task:
1.
Data for source column 7 ('ColumnName') is too large for the specified
buffer size.

I even created a new package from the very beginning, but still the same.

Regards.



Uzytkownik "hb21l6" <hb21l6 (AT) hotmail (DOT) com> napisal w wiadomosci
news:AA64AB0F-1D59-4F86-9DE1-6117E67947BE (AT) microsoft (DOT) com...

ahh.. you say you altered the field length on the Source ( xls file i
assume )
how about increasing the length of the field in the database table?

what field type are you using? char, varchar or nvarchar

also have a read of this article..
http://support.microsoft.com/kb/281517

Thanks
hb



"Curiuos" <curious (AT) mail (DOT) pl> wrote

Sorry - my mistake...
I changed the lenght in destination table, of course, to varchar(1000).
Thank you for the link and the answers.

Regards.


Uzytkownik "hb21l6" <hb21l6 (AT) hotmail (DOT) com> napisal w wiadomosci
news:E8875DB4-358F-4FE5-8CD7-47E8F1FC153D (AT) microsoft (DOT) com...