SSIS-Excel to Sql table column value greater than 255 not able to change it.

Question

i have a dataflow task which is in a for loop container to loop through the excel sheets of a particular folder based on Dealer ids.problem is one of the column value comments hav a datatype of varchar with length 2000 .

i am not able to change the column value to dt_NText as its says error output colums are associated with output columns.the present datatype for column comments is Unicode string [DT_WSTR]

The data flow task contains:

excel source

data conversion and

oledb destination

excel souce:

i am using a excel connection manager .im browing for the path. and my excel version is 2007.

properties of connection manger:

connection string:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\FTP_Files\BayCityChevrolet\Bay City export_2012-04-10_12-58.xlsx;Extended Properties="EXCEL 12.0;HDR=YES"; ( i hav given iMEX=1) but dint make any difference

i changed the type guess rows_>Decimal to 0...dint work

                                                changed it to 16 --dint work..

can anyone please tell me what i shoud be doing to increase the column value.i have looked at all the forums and tried the solutions..nothing is working..

please let me know what could be the possible solution for this

cuurent errors:

[Excel Source 1 [1441]] Error: There was an error with output column "comments" (1554) on output "Excel Source Output" (1449). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[Excel Source 1 [1441]] Error: The "output column "comments" (1554)" failed because truncation occurred, and the truncation row disposition on "output column "comments" (1554)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Excel Source 1" (1441) returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

PS:i am able to send data to sql tabledata from a differnt sheets where column length for comments is less than 255.

---

Answer 1

The excel driver determines the datatype on the first 8 columns... so you need a large text string within the first 8 rows. With type guess rows you can change that number 8, but you still need a big text within the first couple of records.

Imex=1 is for mixed dataypes only (string-values and int-values in a column).

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

the data has got many rows that have column value more than 255..could you please tell me if there is anyother way

---

akhila

Answer 3

Are those rows are in first 8 rows? As SSISJoost said , Excel reads first eight lines to define the datatype. 

Check this link

http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php

To change in Registry check this

http://www.sqlservercentral.com/Forums/Topic860660-147-1.aspx

Thanks

Aamir

---

http://sqlage.blogspot.com/

Answer 4

Thanks aamir but its still not working...no the col length  with 258 is the 23 rd row.

i hav changed the registry values to 0 ..but its not working

---

akhila

Answer 5

Can you just copy the large row to the first row in the file or is this a recurring process?

---

Chuck

Answer 6

Setting type guess rows to 0 will result in checking a couple of 1000 records (I think 65000), but if there is one file with no large text it will fail...

I often use a dummy row in this case and filter it out with a where clause or a conditional split.

And even better... stop using Excel for SSIS :-)

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 7

thanks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

can you pls tell me if there is any  way to solve this

---

akhila

Answer 8

thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

can you pls tell me if there is any  way to solve this

---

akhila

There isn't a 100% solution other then stop using excel within SSIS, otherwise you are stuck with the automatic guessing mechanism of the excel driver

1) manually move large text to top

2) dummy row with large text and filtering it out later on in the process

MSDN: Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see http://support.microsoft.com/kb/281517

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 9

thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

can you pls tell me if there is any  way to solve this

---

akhila

The way I would solve it is to convert the excel file into a delimited file.

---

Chuck

Answer 10

@SSisJoost

