.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Uniqueidentifier field failing during conversion in SSIS

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server


I am trying to convert a table from one schema to another and the destination table (SQL CE) contains a uniqueidentifier field. This field can accept nulls and is optional. I am not supplying any values for it.

But when I run the package it throws an error.

SQL Server Compact Destination [107]: Data conversion failed. [ OLE DB status value (if known) = 2 ]

If I map a dummy value for this column then the error doesnt occur. Any help would be appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

View Complete Post

More Related Resource Links

WebMatrix conversion failed when converting from a character string to uniqueidentifier?


I am trying to retrieve xml from a small function in my .cshtml page. Its throwing me an error. This however, runs fine in a console/form environment.

It takes 3 parameters and 1 exception.

myDll.GetXML(year, username, uniqueidentifier, out ex);

When I run this page I get the error, "Conversion failed when converting from a character string to uniqueidentifier."

Or a stack of.

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBeha

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

Data conversion in SSIS

I am creating an SSIS package, My source DB is ORACLE and destination is SQL Server 2005 I am moving the contents of a Table AO in Oracle DB to  Table B in SQL Server2005 A specific Column in  "Renvenue " in AO table is of data type NUMBER  , it contains  decimal values like 1040.334, 19191.33, 454545.22 I have a corresponding column 'Rev" in Table B ( in SQLserver DB) of datatype Numeric(18,2)   I have created a SSIS package using Data source Reader, DataConversion , OLEDB Destination  in the dataflow task Everything is working fine, but I am getting incorrect data for example 1040.334, 19191.33, 454545.22   for these values I am getting 1040.00 , 19191.00 , 454545.00 the decimal values are not getting transferred to the destination table. I tried converting the datatype to Numeric {DT_NUMBERIC] 18 -2   and even declimal(DT_DECIMAL] scale 2 but still not getting the decimal values   Can some one tell me where am I going wrong...      

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

SQL Agent Failing when trying to run my SSIS Package

