> Wow!
>
> Many thanks for your speedy response!
>
>> So you have both a tab delim file and an Excel Spreadsheet?
>>
> Yes, I'm dealing with the original tab delim text file and then
> imported the data into an Excel spreadsheet to attempt to do some
> calculations with the raw data.
>
> Because the original source (flatfile) has strings of varying length
> in several columns, we are noticing that Excel is dropping characters
> beyond 255...which is no good for our db. So, we don't think we can
> use the Excel file as the source for the import.
>
> We would prefer to import from the tab delimited flat file.
>
> Therefore, we attempted to run the SSIS on the flat file and on the
> excel datatable...but we were unsuccessful on both attempts!
>
> The unicode error is popping up. We're unclear whether we've
> appropriately matched the destination column data types in the SQL
> Server DB.
>
>> For the Excel Source Adapter when you have set it up go to the
>> advanced editor and look at what the Output column datatypes are.
>> DT_WSTR == UNICODE.
>>
> Does that mean our destination column data type is supposed to be
> char(10) or nchar(10)? I thought nchar(10) was unicode and that the
> SSIS will convert the string into the datatable?
>
>> Now you can either change it in here or you can use a Data Conversion
>> Transform to do it for you in the pipeline. DT_STR is NON-UNICODE.
>>
>> Another way to check the datatypes that the pipeline expects is when
>> you are doing the mappings between columns if you hover over the
>> column at which you want to look it will tell you what it thinks the
>> datatype is.
>>
> Yes, we are transforming a string (NN.NN) (representing a dollar
> amount) to a number (NN.NN) in the database....which have selected
> numeric(4,2) Is that a correct datatype?
>
>> If you chage the datatypes after designing with a different on it may
>> not refresh so you may need to redo the transform/destination/source
>> from scratch.
>>
>> For the Flat File. If when setting up your Flat file connection
>> manager you go to the advanced tab there is a button there for
>> "Suggest Types". You can either use this to help figure out what the
>> datatypes of the file should be or you can go through and change them
>> yourself. By default I think they are DT_WSTR
>>
>> For the Flat File in the pipeline you could also use a Data
>> Conversion Transformation.
>>
>> Allan Mitchell
>> www.SQLDTS.com
>> www.SQLIS.com
>> www.Konesans.com
>>> Could you please post steps required?
>>>
>>> I am having the same problem using SSIS. I've been through the
>>> documentation on line for 2 days and have not been able to achieve a
>>> very simple process.
>>>
>>> I have an Excel spreadsheet and a tab delimited file.
>>> The tab delimited file contains various strings representing both
>>> text
>>> and
>>> decimal values. I would like to import the data into a SQL Server
>>> 2005
>>> datatable which matches the column/row structure of the tab
>>> delimited
>>> file.
>>> I am also receiving the exact same errors the other guys are
>>> reporting, but my column types appear to match.
>>> There is no documentation that I can locate to describe how to port
>>> the data.
>>>
>>> Please post the methods to:
>>> 1. Match the source file tab-delimited columns to the
>>> transformation
>>> process.
>>> 2. Match the transformation variables to the SQL datatable's
>>> variables.
>>> - Ken
>>> "Allan Mitchell" wrote:
>>>
>>>> Hello Todd,
>>>>
>>>> Before you do anything double click on the path between the source
>>>> and the destination. Have a look at what the path metadata thinks
>>>> is coming from the source.
>>>>
>>>> What i would do is remove any paths between the Source and the
>>>> destination Now add back a path.
>>>>
>>>> In the Advanced properties for both the source adapter and the
>>>> destination adapter have a look at the columns that are giving you
>>>> the problem.
>>>>
>>>> My guess is that one of them is
>>>>
>>>> DT_STR and the other is DT_WSTR
>>>>
>>>> You would use a Data Conversion transform to do the conversion
>>>> between datatypes
>>>>
>>>> Allan Mitchell
>>>> www.SQLDTS.com
>>>> www.SQLIS.com
>>>> www.Konesans.com
>>>>> I have two simple DTS packages in version 2000 that are about as
>>>>> simple as they come. One extracts from a table and dumps into an
>>>>> Excel file, the other takes the Excel file and appends it into the
>>>>> table from which it came. These took all of about two minutes to
>>>>> create usine DTS in SQL Server 2000.
>>>>>
>>>>> I have tried to re-create them in SSIS 2005 and get errors when
>>>>> trying to run them. Using the Import/Export wizard, the packages
>>>>> get created OK. The first one, whcih extracts from a SQL Server
>>>>> table and dumps into Excel works OK. But the one that takes the
>>>>> Excel data and loads it back into SQL Server gives this error on
>>>>> any column defined as VARCHAR:
>>>>>
>>>>>> Column "Name" cannot convert between unicode and non-unicode
>>>>>> string data types. <
>>>>>>
>>>>> So, basicaly what I am seeing is that the wizard creates the Excel
>>>>> file and assigns the appropriate data types, but then cannot
>>>>> figure out how to get that same data back!
>>>>>
>>>>> I have tried using a Data Cpnversion transform but any data type I
>>>>> try to assign does not work.
>>>>>
>>>>> How do I get SQL Server to accept data from a Text column in
>>>>> Excel?
>>>>>