Solved

SSIS SQL 2005 Data Conversion

Posted on 2007-12-03
44
900 Views
Last Modified: 2012-08-13
I'm working on a SSIS package that is taking data from a text file to a MS SQL 2005 database.  I'm getting an error saying,

Columns "column1" and "column1" cannot convert between unicode and non-unicode string data types.

After receiving that error, I have added a data conversion process to convert the colmn to a unicode text stream. When I use that new column, I'm receiving the following errors:

[OLE DB Destination [2986]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [2986]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "OLE DB Destination" (2986) failed the pre-execute phase and returned error code 0xC0202025.

I have the destination columns set to NVarchar(50).  What do you suggest that I do to resolve this?  Thanks.

Open in new window

0
Comment
Question by:jjrr007
  • 25
  • 17
  • 2
44 Comments
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 20397131
That error is caused when you try to insert data that is too big for a field, for example, inserting a field value of length 20 characters into a 10 character field, or inserting a value greater than 2^15 in an integer field. What you need to do is either increase your field sizes for character fields, or change to double precision or similar data type for numeric values. You can also get around this by trimming the data to the size of your field before inserting into the table.
0
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 20397148
In your particular case, try setting the destination columns to NVARCHAR(255) or even bigger to see if the problem continues
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397318
What type of file is that, csv, xls or txt?
I can show you an easier way to import from files.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20397486
The original file is txt file.  Any assistance you can provide would be much appreciated.

I have tried to increase the data size or show only some of the data. I still get the same error of cannot convert between unicode and non-unicode.  

Thanks!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397504
And how do you delimit the columns/rows in the file?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397534
Also is important to know the size of the columns/row in the text file. Can you use csv formatted files?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397563
This link might help:

http://www.sql-server-performance.com/articles/dba/import_text_files_ssis_p1.aspx

but still there are easier ways to import files. I am talking about well formatted files like xls or csv. If your text files are poorly formatted you can try the link above.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20397621
Thanks for your assistance.  The file is comma delimited.  What steps should I take, to have this work?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20397676
The text file is properly formatted as a CSV file and the data is in the right columns.

What do you suggest?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397759
Then your files extension should be .csv. If yes you can try this:

SELECT
      *
FROM
     OPENROWSET('MSDASQL',--provider name (ODBC)
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=C:\FileFolder\;Extensions=CSV;',--data source
        'SELECT * FROM FileName.CSV')


The file, FileName.CSV, has to reside on the same machine with the SQL server in a folder like C:\FileFolder\. You can change with your own names.


If the server is not set for ad-hoc queries (OPENROWSET) run this first:

sp_configure 'show advanced options',1
RECONFIGURE WITH override  
go
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
go

You need permissions for this.

If the select works that to insert in your table do:

INSERT INTO YourTable
      (<fields_List_here>)
SELECT
      (<fields_List_here>) -- or * if you don't have column names
FROM
     OPENROWSET('MSDASQL',--provider name (ODBC)
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=C:\FileFolder\;Extensions=CSV;',--data source
        'SELECT * FROM FileName.CSV')

You make sure that the <fields_List_here> has the same number of columns like the SELECT or with whatever * returns.

0
 
LVL 1

Author Comment

by:jjrr007
ID: 20397777
Excuse me the source is a text file which is deliminited by commas.  What do you suggest?  I really need a solution quickly.  Thanks!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397785
Try to simply save it as filename.csv
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397796
Rename it form filename.txt to filename.csv that is.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20397828
One other thing:

The file has to have column headers/names. If don't it will use the first row values as column headers. If it doesn't just insert the a first line with column names you want sepparated by commas. The lines have to be sepparated by <cr><lf> (cariage return),  one line one row, which I suppose they are.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20397869
I was able to change it to a csv.  

The files doesn't have column headers.  I can't manually add column headers because the process needs to be automated.  What do you suggest, regarding using the csv file?

If you have any other ideas by changing items in SSIS, please feel free to let me know. Thanks.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398048
does the SELECT return rows from the file?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398185
When I tried the select statement, I received the error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" has not been registered.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398223
is you machine a 64 bit one?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398230
I tried the reconfigure command and then retried the query and received the same error. What do you suggest?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398246
Yes,  I think OLEDB works.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398255
If your machine is 64 bit it won't work, because the MSDASQL driver is built for 32 bit. Itf that the case we're stuck but I found some threads on the net about this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=396615&SiteID=1
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398296
yes, it's a 64-bit version to be more clear.  Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:jjrr007
ID: 20398342
OK,  MSDASQL won't work.  How about changing the data type.  I found a webpage on Microsoft that discusses this at:

http://forums.microsoft.com/msdn/showpost.aspx?postid=188735&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

But, i don't know what steps to take.  What do you think?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398383
Try the link from few posts above with SSIS. You should still be able to build a package that would import the files. Personally I prefer thr OPENROWSET method because is more direct and works great on 32 bit machines. Onother way would by to import the file into on table with only one column of VAR(8000) type for SQL 2000 or VARCHAR(MAX) type for SQL 2005, and parse it into columns. You would use then:

CREATE TABLE #Textimport ( line VARCHAR(8000) ) -- or VARCHAR(MAX) for SQL 2005

INSERT  INTO #textImport
        ( line )
EXECUTE MASTER..xp_cmdShell 'Type C:\FolderName\FileName.CSV'

this will load the table into the #Textimport  where you would have to parse the line field.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398392
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398416
Regarding the SSIS, in .NET there is not default convertion but only explicit. Probably if you find the code that does the transformation and explicitly cast the varchar columns to Nvarchar would help. Unfortunately I have no experience with SSIS but with more research you should be able to find a way.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398548
What about using XP_cmdshell for what you recommended earlier:

SELECT *
FROM
     OPENROWSET('MSDASQL',--provider name (ODBC)
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=C:\FileFolder\;Extensions=CSV;',--data source
        'SELECT * FROM FileName.CSV')

How would I change that to do this?  I'm just hoping I can avoid having to parse out the text.  
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398607
You can't. The XP_cmdshell only executes DOS commands and returns to SQL whatever output they generate. When you execute

EXECUTE MASTER..xp_cmdShell 'Type C:\FolderName\FileName.CSV'

what returns is the same thing you would run in command promt:

C:\>Type C:\FolderName\FileName.CSV,

which is the content on the file, line by line, but as a text and not sepparated in columns. You'd have to parse.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398654
If you manage to do this:

CREATE TABLE #Textimport ( line VARCHAR(8000) ) -- or VARCHAR(MAX) for SQL 2005

INSERT  INTO #textImport
        ( line )
EXECUTE MASTER..xp_cmdShell 'Type C:\FolderName\FileName.CSV'

i can help with the parse pritty easily but it might run a bit slower.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398707
The command shell worked- it placed the data in a tem table.  Now, I'm working on parsing it out.  
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398752
Run the function I gie you in tyour database
CREATE  FUNCTION [dbo].[fnArray]

(

	@list varchar (8000), 

	@seppatt varchar(255), 

	@i int

)

RETURNS VARCHAR(255)

AS

	

-- ### Function Body Here ### --

BEGIN
 

	DECLARE 

		@index int,				-- internal variable to control the loop

		@return varchar(255)	-- will store the returning result
 

	-- initializing variables

	SELECT 

		@index=0, 

		@i=isnull(@i,0)
 

	-- check the data

	IF isnull(ltrim(rtrim(@list)),'')='' OR isnull(ltrim(rtrim(@seppatt)),'')='' OR left(@list, len(@seppatt))=@seppatt OR right(@list, len(@seppatt))=@seppatt

		SELECT @return='-2'	--wrong data , starts or ends with separator, space as separator

	ELSE 

	BEGIN 
 

		IF  @i>=0	-- make sure we have a valid index

		BEGIN 
 

			SELECT @list=@seppatt+@list	--add sepparator at start to match with the elements

			-- loop through string until the wanted element is reached or if the list is finished(no separator left)

			WHILE 1=1

			BEGIN 

				SELECT 

					@index=@index+1 ,

					-- every step the leftmost element is eliminated from the list

					@list=substring(@list,isnull(nullif(patindex('%'+@seppatt+'%',@list),0),len(@list))+len(@seppatt),len(@list))

				IF @index=@i OR  patindex('%'+@seppatt+'%',@list)=0 BREAK -- break loop 

			END 
 

			-- return the result depending of cases

			SELECT @return= CASE 

								WHEN @i=0 THEN cast(@index AS varchar)	-- return the number of elements

								WHEN @i>@index THEN '-1'				-- return out of range > max index

								ELSE substring(@list,1,isnull(nullif(patindex('%'+@seppatt+'%',@list),0)-1,len(@list))) -- return the element

							END 

		END 

		ELSE 

			SELECT @return='-1'	-- out of range <0

		END 
 

	RETURN @return /*

	SELECT @return --*/

	

END 

-- ### END Function ### --

Open in new window

0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398788
Nice work. How do i run this function?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398807
then run the following SELECT against your temporary table:

SELECT
      dbo.fnArray(line, ',', 1) AS Col1,
      dbo.fnArray(line, ',', 2) AS Col2,
      ...
      dbo.fnArray(line, ',', n) AS Coln
FROM #Textimport


n should be the number of columns you have in the csv(text) imported file. This function will extract every value from a coma sepparated list. It can also use other sepparators that can be a specific characcter, like comma in this case, but also a sequence of characters like {|} for instance. The sepparator has to be present in the list.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398843
It simulates an array like:

line is a list of values sepparated by a sepparator, comma in this case so:

      dbo.fnArray(line, ',', 1) will return the first value/element,
      dbo.fnArray(line, ',', 2) will return the second value/element
...

the first agrument is the list, the second is the sepparator and the third is the position in the list, like id in an array

if you use the function like:
      dbo.fnArray(line, ',', NULL)
NULL instead of position, it will return the list's dimsnsion, number of values/elements.



0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398952
Few more things:

1. In
EXECUTE MASTER..xp_cmdShell 'Type C:\FolderName\FileName.CSV'
doesn't matter what the extension of the file is, so you will not have to change them in the future.

2. Hopefully you will not have columns that have commas inside their value because will mess the data.

3. You can use the to insert the data into a real table but you would have to cast the values returned by the fnArray function to match the destination table:

INSERT INTO YourDestinationTable
      (<n_fields_list_here>)
SELECT
      dbo.fnArray(line, ',', 1) AS Col1,
      cast(dbo.fnArray(line, ',', 2) AS int) AS Col2, -- asuming the 2 column goes to an integer one in dest table
      ...
      dbo.fnArray(line, ',', n) AS Coln
FROM #Textimport

0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398979
Aweseome job!
One last thing, how do I insert the data into a table using this query?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20398995
Inserting the data into an existing table, is why I created the dts package.

Thanks again!  We are almost there!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20398999
I already posted that.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20399020
Check few messages upper.

You don't need a dts or SSIS package to run this import. Just build a stored procedure and run it or schedule it for run when you need.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20399055
One final thing, if the file you import is huge the SELECT statement with the fnArray function might take a while to run but normaly you should be OK.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 20399073
I tried that and received this message,

 "Msg 8152, Level 16, State 4, Line 9 String or binary data would be truncated. The statement has been terminated.  "

Do I need to put the data types in the part that says,  <n_fields_list_here> (that is line ten for me is INSERT INTO table)?
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 20399118
No, that message is because what you try to insert has a bigger size than the destination column. You need to apply the LEFT function to all the string type columns ike this:

Say the first column in your destination table is of varchar(50) type then use:

SELECT
      LEFT(dbo.fnArray(line, ',', 1),50) AS Col1, -- LEFT(..,50) will truncate to the dest field's size, 50
      cast(dbo.fnArray(line, ',', 2) AS int) AS Col2, -- asuming the 2 column goes to an integer one in dest table
      ...
      dbo.fnArray(line, ',', n) AS Coln
FROM #Textimport


you need to apply thsi to all your string columns however to make sure you avoid that error and you need to find the size for each dest column.

0
 
LVL 1

Author Closing Comment

by:jjrr007
ID: 31412385
You did a wonderful job!  I really appreciate your time and effort.  Thanks again!!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 20399242
Glad I could be of help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now