i inserted a large row in the first row of excel sheet.. but im still getting same error :(

---

akhila

Answer 11

chuck

will i need to save the excel file as space delimited or change list seperator to |

how will i change my current excel sheet to delimited..

can u pls tel me steps how to make the excel sheet to delimted and send it to sqltable

---

akhila

---

akhila

Answer 12

You could either do it the manual way - open excel and do a save as.

Or in a script do something like this:

http://www.c-sharpcorner.com/uploadfile/yuanwang200409/how-to-convert-xls-file-into-csv-file-in-C-Sharp/

or this:

http://stackoverflow.com/questions/2422212/simple-c-sharp-csv-excel-export-class

---

Chuck

Answer 13

thanks chuck  ill try converting to csv and load the data

---

akhila

Answer 14

@SSisJoost

i inserted a large row in the first row of excel sheet.. but im still getting same error :(

---

akhila

Hi akhila,

Did you re-opened the connection manager and Excel source after you added the dummy row? Otherwise the metadata will still be on dt_wstr 255...
But if it's possible to get CSV files instead... even better.

Joost

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 15

thanks ssisjoost..

ya i reopened the connection manger and excel source after changing it.but i was still getting the same error

i am converting the excelfiles to csv. :)

---

akhila

Answer 16

can any1 tell me where am i goin wrong..

i am getiing an error saying System.Data.OleDb.OleDbException = {"The Microsoft Office Access database engine could not find the object 'Bay City export_2012-04-10_12-5'.  Make sure the object exists and that you spell its name and the path name correctly."}

but the object exists in foldr

D:/Ftp_files/baycityChevrolet/'Bay City export_2012-04-10_12-55(1).csv

and sheetname is Bay City export_2012-04-10_12-5

when i add a watch i see they are taking correct values but im still getting an error

       

Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"

       

'Dim strConn As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & sourcefile & ";Extended Properties=""EXCEL.0;HDR=YES;IMEX=1;"

       

Dim Conn As OleDbConnection = Nothing

       

Dim wrtr As StreamWriter = Nothing

       

Dim cmd As OleDbCommand = Nothing

       

Dim da As OleDbDataAdapter = Nothing

       

Dim ds As New DataSet

       

Dim dt As New DataTable

       

Dim y As Integer

       

Try

            Conn =

New OleDbConnection(strConn)

            Conn.Open()

            cmd =

New OleDbCommand("SELECT * FROM [" + sheetname + "]", Conn)

            cmd.CommandType = CommandType.Text

            wrtr =

New StreamWriter(TargetFile)

            da =

New OleDbDataAdapter(cmd)

            dt =

New DataTable()

            da.Fill(dt)

           

Dim cnt1 As Integer = dt.Columns.Count

           

Dim cnt As Integer = dt.Rows.Count

           

For x = 0 To cnt

               

Dim rowstring As String = " "

               

For y = 0 To cnt1

                   

Dim rs As String = dt.Rows(x)(y).ToString

                    rowstring =

"\" & rs & " \"","

               

Next

                wrtr.WriteLine(rowstring)

           

Next

            Console.WriteLine()

            Console.WriteLine(

"Done! Your " + sourcefile + " has been converted into " + TargetFile + ".")

            Console.WriteLine()

       

Catch ex As Exception

            Console.WriteLine(ex.ToString())

            Console.ReadLine()

       

End Try

   

End Sub

End

Class

reffered source:http://www.c-sharpcorner.com/uploadfile/yuanwang200409/how-to-convert-xls-file-into-csv-file-in-C-Sharp/

---

akhila

Answer 17

In excel the sheet names always have a $ at the end

Try this:

New OleDbCommand("SELECT * FROM [" + sheetname + "$]", Conn)

---

Chuck

Answer 18

Do NOT use a script with the same OLEDB provider for Excel!!! You wil get the same result. If you want to use scripting then you need the excel interop dll.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 19

ssisjoost..thanks a lot for taking time...i am sorry but can you pls tell me what is excel interop dll..and how to use it

---

akhila

Answer 20

ssisjoost..thanks a lot for taking time...i am sorry but can you pls tell me what is excel interop dll..and how to use it

---

akhila

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.11).aspx

but it's not recommended/common to use this in Excel, because it requires an office installation and that's a big no no on a production server :-)

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 21

Do NOT use a script with the same OLEDB provider for Excel!!! You wil get the same result. If you want to use scripting then you need the excel interop dll.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Well, I'd try it and find out if it works.  The issue with using the driver is that it guesses at field sizes and passes on that info.  The driver actually sends the full field into the calling app and that is where the issue happens when the variable being populated from the field is not large enough.  Since the code he posted is just walking the rows and columns in the result set I think it'll actually work.

And he's already done 90% of the work - might as well try it.

---

Chuck

Answer 22

thanks chuck.i dont have the access to my office computer.i tried converting excel to csv from my laptop and i get an error saying System.UnauthorizedAccessException = {"Access to the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\Bay City export_2012-04-10_12-58' is denied."}

can you please give me the code to convert xlx to csv.

---

akhila

Answer 23

Your error:  {"Access to the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\Bay City export_2012-04-10_12-58' is denied."}

Indicates that you did not include the extension on your file name.  Provide your full code and it would be pretty easy to figure out what is wrong with it.

---

Chuck

Answer 24

