Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 911
  • Last Modified:

SSIS SQL 2005 Data Conversion

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
jjrr007
Asked:
jjrr007
  • 25
  • 17
  • 2
1 Solution
 
Goodangel MatopeSoftware ArchitectCommented:
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
 
Goodangel MatopeSoftware ArchitectCommented:
In your particular case, try setting the destination columns to NVARCHAR(255) or even bigger to see if the problem continues
0
 
ZberteocCommented:
What type of file is that, csv, xls or txt?
I can show you an easier way to import from files.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jjrr007Author Commented:
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
 
ZberteocCommented:
And how do you delimit the columns/rows in the file?
0
 
ZberteocCommented:
Also is important to know the size of the columns/row in the text file. Can you use csv formatted files?
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
Thanks for your assistance.  The file is comma delimited.  What steps should I take, to have this work?
0
 
jjrr007Author Commented:
The text file is properly formatted as a CSV file and the data is in the right columns.

What do you suggest?
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
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
 
ZberteocCommented:
Try to simply save it as filename.csv
0
 
ZberteocCommented:
Rename it form filename.txt to filename.csv that is.
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
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
 
ZberteocCommented:
does the SELECT return rows from the file?
0
 
jjrr007Author Commented:
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
 
ZberteocCommented:
is you machine a 64 bit one?
0
 
jjrr007Author Commented:
I tried the reconfigure command and then retried the query and received the same error. What do you suggest?
0
 
jjrr007Author Commented:
Yes,  I think OLEDB works.
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
yes, it's a 64-bit version to be more clear.  Thanks.
0
 
jjrr007Author Commented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
jjrr007Author Commented:
The command shell worked- it placed the data in a tem table.  Now, I'm working on parsing it out.  
0
 
ZberteocCommented:
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
 
jjrr007Author Commented:
Nice work. How do i run this function?
0
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
jjrr007Author Commented:
Aweseome job!
One last thing, how do I insert the data into a table using this query?
0
 
jjrr007Author Commented:
Inserting the data into an existing table, is why I created the dts package.

Thanks again!  We are almost there!
0
 
ZberteocCommented:
I already posted that.
0
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
jjrr007Author Commented:
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
 
ZberteocCommented:
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
 
jjrr007Author Commented:
You did a wonderful job!  I really appreciate your time and effort.  Thanks again!!
0
 
ZberteocCommented:
Glad I could be of help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 25
  • 17
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now