.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

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

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
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!

View Complete Post

More Related Resource Links

User Defined Data Type - using SMO *** NEED HELP ! ***

Hi,Is it possible to script out the User Defined Data Types via SMO in .NET and specifically VB.Net?  I have not been able to find some examples on how to do this.  If you have some experience with this, pleasepost some examples or provide links where I can find this information.Thank you in advance.-Sydney

The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must



This is frustrating, and I don't know how to solve it.

I have a strange problem. I am adding a LinqDatasource object, and set the context:

        protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
            e.ObjectInstance = new KaruselaDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

Then I get this error:
The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Linq, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.


On web.config I already have this:
<add assembly="System.Data.Linq, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

NOTE: The datacontext is in a different project (DLL proect) where I added a refernce to System.Data.Linq.

is there another way adding a refernce to a web project? or only though teh web.config?


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'

Which is the best approach for applying business rules and basic validation like data types?

Hi Everybody   Please suggest me the best approach for applying the business rules and data type validations .Here is my choices : 1)Sequential work flow 2)State machine work flow 3)Work flow rule engine 4)Rule engine without workflow Out of above choices ,which is the best approach? Please provide me the reasons or senarioes for which is the best approach for applying the business rules and also performancewise. My requirement is : After getting the reports ,need to apply the datatype validations like valid or invalid date ...etc to the reports .And stored into Database.In the next step,need to apply the business rules to that reports in such a way that data should be good quality. Thank you very much

Collect data from a User - Validation Bug




I use the action "Collect data from a User". I add a new field of the type Choice, the next step I write two choices:




and configure it:


Default value is blank
  Display as: Radio Buttons
  uncheck Allow Fill-in choices
  uncheck Allow blank values


Click Finich button


When the task is created I need to choose among Approved or Rejected, however if it be not chosen any alternative the validation doesn't work.


If I have a field text the validation works perfectly.


Is there a bug in validation form when the field is a "Choice type" ?


Please help-me.

Multidimensional Arrays (or) ArrayLists and Object Data Type in SSIS

I want to create a multidimensional array dynamically in a Script Component and assign to a Object DataType and then use that variable in another Script Component, to retrieve the elements of the multidimensional array

SSIS data flow Data Types vs. SQL Server Data Types


How do I join a SSIS text field type cast from the Derived Column Transformation Editor with a varchar from an SQL data table in a lookup?

Also, is there any reference on which SSIS data flow datatypes can be JOINed to SQL Server datatypes?

Here's the problem:  I have records in a flat file.  The flat file name contains the data of the records.  When importing, I read the flat file name into a data field using SSIS.  Then, I use FINDSTRING to create a derived column that contains just the file date as YYYYMMDD.

There's a SQL Table that contains Currency Rates, indexed by currency code and date, in DD/MM/YYYY format, but it's stored as a VARCHAR

My problems:

A.) I tried to convert the string, '07302010' into the following formats, without success:





However, none of these can understand the string '20100730' as a date!


B.) Furthermore, if i convert the field into a DTW_STR, it still doesn't match the varchar(50) in my db.


So, basically, despite using all available typecast date formats, and several string formats as well, I can't find a way to

A.) Parse text out of a field

B.) Convert that text to either a DATE format or a TEXT format

C.) Use that resulting field to

SSIS 2008 data type bug



I am using SSIS 2008, I set a varaible @t1 to data type CHAR and set the value to Y next I used an Execute SQL Task with an ADO.NET connection to SQL 2008 R2 database to insert this the value of @t1 into a test table with only 1 column of data type NCHAR(1):

insert into test1

On the Parameter Mapping section of this task I put the Data Type as String and initially set the Parameter size to -1  

The only problem is it tries to insert a 2 character numeric value into the SQL table. If I set the value of @t1 to N the parameter mapping seems to convert this to 78, when I set the value of @t1 to Y the parameter mapping converts this to 89 it looks like it converts the character to an ASCII code.

Is this meant to be the intended behaviour, as all I wanted to do was insert the actual character into the destination column and not the ASCII code?



User Control Web Part - The type initializer for 'IBM.Data.DB2.DB2Connection' threw an exception.



I need help, in our SharePoint 2007 project we have created Web Part using VS 2005 & .Net 2.0, User Control. In the user control for one of the drop down list box databinding method we are using data from IBM DB2. It works fine on our local machine, but after Web Part deployment if i open the page then the Drop Down List is empty. I checked the log in the event viewer. I found the following error message in the vent viewer -

The type initializer for 'IBM.Data.DB2.DB2Connection' threw an exception.


Detail Error Message -