'my code for script that returns work sheetname

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum


    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Help, press F1.
    Dim con As SqlClient.SqlConnection
    Public Sub Main()
        'con = New SqlClient.SqlConnection("Data Source=CarSqlServer;Initial Catalog=Zsample;Persist Security Info=True;User ID=sa;Password=whitehouse")
        'con.Open()
        'Dim cmd As New SqlCommand
        'cmd = New SqlCommand("select * from test_BCC", con)
        'Dim resut As Integer = cmd.ExecuteScalar
        Dts.Variables.Item("User::DealershipId").Value = 968
        Dts.Variables("User::FolderPath").Value = Dts.Variables("User::FolderPath").Value & "\" & Dts.Variables("User::DealershipId").Value.ToString & "\"
        Dim path As String = Dts.Variables.Item("User::FilePath").Value.ToString
        Dim fnPeices() As String = path.Split("\")
        Dim fileName As String = fnPeices(2).Split(".")(0)
        Dts.Variables.Item("User::WorkSheetName").Value = fileName
        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

-------------------------------------------------

'code for converting xlsx to csv

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Collections.Generic
Imports System.IO


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum


    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Help, press F1.

    Public Sub Main()
        Dim sourcefile As String = Dts.Variables.Item("User::FilePath").Value.ToString
        Dim attributes As System.IO.FileAttributes = System.IO.File.GetAttributes(sourcefile)
        If (attributes And IO.FileAttributes.ReadOnly) = IO.FileAttributes.ReadOnly Then
            attributes = attributes And (Not IO.FileAttributes.ReadOnly)
            System.IO.File.SetAttributes(sourcefile, attributes)
        End If
        Dim dest As String = Dts.Variables("User::FolderPath").Value & "CsvFiles"
        Dim worksheetName As String = Dts.Variables("User::WorkSheetName").Value.ToString & ".csv"
        Dim targetfile As String = dest
        convertExcelToCSV(sourcefile, worksheetName, targetfile)
        Dts.TaskResult = ScriptResults.Success

    End Sub
    Public Sub convertExcelToCSV(ByVal sourcefile As String, ByVal worksheetname As String, ByVal targetfile As String)

        'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"""

        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"


        'Dim strConn As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & sourcefile & ";Extended Properties=""EXCEL.0;HDR=YES;IMEX=1;"
        Dim Conn As OleDbConnection = Nothing
        Dim wrtr As StreamWriter = Nothing
        Dim cmd As OleDbCommand = Nothing
        Dim da As OleDbDataAdapter = Nothing
        Dim ds As New DataSet
        Dim dt As New DataTable

        Dim y As Integer
        Try
            Conn = New OleDbConnection(strConn)
            Conn.Open()
            cmd = New OleDbCommand("SELECT * FROM [" + worksheetname + "$]", Conn)
            cmd.CommandType = CommandType.Text
            wrtr = New StreamWriter(TargetFile)

            da = New OleDbDataAdapter(cmd)
            dt = New DataTable()
            da.Fill(dt)
            Dim cnt1 As Integer = dt.Columns.Count
            Dim cnt As Integer = dt.Rows.Count
            For x = 0 To cnt
                Dim rowstring As String = " "
                For y = 0 To cnt1
                    Dim rs As String = dt.Rows(x)(y).ToString
                    rowstring = "\" & rs & " \"","

                Next
                wrtr.WriteLine(rowstring)
            Next

            Console.WriteLine()
            Console.WriteLine("Done! Your " + sourcefile + " has been converted into " + TargetFile + ".")
            Console.WriteLine()

        Catch ex As Exception
            MessageBox.Show(ex.ToString())

        End Try

    End Sub
End Class

---

akhila

Answer 25

Public Sub Main()
        '"User::FilePath" = c:\testfiles\NSBY.xlsx
        '"User::FolderPath" = c:\testfiles\
        'User::WorkSheetName = Sheet1

        Dim sourcefile As String = Dts.Variables.Item("User::FilePath").Value.ToString
        Dim attributes As System.IO.FileAttributes = System.IO.File.GetAttributes(sourcefile)
        If (attributes And IO.FileAttributes.ReadOnly) = IO.FileAttributes.ReadOnly Then
            attributes = attributes And (Not IO.FileAttributes.ReadOnly)
            System.IO.File.SetAttributes(sourcefile, attributes)
        End If
        Dim dest As String = Dts.Variables("User::FolderPath").Value.ToString() & "CsvFiles"
        Dim worksheetName As String = Dts.Variables("User::WorkSheetName").Value.ToString

        Dim targetfile As String = dest & "\" & worksheetName & ".csv"
        convertExcelToCSV(sourcefile, worksheetName, targetfile)
        Dts.TaskResult = ScriptResults.Success

    End Sub
    Public Sub convertExcelToCSV(ByVal sourcefile As String, ByVal worksheetname As String, ByVal targetfile As String)

        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"

        Dim Conn As OleDbConnection = Nothing
        Dim wrtr As StreamWriter = Nothing
        Dim cmd As OleDbCommand = Nothing
        Dim da As OleDbDataAdapter = Nothing
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim x As Integer

        Dim y As Integer
        Try
            Conn = New OleDbConnection(strConn)
            Conn.Open()
            cmd = New OleDbCommand("SELECT * FROM [" + worksheetname + "$]", Conn)
            cmd.CommandType = CommandType.Text
            wrtr = New StreamWriter(TargetFile)

            da = New OleDbDataAdapter(cmd)
            dt = New DataTable()
            da.Fill(dt)
            Dim cnt1 As Integer = dt.Columns.Count - 1
            Dim cnt As Integer = dt.Rows.Count - 1
            For x = 0 To cnt
                Dim rowstring As String = ""
                For y = 0 To cnt1
                    Dim rs As String = dt.Rows(x)(y).ToString
                    If (y > 0) Then
                        rowstring = rowstring + ","
                    End If
                    rowstring = rowstring + """" & rs & """"
                Next

                wrtr.WriteLine(rowstring)

            Next

            wrtr.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString())

        End Try

    End Sub

