Sql Cannot connect: attempting

Showing posts with label attempting. Show all posts

Thursday, March 29, 2012

Cannot export to Excel from Report Manager

When attempting to export to Excel from report manager we get the error
"Internet Explorer cannot download Format=Ecels from reports.server"
Internet Explorer was not able to open this Internet site. The requested
site is either unavailable or cannot be found. Please try again later."
This only happens when the person accessing reports manager is not a local
administrator. Local administrator privileges make the problem go away.
What is report manager trying to access that I need to give my user access
to? I cannot make the user a local admin.The problem does not just occur if exporting to excel. It's all file types.
Any file type we try to export to from report manager kicks back essentially
the same error we were getting when trying to export to excel. Oddly enough,
the development server which uses 2k3 server doesn't do this. The production
server that is running Server 2K does.
"Valleri_Collins" wrote:
> When attempting to export to Excel from report manager we get the error
> "Internet Explorer cannot download Format=Excel from reports.server"
> Internet Explorer was not able to open this Internet site. The requested
> site is either unavailable or cannot be found. Please try again later."
> This only happens when the person accessing reports manager is not a local
> administrator. Local administrator privileges make the problem go away.
> What is report manager trying to access that I need to give my user access
> to? I cannot make the user a local admin.

Sunday, March 25, 2012

Cannot Drop Server

I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
> >I am receiving the following error when attempting to drop a server to
> >rename
> > it
> >
> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> > Line
> > 39
> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> > replication.
> >
> > Replication is not occuring on this server.
> > When I execute sp_get_distributor the results show that replication is not
> > occuring on this server.
> >
> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> > My question is should I delete the record that is in this table to allow
> > renaming of the server?
> >
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>> It is possible this server has a remote distributor. Issue a call to
>> sp_helpdistributor to get the server which is acting as the distributor's
>> name.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in
>> message
>> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>> >I am receiving the following error when attempting to drop a server to
>> >rename
>> > it
>> >
>> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
>> > Line
>> > 39
>> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
>> > replication.
>> >
>> > Replication is not occuring on this server.
>> > When I execute sp_get_distributor the results show that replication is
>> > not
>> > occuring on this server.
>> >
>> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
>> > My question is should I delete the record that is in this table to
>> > allow
>> > renaming of the server?
>> >
>>sql

Monday, March 19, 2012

Cannot create INSTANCENAME

