.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

Variable types are strict, except for variables of type Object- SSIS 2008

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

Hi , I declared a user variable MINIODate  as string in package level and assigned this variable in an execute sql task 2008 , where my source is ado.net and sql command is Select CSTR(MIN(IODate) ) as MINIODate  from IOData ( This is source table available in MSAccess DB) , The above senario working fine in 2005. During the run time in 2008  i am getting the following error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MINIODate": "The type of the value being assigned to variable "User::MINIODate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

View Complete Post

More Related Resource Links

SSIS 2008-Variable Expression Type Cast Syntax Error


Hi all members.

I need to ask for the following code. I need to convert the variable into a string.

"Select Distinct convert (int ,A.CustomerCategory) as AccountReceivableCategoryID, convert (varchar(40), B.Text) as AccountReceivableCategoryNameE
from Inkunde A , inpara B
where A.CustomerCategory= B.SearchItem
and B.Language1 = 'EN'
and B.ParameterName = 'CATEGORY'
and A.CustomerCategory = (DT_STR,200,1252) "+  @[User::CategoryID] +"
Order By A.CustomerCategory"

Kindly any member let me know, How to correctly use the Type Cast fucntion in variables

Thanks and Best Regards,


Using a Variable in SSIS - Error - "Command text was not set for the command object.".

Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.   Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E0C  Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression  Property to true too. Please let me know where am going wrong? Thanks in advance.

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!

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 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?



How to send any collection object from .NET Application to object variable created in SSIS Package


HI All,

I have a question regarding SSIS -- How to send list object from .NET Application to object variable created in SSIS Package. In List object there are one lack records stored..This list object needs to be passed to SSIS Package. Please reply me back with any suggestion/solutions ...Required ASAP.

Waiting for your replies.





foreach statement cannot operate on variables of type 'object' because 'object' does not contain



Still struggling with this code Frown


I'm trying to follow an example in a book i'm reading, but the example is using linq to sql, i'm trying to use linq to xml. Problem is i get the following error:

Error 1 foreach statement cannot operate on variables of type 'object' because 'object' does not contain a public definition for 'GetEnumerator' d:\MVC3_Website\Website.WebUI\Views\Home\Index.aspx 11 8 Website.WebUI


My code is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Website.Domain.Models.Weather.Entities
    public class WeatherForcast
        public string loc { get; set; }
        public string loc_id { get; set; }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using Website.Domain.Models.Weather.Abstract;
using Website.Domain.Models.Weather.Entities;

namespace Website.Domain.Models.Weather.Concrete
    public class WeatherRepository : IWeatherRepository
        public IEnumerable<WeatherForcast> GetWeatherCity(string strWeatherCity)

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

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

type object parameter



I have a problem I can't figure out.

I've been trying to add a listbox with multi select enabled as a parameter to my select statement in sqldatasource.

So i created a function like this :

string strItemTypes = "";
foreach (ListItem li in lbItemType.Items)
   if (li.Selected)
      strItemTypes += "," + li.Value;
strItemTypes = strItemTypes.Substring(1);

which gives me the selected items like this for example : "1,2,3"...
Then I've been trying to add it to my sql select statement :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name, tblStockItems.Description, tblStockItemTypes.TypeItem, tblStockItems.Notes FROM tblOfficesItems INNER JOIN tblStockItems ON tblOfficesItems.fkItem = tblStockItems.idItem INNER JOIN tblStockItemTypes ON tblStockItems.fkTypeItem = tblStockItemTypes.idTypeItem WHERE tblStockItems.fkTypeItem IN ( @fkTypeItems )

using a hiddenfield with a controlparameter of type=object
but it just don't seem to work, seems like the data just won't bind or something and I don't get any errors so I'm confused!!

then i tried this :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name

Cannot convert type 'object[]' to 'byte[]'


I am doing this .. and i want to convert object array to byte array. How should I do that?

Object[] myVal = um.myData;

Byte[] byteArray = (Byte[])myVal;

Basic Instincts: Type inference in Visual Basic 2008


Visual Basic 2008 provides type inference to get the language ready to take advantage of LINQ and make your code more robust.

Bill Horst

MSDN Magazine October 2007

Types in Yukon: Managed UDTs Let You Extend the SQL Server Type System


The next version of SQL Server, code-named "Yukon," will offer enhanced support for user-defined types (UDTs). Because UDTs can be managed by the CLR, you can represent a wide variety of data structures to create types not possible with previous versions of SQL Server. With UDTs you can more precisely control the kind of data your type accepts, resulting in better data management. This article explores UDTs in "Yukon" and covers their design and implementation.

Peter W. DeBetta

MSDN Magazine February 2004

Unable to cast object of type 'System.Web.Mvc.ValueProviderResult' to type 'System.IConvertible'


 Why am I getting this error on line:

               sp.Price = Convert.ToDecimal(collection.GetValue("Price"));

sp is :
  SPecials_Templates sp = new SPecials_Templates();

SPecials_Templates Defined:

    public partial class SPecials_Templates
        public class SPecials_TemplatesMetaData

            public object Key { get; set; }
            public object Template_Name { get; set; }
            public object Headline { get; set; }

BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its


why do i get

BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its base types.

with the code

 below ..thanks


<form id="form1" runat="server">
               <asp:Button ID="Button1" runat="server" Text="Run Access" />
In your code behind:

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