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

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

Mapping custom UDT's (User Defined Types)

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

We have custom user defined types on SQL 2008. I would like to map these data types for SSIS when exporting / importing data. I've managed to do this successfully for the built-in types such geometry / geography by editing the MSSQLToSSIS10.XML.

My question would be if this is also possible for a custom UDT's created with a different schema, ie [myschema].[MyUDT]? If I define the mapping, SSIS only looks for the UDT definition without the schema (which works in the mapping file without the schema), but fails on the table creation, since the data type cannot be found.

If I define [myschema].[MyUDT] in the MSSQLToSSIS10.xml file, the mapping cannot be found in SSIS. I'm stuck here with a catch 22.

Any advice / workarounds ?


View Complete Post

More Related Resource Links

Defaults and User Defined Types Tabs in SSMS

Hello, Just to make sure I was using all the available tools for the right purpose, I have been going through all the tabs I haven't used in SSMS. I've managed to research what they all mean but two have me a little stumped. These two tabs are: [Database]->Programmability->Defaults Quite hard to search for help on as the word default is used to explain a lot of different things! [Database]->Programmability->Types->User-Defined Types I understand what "User-Defined Table Types" and "User-Defined Date Types" are, so what goes in "User-Defined Types"? Can anyone explain what these are? Thanks very much

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

SQL Types and user defined aggregates


I have been experimenting with SQL 2008 CLR Aggregates with some success thanks to this forum.

I have tried changing a working aggregate from using SqlDouble to using SqlDecimal and the function compiles fine however when it comes to the CREATE AGGREGATE statement no matter what T-SQL types I use for the input and return values I get error messages.

According to BOL SqlDecimal should map to DECIMAL or NUMERIC quite happily but it doesn't seem to work.

SqlDouble works quite happily with FLOAT and REAL.

Should I even be using SqlDecimal and if so how?

Are there any other data type anomolies.  I suspect that the date datatypes will also give problems.

Workflow designer rehosting and user defined types


I'm rehosting workflow designer, and I want to use my custom types as variables of the workflow. At first I couldn't get my types to be shown in the "Browse and Select a .Net Type" Window. I maneged to do that by loading assembly to AppDomain:


But now when I want to assign a property of a variable which is of type from my assembly I get the message: Compiler error(s) encountered processing expression "variable1.Attribute1" Type "BusinessObject1" is not defined

Problem with accessing User defined types.



I have a User defined type with .net CLR and I am trying to use that in sql server 2005.

Following is the class which I have created for User Defined Type.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class TxnRecObj : INullable,IBinarySerialize
  private int _InOutCode;
  private string _InOutData;
  private int _OraCode;
  private string _OraError;
  private string _ErrorData;

  private string _TRANS_CODE;
  private string _DA

User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

"Type ASP.x is not defined" error for dynamic user control when publishing updateable


