Error: CREATE ASSEMBLY for assembly

I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.

I import “System.IO” and have therefore set the permission levels to EXTERNAL_ACCESS.

I am receiving the same error message that many folks have received.

CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.

The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.

My CLR access is “on”

I have tried

1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [Builtin\Administrators].

2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to “My Windows Authentication ID”.

3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON

4) In Visual Studio .NET 2005 Set the permission levels to ‘external’

5) Tried Builtin\Administrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.

I can compile BUT NOT DEPLOY

Any help would be greatly appreciated.

Regards Steve

Who is the owner of the database ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Thanks for the reply.

I am running with Windows Authentication at the present moment , under the ID Administrator1 (running under windows Vista.(still would like to get reporting services to run ) . I did read the article on "Opening up the Administrator ID in Vista and installing SQL Server 2005 under that ID."

Re my current problem....

I managed to get it to deploy. I assigned Administrator1 as an "owner" of the database and gave the ID security administrator rights. I did the same with Builtin/Administrator.

I found something very interesting. I am able to deploy CLR stored precedures from the Visual Studio environment but not CLR functions. (EXTERNAL ACCESS) In order to deply functions I must compile the Dll and create SQL to CREATE the ASSEMBLYfrom within the SQL Server Environment. Any ideas why.?

Once again thanks for the help? Very much appreciated.

Steve

|||

OK Folks this took me about 3 hours to do BUT I have resolved the problem and it WORKS!!!

BTW I am using SQL Server 2005 Standard and Visual Studio.NET Enterprise

First create a Strong key as described in“The secret of strong naming”

DO NOT DODELAY SIGNING !!!!!!

http://www.ondotnet.com/pub/a/dotnet/2003/04/28/strongnaming.html

Sn – k MyKeyFile.snk

FIND OUT WHERE THE SNK FILE WAS COMPILED.

Go into VS2005 .NET and under project / properties in the signing page click the Sign the Assembly check box and browse to find MyKeyFile.snk

The database page

Mark the assembly for external access and leave the owner blank

Now we must go to the article

http://sqljunkies.com/WebLog/ktegels/archive/2006/08/14/safetydancelite.aspx

The text below comes from this article....

A kinder, lighter Safety Dance for the SQLCLR

A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.

Part one -- preparing the master database

1. As needed, start a series of queries against the MASTER database.

2. Create the target database (meaning execute a CREATE DATABASE query).

3. Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.

4. Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'

USE Master

GO

create asymmetric key imageskeyFile from executable file = 'C:\YEAR 2007\Images\Images\bin\Debug\Images.dll'

5. Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>

Use Master

Go

create login ImageMaker from asymmetric key imageskeyFile

6. Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>

Use Master

Go

grant EXTERNAL ACCESS assembly to ImageMaker

Part two -- preparing the hosting database

1. As needed, start a series of queries against the desired user database.

2. Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)

3. Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>

4. Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe or external access as shown below

SET QUOTED_IDENTIFIER OFF

USE YEAR2007

GO

CREATE ASSEMBLY Images

AUTHORIZATION ImageMaker

FROM "C:\YEAR 2007\Images\Images\bin\Debug\Images.dll"

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

Error Working With DTS -- "Class Not Registered"

I have a DTS package (stored in a .dts file) that was created in SQL Server 2000 and I am trying to work with it in SQL 2005. When I try to import it into the Legacy > Data Transformation Services node in Management Studio, I get a window that reads "Class Not Registered (Microsoft Data Transformation Services (DTS) Package)". I get the same error if I try to just execute the package itself from a query window.

I have Legacy Support installed, and I also have Integration Services installed.

Any ideas? We have three installations of SQL 2005 and I am getting the same error on all of them.

Have you installed Microsoft SQL Server 2000 DTS Designer Components from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&displaylang=en

The DTS designer does not get installed with SSIS.

Thanks.

Error with schedule a DTS package...

Hi experts...

I'm trying to schedule a DTS package (import some tables from Mysql database) but there is
an error and I don't know how to resolve it.

The error is always the same...
"The job failed. The Job was invoked by Schedule 24 (Import RT data). The last step to run was step 1 (Import RT data)."

I have tried changing the all the parameters in the job properties, but I always obtein the same message.

The DTS package works fine, I can execute and it works, the problem is the schedule...