An unhandled exception occurred and the process was terminated.

Application ID: /LM/W3SVC/2076212728/Root

Process ID: 26880

Exception: System.TypeInitializationException

Message: The type initializer for 'IBM.Data.DB2.DB2Connection' threw an exception.

StackTrace: at IBM.Data.DB2.DB2Connection.Finalize()

InnerException: System.InvalidOperationException

Message: SQL1159 Initialization error with DB2 .NET Data Provider, reason code 7, tokens 9.1.1.DEF.2, SOFTWARE\IBM\DB2\InstalledCopies

StackTrace: at IBM.Data.DB2.y.f.a()

at IBM.Data.DB2.DB2ConnPool.g()

at IBM.Data.DB2.DB2ConnPool..ctor()

at IBM.Data.DB2.DB2Connection..cctor()

For more information, see Help and Support Center at h

Text was truncated with "nvarchar(max)" data type and size by using SSIS Import and Export Wizar



I am using SSIS Import and export Wizar to load data from Excel file, I have set the datatype as "nvarchar(max); those data are not biger than 1000 bits but was failed to transfer as the error says:

[Source - TreatmentPlans$ [1]] Error: The "output column "SchoolPlan" (57)" failed because truncation occurred, and the truncation row disposition on "output column "SchoolPlan" (57)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

checking data types in SSIS

hi i have a "gender" column in my database in which o/1 value exists.i want to change 0 to male and 1 to female in SSIS.
2) i also have DOB table in which dates are in dd-mmm-yy format.i want to chechk some of them are erronous like 31-feb-90 ,22-apl-88,21/03/67.
I want to know method how to do remove these erronous data? how to convert it?
how to send dirty data to "error table" ???
need quick response plzzzzz

SSIS with IBM OLE DB Provider for DB2 (SQL Command data access mode)


I have SSIS package which gets the data from DB2 servers, and I am using "IBM OLE DB driver for DB2" in source connection provider, which is mandatory to use in my case. And my data access mode is "Sql command". When I clicked on columns in OLE DB source , I am getting the following error message. But its working fine when i select data access mode as "Table\view". Please help me in this as soon as possible. Please look at the following error description.


Error at Data flow task [OLE DB SOurce]: An OLE DB error has occurred. Error code: 0x80040E21.

 (Microsoft Visual Studio)


Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)


Sateesh Maduri

data type conversion using ssis



i am migrating from oracle to sql i am getting data type format error while importing data from oracle to sql for date datatype in oracle. I was using SSIS with data conversion task.

IS there any method i can use to resolve the error with date format?

Thanks in advance


2008 SSIS and NUMA Memory issues and BLOB Data Types


We're running SSIS 2008, moving data from an Oracle 10g database to a SQL 2008 database.  The SSIS is running on the same machine as the SQL Destination server.  The server has 8 gigs and is windows 2003 sp2

The issue we're having is when our package pulls a blob data type from oracle it will just quit with no errors at about 1.4 million records.   We know there are 6 millions records in the dataset. 

A friend of mine said it was a NUMA Memory issue that it is running out at some point and SSIS thinks the incoming data is finished.  Unfortunately, she said there was no answer. 

I was wondering if someone else had a simliar situation moving blobs from Oracle?


(SSIS 2008) What are the precise numerical sizes (in bytes) of the date data types in SSIS?


I have been reading the famous Integration Services: Performance Tuning Techniques document and I want to use the guidance in the Buffer Sizing section.

In order to optimize my settings for DefaultMaxBufferRows and DefaultMaxBufferSize, I need to calculate the Estimated Row Size for my Data Source.

However, when I look to the Integration Services Data Types document I find that several of the data types do not explicitly list their size in bytes.

(DT_BOOL also isn't listed but the assumption must be it's one bit)

Does anyone know how big (in bytes) these data types are? The Estimated Row Size can't be found without them.


Peter Kral

Validation on data type



I am trying to realize valition on datatype. I have used DataAnnotations, but for data type it's not showing customized message

 for example when I' am trying enter string data into int typed field. How I can customize messages in this case?

Declaration of validation.

public partial class GPS_Mark { } 

public class MarkValidation { 
 [DataType(DataType.Currency, ErrorMessage = "This field should be numeric")] 
 public int MarkE{ get; set; }

It's my view

<div class="editor-label">
 <div class="editor-field">
   @Html.ValidationMessageFor(model => model.MarkE)

When user would enter into this field somethihg like: adsf

He would see: "The field MarkE must be a number", not my "This field should be numeric". The mean the same but I need show costumized



[Range(-15, 15, ErrorMessage = "Enter digit")] 

also not helped


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