I have a user control (ContainerCtrl) which programmatically instantiates another user control (SimpleCtrl) in OnLoad. It builds and runs fine, but when I publish it I get the error "error BC30002: Type 'ASP.SimpleCtrl' is not defined." (If I publish without the "updateable" option there's no error, but I need the site to be updateable.)

I assume this has to do with the particulars of how the assembly(ies) are built for the different compiler options. My question is, how can I get it to work properly for "updateable" publishing?

VS 2008 / .NET 3.5 sp1

The code follows (web.config is the default from Visual Studio).

Many thanks, Dave


<%@ Control Language="VB" ClassName="SimpleCtrl" CodeFile="SimpleCtrl.ascx.vb" Inherits="_SimpleCtrl" %>

    Simple control


Partial Class _SimpleCtrl
    Inherits UserControl

    'Nothing to see here.

End Class


<%@ Control Language="VB" CodeFile="ContainerCtrl.ascx.vb" Inherits="_ContainerCtrl"

How do I persist custom complex types in design-time


Let's say that I've got one custom webcontrol called MyControl.

public partial class MyControl: WebControl {
public IComplex Complex { get; set; }

The control is a very simple control but it holds an instance of a complex type that is accessable through a property. The property has the propertytype IComplex

public interface IComplex {
string Name { get; set; }
List<IComplex> Children {get;}

Let's say that I am able to pick one of several different types of object that derives from IComplex in design-time and assign it to the controls property "Complex" property.

Naturally I would like to persist this information to the HTML-View in a user-friendly way.

Because we don't know what type the user has picked there is no way to instansiate the type if we don't persist the name of that type. The Type could also contain more properties than the Interface the controls property is refering.

<cc1:MyControl id="MyControl1" runat="server">
<Complex> <!-

Office Space: Custom Field Types for SharePoint 2007


Custom field types provide a powerful way to define a reusable SharePoint column definitions.

Ted Pattison

MSDN Magazine May 2009

ASP.NET 2.0: Personalize Your Portal with User Controls and Custom Web Parts


ASP.NET 2.0 introduces a Web Part control that is designed to deal with the serialization, storage, and retrieval of customization and personalization data behind the scenes. In this article, the authors explain how you can put the WebPart control to work in your ASP.NET 2.0 applications.

Ted Pattison and Fritz Onion

MSDN Magazine September 2005

User Preferences: Manage User Settings in Your .NET App with a Custom Preferences API


There are plenty of options out there for managing user preferences including custom configuration files, the Windows registry, and the isolated storage. But each option has its pros and cons -- and a bad choice can make life difficult. In this article, the author evaluates various options and identifies the characteristics of a good preferences API. Based on those characteristics, he introduces an API that is specifically designed for preferences management, one that offers the best of all the options.

Ray Djajadinata

MSDN Magazine July 2004

Error Handling: Throwing Custom Exception Types from a Managed COM+ Server Application


Exception handling semantics in .NET are based on type, so you can create custom exceptions that have their own properties and methods. In .NET, exceptions are first-class citizens, and since they're the built-in error handling mechanism, all .NET-compliant languages must support exceptions. In addition, COM+ services are available to .NET code as Enterprise Services, so you can leverage exceptions in your Enterprise Services design.In this article the author describes custom exceptions, throwing exceptions across COM interop boundaries, and working with Enterprise Services.

Bob DeRemer

MSDN Magazine March 2004

InfoPath: Turn User Input into XML with Custom Forms Using Office InfoPath 2003


Office InfoPath 2003 is a new Microsoft Office product that lets you design your own data collection forms that, when submitted, turn the user-entered data into XML for any XML-supporting process to use. With an InfoPath solution in place, you can convert all those commonly used paper forms into Microsoft Office-based forms and end the cycle of handwriting and reentering data into your systems. Today organizations are beginning to realize the value of the mountains of data they collect every day, how hard it is to access it, and are striving to mine it effectively. InfoPath will aid in the design of effective data collection systems. Here the author shows you how to get started.

Aaron Skonnard

MSDN Magazine September 2003

Large document libraries with a custom user interface


We recently migrated our document management system to SharePoint (WSS 3.0) and I adapted my VB6 client application to search for and retrieve a list of documents from this new SharePoint library using the Lists web service. Although the total number of documents in this library is 90,000 at one site and over 145,000 at another, my VB6 application never retrieves more than 600-700 documents at a time. From reading white papers such as "Working with Large Lists in Office SharePoint Server 2007" (http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409), it appears that if my custom user interface limits the number of items returned in a search of these large lists, that it may not be necessary to put these documents into folders. However, our current scanning software, KnowledgeLake Capture, by default places newly scanned documents into folders based on the year, month, day and hour, which means that these documents are placed in nested folders 4 levels deep.

There are also some 20 metadata fields on these document libraries that allow us to filter these document libraries so that search results will in most cases be limited to a few hundred items.

We acquired new servers (Windows Server 2008 with SQL Server 2008) to host our SharePoint libraries and we are running WSS 3.0 S

Unable to search Custom User Property Created in User Profiles in SharePoint 2010


Hi ,

I have created a custom user property,and created metadata property and added mapping to it, still i m not able to Search in People,Please advise

Creating Custom SharePoint 2010 Field Types and Controls with Visual Studio 2010 and Silverlight 3

Learn how developers can create rich experiences in SharePoint Server 2010 publishing sites using Microsoft Silverlight 3.
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