Here you go.  This worked fine for me

---

Chuck

Answer 26

Thanks a looot for the code..

i am still getting this error

The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

the sheet in the destination folder is getting created but the data is not going into the sheet.

---

akhila

Answer 27

Thanks a loot SSIS jhoost..im still trying :)

---

akhila

Answer 28

Thanks a looot for the code..

i am still getting this error

The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

the sheet in the destination folder is getting created but the data is not going into the sheet.

---

akhila

Well you need to look at your particular .xlsx file and see what the name is at the bottom of the sheet.   Sheet1$ is the default - but could have been changed

---

Chuck

Answer 29

i renamed the sheet as sheet1 before execution.My folder properties are readonly.will that be a problem.itried unchecking the readonly option .but its not getting applied

---

akhila

Answer 30

Readonly should be fine.

---

Chuck

Answer 31

i still have the same error

The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

---

akhila

Answer 32

Since I do not have your excel file I've really got no way to figure it out - sorry.

---

Chuck

Answer 33

Did just notice a bug in the code that wouldn't cause the problem you were seeing - fixed it above

it used to say if (x> 0) Then

Should be

If (y > 0) Then
                       

---

Chuck

Answer 34

thats ok..thank you :)

---

akhila

Answer 35

chuck..thanks a ton..i created a dummy xlsx file..i think prob is with the data ... and this code runs perfect..thanks a looot for all the help  :)

---

akhila

Answer 36

thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

can you pls tell me if there is any  way to solve this

---

akhila

There isn't a 100% solution other then stop using excel within SSIS, otherwise you are stuck with the automatic guessing mechanism of the excel driver

1) manually move large text to top

2) dummy row with large text and filtering it out later on in the process

MSDN: Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see http://support.microsoft.com/kb/281517

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

I'm having the same problem.  My source file may or may not have text on any given day, with no limit on the text size.  This issue makes it impossible for me to import a file without knowing beforehand what the text length is!  How can it be that this built-in feature of SSIS is so nonsensical?

Answer 37

Liam.

i converted excel file to ccv.then i gave DT_Text as datatype for strings.i think it accepts upto 4000 charecters

---

akhila

Answer 38

Hi.  Thanks for your reply.

Do you mean you converted the Excel file to CSV then imported the CSV with SSIS?  Is this problem caused by Excel then?

Answer 39

for me the problem was caused by excel sheet .

it contained data with more than 255 characters

and excel input colums accepted only ST_STR which had a limit of only 255 characters.

i dint know handling it.

so i converted excel sheet into csv file and then imported the data the converison is done using code given by chuck

check the above post for code

---

akhila

Answer 40

I can't make sense of that code.  I'm no good with VB, I'm afraid.  My problem is that my text columns do not have a set size: they could be less than 255, they could be more, and I find it hard to understand why Excel/SSIS can't dynamically select between the two.  Instead, I have to choose whether my columns are WSTR or NTEXT, and if they don't match, the import fails.

Thanks for your help though.