jjrr007
asked on
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.
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.
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.
I can show you an easier way to import from files.
ASKER
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!
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.
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.
ASKER
Thanks for your assistance. The file is comma delimited. What steps should I take, to have this work?
ASKER
The text file is properly formatted as a CSV file and the data is in the right columns.
What do you suggest?
What do you suggest?
Then your files extension should be .csv. If yes you can try this:
SELECT
*
FROM
OPENROWSET('MSDASQL',--pro vider name (ODBC)
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\; Extensions =CSV;',--d ata 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',--pro vider name (ODBC)
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\; Extensions =CSV;',--d ata 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.
SELECT
*
FROM
OPENROWSET('MSDASQL',--pro
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\;
'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',--pro
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\;
'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.
ASKER
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.
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.
ASKER
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.
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?
ASKER
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.
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" has not been registered.
is you machine a 64 bit one?
ASKER
I tried the reconfigure command and then retried the query and received the same error. What do you suggest?
ASKER
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
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=396615&SiteID=1
ASKER
yes, it's a 64-bit version to be more clear. Thanks.
ASKER
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?
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.
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/
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.
ASKER
What about using XP_cmdshell for what you recommended earlier:
SELECT *
FROM
OPENROWSET('MSDASQL',--pro vider name (ODBC)
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\; Extensions =CSV;',--d ata 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.
SELECT *
FROM
OPENROWSET('MSDASQL',--pro
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\FileFolder\;
'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.
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.
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.
ASKER
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 ### --
ASKER
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.
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.
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
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
ASKER
Aweseome job!
One last thing, how do I insert the data into a table using this query?
One last thing, how do I insert the data into a table using this query?
ASKER
Inserting the data into an existing table, is why I created the dts package.
Thanks again! We are almost there!
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.
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.
ASKER
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)?
"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)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You did a wonderful job! I really appreciate your time and effort. Thanks again!!
Glad I could be of help.