Thanks in advanced...What account is the scheduled task running under? Normally if you run it yourself and it works, you are running the DTS package with YOUR permissions. However, if you run the DTS package under a schedule, it is using the standard SQL Server account (which is either Local System or another specified account). You need to change the account the schedule runs under to have enough permissions to run properly.|||Thanks for your reply...

I have one more question... How can i change the account of the schedule task?... I don't know how can I change it.

The strange thing is that I've done the same with another databases, only one thing is different, the source, the schedules that work fine are between SQL databases and this is from Mysql to sql database... but this is not the problem becouse as I said I can execute it and it works fine.

thanks again.|||If you schedule a job in SQL Server, then on the Job Properties dialog window, click the Steps tab. Highlight the step (here, executing the DTS package job) that you want, and click Edit or properties. In the Job Step properties dialog window, click the Advanced tab, and there is a Run as user dropdownlist. Try changing that to your credentials.|||Hi Again,

There is a problem with this solution... the step type is CmdExec and you can't see this option unless you choose SQL Script type... I don't understand what's happening, I have read the documentation about Package Scheduling and Security Issues but I don't know how to change who invoked the job...

thanks again...|||That's an operating-system command or executable program command step. I'm not familiar with that. Is there any way to change the security context in the DTS package designer?|||Hi again...

The step is an operating system command... but as I said this job is similar to other... the only I can see related to the security context is the owner, and it's the same in all jobs that I have done (and they work fine).

thanks for your interest.|||Aha, I found this from databasejournal.com (link

You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs.|||Thanks a lot, but I haven't resolved the problem, I have seen this choice, and I put an administration user, but It didn't work... I don't know...

Sorry about your lost time.|||An administrative user for the box, or an administrative user on the domain? Domain permissions could be the problem.

You could also try posting to the Microsoft newsgroups, specifically the sqlserver.dts one:

http://communities.microsoft.com/newsgroups/default.asp

Then look for sqlserver.dts in the left frame after it downloads (it takes a while).

Error with Openrowset

I have a text file, that I need to import to SQL. I am trying to do this using OPENROWSET. This is the qry i use below.

select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver
(*.txt; *.csv)};
DefaultDir=c:\edge;',
'select * from
vwConstrLendingLoanDefiDetailReady.txt')

I have created a System DSN, For the microsoft text driver. Anyhow it throws this error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Any idea why?

If this is in SQL 2005

Go to Surface Area Configuration->Surface Area Configuration for Features
Database Engine->Ad Hoc Remote Queries
Make sure 'Enable OPENROWSET and OPENDATASOURCE Support' is checked.|||It is SQL SERVER 2000. I have used Openrowset while connecting to other dbs like SQL Server etc...|||i FOUND THE Fix. I had an extra space, in mjy qry. Fixed that and it was able to execute the qry, and return the data.

error with OpenRowSet

I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...I think it should work if file name is correct. Check whether the file
name is correct
Madhivanan|||Hi
Try this one
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\MyData.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
OR
Specify all columns
insert into MyTable (col1,col2)
SELECT col1,col2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
".bill" <wSweetman@.ucsd.edu> wrote in message news:O3rAPZfHFHA.3612@.TK2MSFTN
GP09.phx.gbl...
I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...|||Do you have the file opened in Excel when you try to do this?
That would cause an error, but otherwise the syntax looks ok.
If the file C:\Default.xls exists and has a sheet named
Sheet1, it should work. Does the SQL Server
login have access to that file? What happens if you try
exec master..xp_cmdshell N'dir C:\*.xls'
Steve Kass
Drew University
.bill wrote:

>I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>Connection Closed
>here's the sql:
>select *
>into MyTable
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>any help is appreciated...
>
>|||Hi there,
Has anyone found an answer to this yet?
I tried:
insert into tblTestSql (RowNo,RecNo,LdUserName,
Code,LogIn,LogOut)
SELECT RowNo,RecNo,LdUserName,
Code,LogIn,LogOut
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Which gives me
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
When I try:
exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
I get
The system cannot find the file specified.
Any ideas?
Paul
".bill" wrote:

> I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet
, but receive the following error:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> Connection Closed
> here's the sql:
> select *
> into MyTable
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
> any help is appreciated..|||Hi Paul,
The file is in the local machine and you might have run the query in
the server which is not the local machine
Madhivanan|||Hi Madhivanan,
It dosn't matter whether the excel (or txt/csv) file is on the server on on
my local machine I get the same result.
Paul.
"Madhivanan" wrote:

> Hi Paul,
> The file is in the local machine and you might have run the query in
> the server which is not the local machine
> Madhivanan
>|||Paul,
How do you know the file exists?
If it does, and exec master..xp_cmdshell N'dir
c:\ReleaseTwo\LogInandOut.xls' fails,
it's not a problem with OPENROWSET. It sounds like the account used by
SQL Server does not have permission to access the Excel file.
SK
Paul in Harrow wrote:
>Hi there,
>Has anyone found an answer to this yet?
>I tried:
>insert into tblTestSql (RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut)
>SELECT RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>Which gives me
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: Unspecified error]
>When I try:
>exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
>I get
>The system cannot find the file specified.
>Any ideas?
>Paul
>".bill" wrote:
>
>|||> How do you know the file exists?
Because I created it.

> If it does, and exec master..xp_cmdshell N'dir
> c:\ReleaseTwo\LogInandOut.xls' fails,
> it's not a problem with OPENROWSET. It sounds like the account used by
> SQL Server does not have permission to access the Excel file.
I've just shown that to my techie and he's looking blank! How do I/we find
out what permissions SQL Server has & how do I/we (proberly me) change them?
Many thanks
Paul.|||
Paul in Harrow wrote:

>Because I created it.
>
>
>I've just shown that to my techie and he's looking blank! How do I/we find
>out what permissions SQL Server has & how do I/we (proberly me) change them
?
>
>
It might be as simple as: navigate to the file in Windows Explorer,
right-click, go to the security tab, and make sure the file is available
to the account under which the SQL Server service is running.
But managing Windows accounts is not my strong point, so you could also
look at "Setting up Windows Services Accounts" in Books Online and
see what you find on Usenet:
http://groups.google.co.uk/groups? ...missions%20file
http://www.google.co.uk/search? q=%...missions%20file
For the openquery statement to work, you should be able to log on to
Windows with the same account used by the SQL Server service and
then access the file.
SK

>Many thanks
>Paul.
>

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to

import SQL generate documents and received these errors. I have

had to add the extra step of first importing them into a spreadsheet

and then importing into SQL Server 2005. If anyone knows of a

patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank you
Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan

|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan

|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to import SQL generate documents and received these errors. I have had to add the extra step of first importing them into a spreadsheet and then importing into SQL Server 2005. If anyone knows of a patch, I would love to know.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank you
Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan

|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan

|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to import SQL generate documents and received these errors. I have had to add the extra step of first importing them into a spreadsheet and then importing into SQL Server 2005. If anyone knows of a patch, I would love to know.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to

import SQL generate documents and received these errors. I have

had to add the extra step of first importing them into a spreadsheet

and then importing into SQL Server 2005. If anyone knows of a

patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to

import SQL generate documents and received these errors. I have

had to add the extra step of first importing them into a spreadsheet

and then importing into SQL Server 2005. If anyone knows of a

patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?

Error while importing Text file using Import Export Wizard

Hi all

i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

Messages

Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

- Post-execute (Success)

Messages

Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)

=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter

moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem

thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

-Jamie|||dear sir
thank for ur replay

about what u say

Import/Export wizard offers you the chance to save your package

i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...

also when u say

then open it up and look at the metadata of the pipeline

i didnt under stand it ..... sorry
but after long investegation i found the following

i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows

all the colums with data type: varchar and length: 8000 for both tabels.

i fined somthing very strange

- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005

- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

|||I'm convinced also that some kind of bug must exist here. I keep getting this error:

The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

Dan|||OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

Dan|||

Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

Thanks!!

|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||

I get the same error on all our development and production machines.

I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

This will just show that there is a bug in SSIS 2005.

Please help MS! This is a big problem for us!

|||

I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.

Can anyone help us?

|||

I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.

BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

Hopefully this issue is resolved and fixed by Microsoft soon...

|||

The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

Thanks.

|||

Thanks for your answer,

I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

|||I think there is a bug in the product as well. I have tried to

import SQL generate documents and received these errors. I have

had to add the extra step of first importing them into a spreadsheet

and then importing into SQL Server 2005. If anyone knows of a

patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||

hi jaypee,

I have just tried using the bulk insert as follows

BULK INSERT dbo.[table]

FROM 'Y:\data.csv'

with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

and I ge the following error for all the Datetime columns in the file.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

any ideas?