Re: importing xls file - different attempt - one result....


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.DataPumpTransformCopy")
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@xxxxxxx> wrote in message news:ejcpfb$2it1$1@xxxxxxxxxxxxxxxxxxxxx
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.


.