SVR: W2KADV SP4
MSDE is already running supporting Veritas Backup exec and another
application.
Attempting to configure WSUS.
Need to create a new INSTANCENAME=WSUS
Command line setup SAPWD="oursapwd" INSTANCENAME="WSUS"
What I get is this a blackened screen with "Windows 2000 Setup" in the upper
right hand corner. In the center of the screen is a blank menu box entitled
"Usage"
ERRORLOG: entries
2005-07-06 15:33:40.19 server Microsoft SQL Server 2000 - 8.00.818
(Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
2005-07-06 15:33:40.19 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-07-06 15:33:40.20 server All rights reserved.
2005-07-06 15:33:40.20 server Server Process ID is 9384.
2005-07-06 15:33:40.20 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2005-07-06 15:33:40.20 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2005-07-06 15:33:40.31 server SQL Server configured for thread mode
processing.
2005-07-06 15:33:40.31 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2005-07-06 15:33:40.34 spid3 Starting up database 'master'.
2005-07-06 15:33:40.45 spid5 Starting up database 'model'.
2005-07-06 15:33:40.45 server Using 'SSNETLIB.DLL' version '8.0.818'.
2005-07-06 15:33:40.47 spid3 Server name is: "Left Blank".
2005-07-06 15:33:40.47 spid3 Skipping startup of clean database id 4
2005-07-06 15:33:40.47 spid3 Skipping startup of clean database id 5
2005-07-06 15:33:40.47 spid3 Starting up database 'SUSDB'.
2005-07-06 15:33:40.53 server SQL server listening on Shared Memory.
2005-07-06 15:33:40.53 server SQL Server is ready for client connections
2005-07-06 15:33:40.56 spid5 Clearing tempdb database.
2005-07-06 15:33:40.56 spid3 3 transactions rolled forward in database
'SUSDB' (6).
2005-07-06 15:33:40.58 spid3 0 transactions rolled back in database
'SUSDB' (6).
2005-07-06 15:33:40.58 spid3 Recovery is checkpointing database 'SUSDB'
(6)
2005-07-06 15:33:40.76 spid5 Starting up database 'tempdb'.
2005-07-06 15:33:40.80 spid3 Recovery complete.
2005-07-06 15:33:40.80 spid3 SQL global counter collection task is
created.
2005-07-06 15:33:43.64 spid51 Starting up database 'SUSDB'.
2005-07-06 15:48:59.22 spid52 Starting up database 'msdb'.
2005-07-06 15:48:59.44 spid52 Using 'xpsqlbot.dll' version '2000.80.760'
to execute extended stored procedure 'xp_qv'.
hi,
Lokton wrote:
> SVR: W2KADV SP4
> MSDE is already running supporting Veritas Backup exec and another
> application.
> Attempting to configure WSUS.
> Need to create a new INSTANCENAME=WSUS
> Command line setup SAPWD="oursapwd" INSTANCENAME="WSUS"
> What I get is this a blackened screen with "Windows 2000 Setup" in
> the upper right hand corner. In the center of the screen is a blank
> menu box entitled "Usage"
> ERRORLOG: entries
this is the error log of the default MSDE instance probably used by Veritas
and other sw...
if you are using the new sp4 ugraded MSDE installation package, please try
omitting the quotes surrounding "oursapwd" and "WSUS"
then please add the
/L*v "c:\msdelog.txt"
parameter to your call to setup.exe boostrap installer, in order to allow
verbose logging of the installation process.. should error(s) be raised a
RETURN VALUE 3
will be written to the log, and about 10/15 lines before each entry some
(sometime criptic) description of the problem will be reported...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Wednesday, March 7, 2012

cannot convert between unicode and non-unicode with SQL Server

I keep getting the error message below when attempting to import a text file (flat file) to an SQL Server Destination using SSIS. This database has recently been migrated from SQL Server 2000 (where I used a DTS Package that worked fine). I plan on doing further manipulation to the file after importing but have so far tried to keep one step at a time. I have tried using a smaller files and different tables but still get a similar response. I believe that the encoding of the text file is ANSI. If it is relevant the database collation is Latin1_General_CI_AS (the same as it was when 2000). Any help anyone can provide will be greatly appreciated!!!

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 1" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 2" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 3" and "Name" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 4" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 5" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 6" and "ExpiryDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 7" and "RateType" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 8" can't be inserted because the conversion between types DT_STR and DT_BOOL is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 9" and "FullName" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 10" and "Address" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 11" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 12" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 13" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 14" and "Occupancy" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 15" and "LoanPurpose" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 16" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 17" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 18" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 19" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 20" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 21" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 22" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 23" and "DocumentLocation" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 24" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 25" and "SecurityType" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 26" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 27" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 28" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 29" and "MortgageInsurancePolicyNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 30" and "SecurityAddress" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 31" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 32" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 33" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 34" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 35" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 36" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 37" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 38" and "SecuritySuburb" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 39" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 40" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 41" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 42" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 43" and "MortgageNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 44" and "TitleParticulars" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 45" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 46" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 47" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 48" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 49" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 50" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.

Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 51" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (174)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

The errors are exactly as are stated. You are trying to do implicit conversions that do not work. e.g. In column1 the value is coming in as a String and you are trying to insert it into an integer column.

You will have to open up the package and use the Data Conversion component or the Derived Column component to do explicit conversions.

-Jamie

|||

Thanks for the reply Jamie, I have tried using both of these and still get a similar conversion error (as below on a similar table). I have tried converting to different datatypes but still get the same conversion error.

The same database had no problem using DTS on 2000.

I must missing something that is probably blatantly obvious, but do you have any other ideas?

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [SQL Server Destination [4395]]: The column "TransactionKey" can't be inserted because the conversion between types DT_NTEXT and DT_WSTR is not supported.

Error at Data Flow Task [SQL Server Destination [4395]]: The column "AccountNumber" can't be inserted because the conversion between types DT_NTEXT and DT_WSTR is not supported.

Error at Data Flow Task [SQL Server Destination [4395]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SQL Server Destination [4395]]: The column "Acmount" can't be inserted because the conversion between types DT_R4 and DT_CY is not supported.

Error at Data Flow Task [SQL Server Destination [4395]]: The column "TransactionDate" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.

Error at Data Flow Task [SQL Server Destination [4395]]: Column "Narration" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (4395)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

|||

In SQL Server 2000, DTS used to do implicit conversions in many circumstances. The decision was made to eliminate that risky guessing in Integration Services, and to compel all data type conversions, truncations, etc. to be made explicitly.

Note that if you use the Import and Export Wizard to create your "base" package, the wizard creates all these tedious conversion transformations for you.

-Doug

|||

I'm getting the '...can't convert unicode...' conversion errors for strings imported from a table in Access 97.

I see that the Import Wizard does it's job and will successfully import the data into a new table.

But if you then change the destination to a table that already exists (created by a db copy from SQL2k) then the wizard generated package fails with the unicode conversion error again.

I've changed the destination column properties to match those created in the new table by the wizard - and it still fails.

I suspect that I could get the package copy the import into my table by doing an INSERT and then drop the table that the wizard creates. But I'm sure there's a more direct way than this.

I suspect I'm overlooking something obvious - but it is proving difficult to manage data between Microsoft family applications.

Any help / links appreciated.

|||

have the same error trying to import an Access database with a memo

field type. SQL Server 2005 SSIS can't convert the memo to a varchar

and gives the unicode error. Odd.|||

I have found the same problem connecting to a Lotus Notes database via the NotesSQL 3.02g ODBC driver.

Consider the following article on unicode.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6voh.asp

When I changed the datatypes of my destination table from varchar to nvarchar, I no longer received the "cannot convert between unicode and non-unicode" error.

|||To be clear: String conversion errors can be resolved by changing char columns to nchar and varchar columns to nvarchar in SQL2005.|||

To be really clear, conversion errors happen because we use types inconsistently. There are unfortunately some instances when the type is forced upon us, but it is easy enough to change types inside SSIS. Changing existing database structures to simplify your ETL is not a great justification, alebit if possible it may make life easier. So in summary changing SQL Server column types will work, but so will converting the data within the pipeline, see the Data Conversion transform or the Derived Column transform.

|||

Well, I guess just another in a long line of folks hitting this trip wire. I'm trying to import an Excel file into an existing SQL table which as varchar columns, not nvchar. And, you're right, I'm not changing the table definition!

OK, so please forgive the basic nature of the question, but you say it is easy enough to change types in SSIS. So, you have to drag one of those transforms (Data Conversion or Derived Column) then onto your package?

I was just thinking that there ought to be a more native way to do this. But I guess this is it, then?

For example, I right-clicked by source data file and went to the Advanced Editor. There's a Input and Output Properties tab that shows Excel output in a tree view. When expanded, in turn, there are External and Output columns nodes.

I tried changing these to my desired data types, but it didn't seem to a) save my changes much less b) make the package work. So what's does one use the Input and Output Properties for?

Thanks for any information (or if you're up to adding an indepth article about it on your SQLIS.com website!). BOL does not seem to offer any meaningful information here.

|||

External columns are what the source actually contains. Output columns are what SSIS gives you downstream. You cannot change the types of either because the external types are decided by the underlying Excel provider not you, and you cannot change the output columns because they should match the external columns. External vs output is a bit like which columns have you selected to bring through to SSIS out of those available. So changing types as part of your column choice does not make sense, it would only lead to problems and failures.

The lack of implicit conversion is annoying for most people, but you can workaround. If implicit conversions take place and they are wrong, then you are generally stuck. The strong typing of SSIS is better in the long run I firmly believe, although I do often think it would be nice the other way too. Think of the way strings can be interpreted as dates. How may times have you hot problems with MD vs DM (if you in the UK all the time!). VBScript was my favourite as that would convert dates both ways depending on what the value was, all within the same function! You had no control and now idea which way it would go. Hence my assertion that not having implicit conversions are better. On the other hand some more help could be given in the product, such as selectively allowing similar types to be converted, STR to WSTR and I2 to I4. There will still be issues no doubt for more international organisations with multiple extended character sets or those using different code pages.

Data Conversion or Derived Column is the way to go I'm afraid.

|||Again thanks for the update.

So, I'm trying a simple test at this point. Starting with a brand new package, I set up an Excel source, then dragged a Derived Column transformation onto the Data Flow designer. I selected just two columns to test w/ by dragging them from the Columns pane down to the Expressions field, putting a TRIM() around 'em, entered a custom name for each in the Derived Column Name field, selecting <add as new column> and selecting string [DT_STR] in the Data Type field.

Then set up my SQL table as the destination and mapped the two derived columns to the corresponding columns in my table.

I'm still getting task validation errors.

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

So, this makes no sense to me. In the derived transformation editor, I have the data types set to string; in my SQL table, the columns are varchar's. I'm just not sure what I else I should be doing...

Any suggestions would be welcome.|||

Although you have selected the type of the output column to be DT_STR you haven't told SSIS how to convert the value into a DT_STR so in essence all you have done is define another implicit conversion.

Explicit conversions are done using type casts. The following expression will convert a column of type DT_WSTR into a DT_STR:

(DT_STR, <length>, 1252) (<column_name>)

Hope that helps.

-Jamie

|||

I am experiencing a similar error using the Microsoft OLEDB provider for oracle as a source. All data types are defined as nvarchar2 in Oracle and exist in a sql server 2000 db typed as nvarchar. Since these are both unicode types, why am I getting the same error?

I did find that the .net oracle provider fixed the unicode/non unicode error however the size of the source field is 1.5 times in the destination. The package will run but I get truncation warnings throughout execution.

Any feedback on these is greatly appreciated!

Thanks,

Barbara

|||

I am having the same problem with SQL Server as a source and destination. I am using a "DataReader Source" and specifying a SQL query. I then pass it through Merge Join and Conditional Split transforms. My destination is an OLE DB Destination. If I pick an existing destination table that has EXACTLY the same schema as the source table, the data flow fails with the same message everyone is getting above. Somewhere in the process the package is converting the source data from varchar to nvarchar. I can only get it to work if I have it create a new table in the OLE DB Destination, in which case it gives me nvarchar columns (identical schema as source except it changes the varchars to nvarchars).

Where in the package is it converting from varchar to nvarchar? It this a known issue? I can tell you with certainty that the source fields are not unicode.