.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 2008-Variable Expression Type Cast Syntax Error

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

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,


View Complete Post

More Related Resource Links

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


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.

Syntax error (missing operator) in query expression

Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

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.

A MOF syntax error occurred - SQL Server ENT 2008 R2 on Windows Server 2008 R2

Having trouble getting SQL Server 2008 R2 installed on a Windows Server 2008 R2. Getting an MOF syntax error at this point durring the installation "SqlEngineConfigAction_install_confignonrc_Cpu64". Below is the section of the Details.txt file that includes the error. Any help on this would be greatly appreciated.   2010-07-28 10:51:35 Slp: Running: C:\Windows\system32\WBEM\mofcomp.exe "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof" 2010-07-28 10:51:35 Slp: Microsoft (R) MOF Compiler Version 6.1.7600.16385 2010-07-28 10:51:35 Slp: Copyright (c) Microsoft Corp. 1997-2006. All rights reserved. 2010-07-28 10:51:35 Slp: Parsing MOF file: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof 2010-07-28 10:51:35 Slp: MOF file has been successfully parsed 2010-07-28 10:51:35 Slp: Storing data in the repository... 2010-07-28 10:51:36 Slp: An error occurred while processing item 17 defined on lines 201 - 222 in file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof: 2010-07-28 10:51:36 Slp: Compiler returned error 0x800706beError Number: 0x800706be, Facility: Win32 2010-07-28 10:51:36 Slp: Description: The remote procedure call failed. 2010-07-28 10:51:36 Slp: 2010-07-28 10:51:36 Slp: Sco: Compile operation for mof file C:\Program Files\Microsoft SQL S

SQL Server 2008 Script Componant Error - [SSIS.Pipeline] Error: component "Script Comp Name" (48) fa

Hi All,   I am facing one strange issue in SSIS 2008. I have developed one SSIS package which is importing excel data into SQL Server 2008. This Package contains some Scirpt componants. This package is working fine on Windows XP machine but when I am trying to run on Windows 2003 server it gives me error "[SSIS.Pipeline] Error: component "SCR RDSTableRelation" (48) failed the post-execute phase and returned error code 0x80004002". Surprising thing is that if on windows server 2003server  if I drag a new script componat and paste the previous script code itself then it works fine. Even if I copy-paste the existing script componant and give source-destination connectino to this new script componant then also it works.

Arithmetic overflow error converting expression to data type smallint

Can anyone tell me what exactly this means? Arithmetic overflow error converting expression to data type smallint. 

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?



SSIS 2008 R2 SCD error


Dear All,

I have designed a packge using SSIS 2008 and populated 100 records first day it was worked fine.

Secound there 100 old records(some records are changed ) and 5000 new records and started load the data. When i start the package i am getting the below error.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Slowly Changing Dimension" (1354) failed with error code 0xC0202009 while processing input "Slowly Changing Dimension Input" (1365). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.




- Thiru

Type to Variable Error



I am getting this Error: is a type but is used as  a 'Variable'. Please let me know how to resolve this.

Thanks in advance.


SSIS -2008 R2 - ODBC Connection error - Password does not save


Please know that I am new to SSIS 2008 and XML. Have a package I am trying to deploy. Am on a HV 64 Bit environment SQL Server 2K8 R2. Have an ODBC connection to our HBOC system as a data source. As always package runs in test environment and not in prod.  I need a bit of insight into the what and why's of the configuation file and how to change out the XML. 

Main issue is the password not saving and how to handle the ODBC side of things. I appreciate any help in this matter as I have searched with no real understanding of XML and best practices of creating the Congif file.


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

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




Plenty .... Type Cast Error




    In my first scene I am fetching data from database.

string conname = "Data Source=xxx;Initial Catalog=programtracking;Integrated Security=True";

string tp = "select count(*) as tpc from tracking where netlevel='02' ";

string dp = "select count(*) as dpc from tracking where netlevel='02' and status='1'";

string dpl = "select date from tracking where netlevel='02' and status='0'";

System.Data.SqlClient.SqlConnection con1 = new System.Data.SqlClient.SqlConnection(conname);

System.Data.SqlClient.SqlCommand tpcmd = new System.Data.SqlClient.SqlCommand(tp, con1);

System.Data.SqlClient.SqlCommand dpcmd = new System.Data.SqlClient.SqlCommand(dp, con1);

System.Data.SqlClient.SqlCommand dp1 = new System.Data.SqlClient.SqlCommand(dp1, con1);
 System.Data.SqlClient.SqlDataReader tpdata;

   System.Data.SqlClient.SqlDataReader dpdata;

   System.Data.SqlClient.SqlDataReader dpdata1;


                               tpdata = tpcmd.ExecuteReader();

                               dpdata = dpcmd.ExecuteReader();

                               dpdata1 = dpcmd.ExecuteReader();

                               while (tpdata.Read())


SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site


I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?



George the DBA

Error on mapping oracle varchar2 to sql server 2008 using import facility, defaults to type 129 in m



I am trying to use the import wizard to import an oracle 10g table into an sql server 2008 (10.0.2531) database, it comes up with the error 'data types of source columns were not mapped correctly to those available on the destination provider'. When I go into 'edit mappings' it shows all the varchar2 oracle columns mapping to type 129 ? all the other columns have normal mapping types. If i go through and change 129 to varchar the import will work ok.

I understand that in c:\Program files\microsoft sql server\100\dts\mappingfiles there is a series of files that map datatypes to/from different sources, there are 3 files

oracleclienttomssql , oracleclienttomssql10 , oracleclienttossis10 which have a varchar2 to nvarchar mapping

and 2 other files  oracletomssql ,  oracletomssql10 which map varchar2 to varchar and one other file, oracletossis10 which maps varchar2 to dt_str

I running the import on a server with an Oracle client installed with sql server 2008 (10.0.2531).

I am not sure which mapping file is being used ? and whichever one is being used it should provide a mapping rather than type 129 (whatever that is).

I would like to get this mapping resolved as I have many tables to import and over time the structure of the Oracle tables will change so I want to use import as the easiest optio

Error occurred in deployment step 'Activate Features': Unable to cast object of type


I keep getting this error when trying to deploy a feature to use a customizable master page>  I verified that I my scope is at the web level.

Error 43 Error occurred in deployment step 'Activate Features': Unable to cast object of type 'Microsoft.SharePoint.SPWeb' to type 'Microsoft.SharePoint.SPSite'.
  0 0 FsMECCMaster


using System;

using System.Runtime.InteropServices;

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