SSIS SQL 2005 Data Conversion

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.

In your particular case, try setting the destination columns to NVARCHAR(255) or even bigger to see if the problem continues

What type of file is that, csv, xls or txt?
I can show you an easier way to import from files.

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!

And how do you delimit the columns/rows in the file?

Also is important to know the size of the columns/row in the text file. Can you use csv formatted files?

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.

Thanks for your assistance.  The file is comma delimited.  What steps should I take, to have this work?

The text file is properly formatted as a CSV file and the data is in the right columns.

What do you suggest?

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.

Excuse me the source is a text file which is deliminited by commas.  What do you suggest?  I really need a solution quickly.  Thanks!

Try to simply save it as filename.csv

Rename it form filename.txt to filename.csv that is.

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.

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.

does the SELECT return rows from the file?

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.

is you machine a 64 bit one?

I tried the reconfigure command and then retried the query and received the same error. What do you suggest?

Yes,  I think OLEDB works.

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

yes, it's a 64-bit version to be more clear.  Thanks.

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?

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.

Here is a link about that last suggestion:

http://www.simple-talk.com/sql/t-sql-programming/importing-text-based-data-workbench/

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.

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.  

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.

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.

The command shell worked- it placed the data in a tem table.  Now, I'm working on parsing it out.  

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 ### --

Select allOpen in new window

Nice work. How do i run this function?

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.

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.

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

Aweseome job!
One last thing, how do I insert the data into a table using this query?

Inserting the data into an existing table, is why I created the dts package.

Thanks again!  We are almost there!

I already posted that.

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.

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.

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)?

You did a wonderful job!  I really appreciate your time and effort.  Thanks again!!

Glad I could be of help.