My SQL Agent was working fine.  Now when I try to run it, it failes when trying to run my SSIS package.  I looked in the logs, and job history, no luck in finding any critical errors. My SSIS package runs fine through VS 2005 in debug mode. Here are the only errors: 01/04/2006 09:53:48,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect01/04/2006 09:53:48,,Warning,[260] Unable to start mail session (reason: No mail profile defined)01/04/2006 09:53:48,,Information,[129] SQLSERVERAGENT starting under Windows NT service control01/04/2006 09:53:48,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent01/04/2006 09:53:30,,Information,[432] There are 11 subsystems in the subsystems cache01/04/2006 09:53:29,,Information,[339] Local computer is BG-SQL2005 running Windows NT 5.2 (3790) Service Pack 101/04/2006 09:53:29,,Information,[310] 1 processor(s) and 512 MB RAM detected01/04/2006 09:53:29,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is01/04/2006 09:53:29,,Information,[102] SQL Server ODBC driver version 9.00.139901/04/2006 09:53:29,,Information,[101] SQL Server BG-SQL2005 version 9.00.1399 (0 connection limit)01/04/2006 09:53:29,,Information,[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 1860

SSIS Package failing

Hi there, There is SQL agent job that runs a SSIS package once a day, but for the last few days its been failing. As far as I can tell nothing has changed. The Database name is 'Greece' The error message is:   Message Executed as user: S2\ServiceAccount. ...9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:14:18  Error: 2010-09-13 10:14:49.36     Code: 0xC0202009     Source: NL LCDB Import Products Connection manager "Greece.eu.LB.com,10563.msdb.BNLSQL"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'BNLSQL'.".  End Error  Error: 2010-09-13 10:14:49.36     Code: 0xC020801C     Source: NL LCDB Import Products Log provider "NL LCDB Import Products"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Greece.eu.LB.com,10563.msdb.BNLSQL" failed with error code 0xC0202009.  There may be error messages

Data Flow Task failing to drag-and-drop onto SSIS Control Flow

This morning, I started having problems with Visual Studio 2005 on XP when I tried to create an SSIS project; I was getting an error message about the failure to create an SSIS runtime object, with the reference "Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type". Digging here and elsewhere on the Net, I used regsvr32 to re-register msxml3.dll, msxml6.dll, and dts.dll. This allowed me to create a new SSIS project, but I was unable to add a data flow task to the Control Flow pane -- when I dragged the control onto the pane, nothing happened, and when opened the Data Flow pane and clicked on the 'No data flow task has been defined. Click here to create a data flow task" link, I got a 'Failed to create the task. The designer could not be initialized." error. Searching here and on the Net, I uninstalled and reinstalled the SQL Server 2005 workstation tools.

After doing this, I am still unable to create a data flow task by dragging the task out of the toolbox; the pointer assumes the right form for the drag-and-drop, as it does for other tasks, but when I release the mouse button, nothing happens. However, when I go to the Data Flow pane and click on the 'click here to create a new data flow task', it creates the task, and I am able to use copy-and-paste to make copies of the data flow task

SSIS Data Conversion without using the Data Conversion Transformation

how can i convert a record from a flat file to a different data type without using the data conversion task?

SSIS variable that should contain uniqueidentifier



I am importing data and wand to assign a fixed value to an output col. the fixed value should be a uniqueidentifier maintained in a variable so that it can easely exchange prior to execution. My problem is that I realized that there is no variable type for uniqueidentifier. When trying to store the guid in a string variable and then trying to cast back using a script component i received a casting exception telling me that a string cannot be converted into a guid.

I dont want to use an object type because then i would have to manually read out the variable a execution start.

Does anyone have experience using varialbes and guids?

Thanks a lot


SSIS SQL jobs failing urgent help please!!!


Here is the error I am getting when running the SQL jobs, this sql job basically runs the SSIS package. Not sure where to look at to fix this error:

Description: The log provider type "DTS.LogProviderTextFile.2" specified for log provider "SSIS log provider for Text files" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.



DateTime Conversion Issues in SSIS



I have a Weird issue with SSIS. I have some input data of dates like '01/01/1999' and '01/01/0207. First one is a valid date and later is invalid. I have to use DataConversion inorder to convert it to datetime and re-direct the errored row to Flat-file destination.

Now the Issue is:

If I convert to DateTime (DateTimeStamp) in Data Conversion, it is treating both the dates as Valid and failing in my SQL Destination as the later is an invalid date.

If I convert to DateTime2 (DateTimeStamp2 with Precesion) in Data Conversion, it is treating both the dates as Invalid?

How do i fix this in SSIS (In a simple way) to throw 1 as error and other as Valid? Any help is appreciated.



ssis date conversion



We are trying data conversion  using ssis  and facing the following issues: 

1. How to default values to table (sql server) through SSIS in the data transfer step. (I used to run an update after the data transform task, but here the table schema does not allow nulls at all during the intial data transfer). It looks to me may be there is an option somewhere that i need to check for the default values.

2. We want to transfer data to a identity column, knowing that it does not let me export data into that column i actually added a  SQL step before the data flow task saying "SET identity_insert off" on that table but still the package fails saying that cannot insert on identity column.

3.we also want generate a script that shows how the data transformation is done like which table and column is mapped to which destination table and column.

please advice.


Conversion failed when converting from a character string to uniqueidentifier


Hi all,


I am trying to match a username to a userid which are in 2 different sql tables. Here is my select statement:

SELECT     aspnet_Users.UserName
FROM         aspnet_Users INNER JOIN
                      aspnet_starterkits_Projects ON aspnet_Users.UserName = aspnet_starterkits_Projects.ProjectManagerId

When I run this i get an error message: "Conversion failed when converting from a character string to uniqueidentifier"

Is there anyway around this? Or a different select statement etc?



SSIS Error in Script task: "Conversion from string "C002" to type 'Integer' is not valid"


Hi All,

I have a script component in my Dataflow that generates Error Description string.

But I am not trying to convert anything in my Script as you can see below.

Here is the Code inside the SCR_component.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
     'Use the incoming error number as a parameter to GetErrorDescription
    Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
   End Sub
End Class


When I run the package , it fails at this script task with the following error:

Description: System.InvalidCastException: Conversion from string "C002" to type 'Integer' is not valid.


The value "C002" is actually a column in the metadata and its type is "DT_STR". I am not trying to convert it in my Script as you can see

Field shows as when using SSIS and sharepoint adapter


Hello, I am setting up an SSIS usingthe Sharepoint Source adapter. I am replacing and existing process that imports data from Sharepoint into Excel directly, but this is manual.  The sharepoint source adapter seems to truncate column names or provides different column names then what is visible on the user Sharepoin site - they all do not match my manual import into Excel and there are additional fields.  I'd assume that what it is returning is the actual Sharepoint Database column name.  Anyway, I need to map my fields to my existing process in SQL.  The only way I know how to do this and identify what columns are being returned, I am attempting to import the data into Excel using SSIS through the Excel Destination.  I'm having an issue with a few fileds....I have a field/column called QAReviews which is importing into Excel with all the fields for each record showing <Lon Text> instead of the value the Sharepoint site actually contains.  In my manual Excel import process, this field shows the correct data.  By the way, on sharepoint this is a choice field. Can someone tell me why I'm getting something different through SSIS then what I get from using the manual import in Excel.  I also exported the data from Sharepoint to Access and again I get the correct data. Please advise.



SSIS job failing successfully



I have a series of SSIS packages I use to copy data from a MYSQL database to SQLServer.  The packages are set up to execute via a scheduled job.  I each case, the data is copied, but the step that executes the package fails.


Date  7/31/2008 12:54:42 PM
Log  Job History (Test t_accounts)

Step ID  1
Server  xxxxxx

Job Name  Test t_accounts
Step Name  exec
Duration  00:00:02
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Executed as user: xxxxxxx. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   
Started:  12:54:42 PM  DTExec: The package execution returned DTSER_SUCCESS (0). 
Started:  12:54:42 PM 
Finished: 12:54:43 PM 
Elapsed:  0.969 seconds. 
The return value was unknown.  The process exit code was -532459699.  The step failed.


I've turned on logging for every event in the package, but don't see anything that looks like a failure.



Source = MYSQL 5.0 using MYSql Connector /.Net 5.1.6

Destination = MSSQL 200

SSIS Derived Column and Data Conversion Task


I am passing down a variable, filename, as  string and then using Derived Column to pass it down to the destination. The filename is numeric (date) like such: 20101010. So, I need to convert the filename into a date formmat before I pass it to the destination table. Therefor, I am using a data conversion and converting it to a Date format. I tried using both database date or the date format and it keeps failing.

Here is what the failure report:

[Data Conversion [42297]] Error: Data conversion failed while converting column "DateAdded" (29685) to column "Copy of DateAdded" (42310).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".  

Any ideas to resolve this issue?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend