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


Top 5 Contributors of the Month
david stephan
Fauzul Azmi
Asad Ali
Post New Web Links

How to use SSIS to perform Case Statement operation

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

Hi all

i am working SSIS from Past three months now i want know how to build the

package so that it performs the following Case Statement operation

please help

SELECT SchemeCode=
  CASE  SLF.Loan_Type
  WHEN  'C' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='CASH CREDIT LOAN')
  WHEN  'U' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='CUMULATIVE DEPOSIT LOAN')
  WHEN  'J' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='DHANALAXMI CASH CERTIFICATE LOAN')
  WHEN  'R' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='DHANALAXMI CASH CERTIFICATE LOAN')
  WHEN  'Q' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='FIXED DEPOSIT LOAN')
  WHEN  'Z' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='FIXED DEPOSIT LOAN')
  WHEN  'D' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='FIXED LOAN')
  WHEN  'N' THEN(SELECT Schemecode FROM Mig_BankSoft.dbo.Mig_SchemeDefinition where SchemeName='FIXED LOAN')
  WHEN  'P' THEN(SELECT Schemecod


View Complete Post


More Related Resource Links

ToggleDebugCompilation fails trying to perform "an unauthorised operation"

  

Hi,

We're having a problem with the Visual Studio 2008 RTW version of the web deployment projects on a colleagues machine. The project works fine if I run it on my machine, but when he tries to run it on his machine he gets:

<SNIP : Most output removed>
  Task "Exec"
    Command:
    if exist ".\TempBuildDir\" rd /s /q ".\TempBuildDir\"
  Done executing task "Exec".
  Using "ToggleDebugCompilation" task from assembly "C:\Program Files\MSBuild\Microsoft\WebDeployment\v9.0\Microsoft.WebDeployment.Tasks.dll".
  Task "ToggleDebugCompilation"
    Updating Web.config <compilation> element debug attribute to 'True'.
    C:\Program Files\MSBuild\Microsoft\WebDeployment\v9.0\Microsoft.WebDeployment.targets(639,7): error : Attempted to perform an unauthorized operation.
    C:\Program Files\MSBuild\Microsoft\WebDeployment\v9.0\Microsoft.WebDeployment.targets(639,7): error MSB4018: The "ToggleDebugCompilation" task failed unexpectedly.
    C:\Program Files\MSBuild\Microsoft\WebDeployment\v9.0\Microsoft.WebDeployment.targets(639,7): error MSB4018: System.ArgumentNullException: Parameter "message" cannot be null.
    C:\Program Files\MSBuild\Microsoft\WebDeployment

SWITCH...CASE STATEMENT

  

Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
{
 
    if (Page.IsPostBack)
    {
    public String toSring()
    {
    int intcomputerChoice=1;
        
         if(computerChoice==1)
         {
         return(Rock);
       }
       if(computerChoice==2)
         {
         return(Paper);
       }
       if(computerChoice==3)
         {
         return(Scissors);
       }
       }
       if (Page.IsPostBack)
      {

Error "user does not have permission to perform this operation" with User Instance = True.

  

I setup SQL Server 2005 / SQL Express. All works fine in SQL Server 2005.

I am connecting to a SQL Database from VS 2005,  Tools/Connect to Database. I set the Data Source to "Microsoft SQL Server Database File (SqlClient)" (meaning SQL Server Express) and User Instance = True, and Integrated Security = True.

The above is working successfully on all machines except my Laptop. On my Laptop, I am getting this error "user does not have permission to perform this operation". On my Laptop, if I change User Instance = False, it works fine. Also, If I connect to SQL Server 2005 it works fine only if User Instance = False.

I tried all kind of security changes, nothing worked. For example, I setup Everyone as Full Control for the Folder and the MDF File of SQL Server. I gave my user ID as sysadmin role in SQL Server Express. Also, I enabled creating User Instance form SQL Server Express, Nothing is working.

Appreciate your help.

Tarek.


Can not perform this operation.The file is no longer checked out or has been deleted?

  
In my sharepoint designer 2007, when I try to check in a file, it give me the following errors:

Can not perform this operation.The file is no longer checked out or has been deleted.

Please advise me how to fix this problem?

Thanks.

Case statement is never executing

  

Can some please advise as to why the case statement is never running

 

Imports Microsoft.VisualBasic
Imports AjaxControlToolkit


Public Class MultilevelAccordion
    Inherits System.Web.UI.UserControl
    Implements System.Web.UI.ITemplate

    Dim templateType As AccordionItemType

    Sub New(ByVal type As AccordionItemType)
        templateType = type
        MsgBox(templateType.ToString)

    End Sub

    Sub New()

    End Sub

    Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) _
          Implements System.Web.UI.ITemplate.InstantiateIn

        Dim ph As New PlaceHolder()

        Select Case (templateType)
            Case AccordionItemType.Header
                Dim myLabel As Label = New Label()
                myLabel.ID = "HeaderLabel"
                MsgBox("header")


                ph.Controls.Add(myLabel)
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
            Case AccordionItemType.Content
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
        End Select
        container.Controls.Add(ph)
    End Sub

    Shared Sub Item_DataBinding(ByVal sender As Object, ByVal e As AccordionItemEventArgs)

        Dim ph As PlaceHolder = CType(sender, PlaceHolder)
        Dim headerValue As String = _

LINQ to SQL and Case statement

  
Working with LINQ I realized that I had to use the simple case statement in my SQL query. There is no special keyword for this. To create a case statement like structure you will have to do it in the select section of the query. Below is an example of the usage of the case statement in LINQ. var t = from n in idc.categories            select new             {                        catName =                         (n.id==1 ? "Cat1" :                        n.id==2 ? "Cat2" :                        n.id==3 ? "Cat3" : "Unknown Category"                        )             }; Here in the above code we are using multiple cases for value 1, 2 and 3

Error "user does not have permission to perform this operation" with User Instance = True.

  
I setup SQL Server 2005 / SQL Express. All works fine in SQL Server 2005. I am connecting to a SQL Database from VS 2005,  Tools/Connect to Database. I set the Data Source to "Microsoft SQL Server Database File (SqlClient)" (meaning SQL Server Express) and User Instance = True, and Integrated Security = True. I am using Windows Authentication mode in SQL Server Express. The above is working successfully on all machines except my Laptop. On my Laptop, I am getting this error "user does not have permission to perform this operation". On my Laptop, if I change User Instance = False, it works fine. Also, If I connect to SQL Server 2005 it works fine only if User Instance = False. I tried all kind of security changes, nothing worked. For example, I setup Everyone as Full Control for the Folder and the MDF File of SQL Server. I gave my user ID as sysadmin role in SQL Server Express. Also, I enabled creating User Instance form SQL Server Express, Nothing is working. I am already added to the Administrator Group locally on my Laptop. Still same problem. Checked this article: http://msdn.microsoft.com/en-us/library/ms254504.aspx, same problem. I enabled User Instances and also tried to Disable User Instances. When I disable user instances, and then try to connect from VS, I get a different error, which is asking to enable user instances.

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

Query help to write case statement in Where clause?

  
Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Two Output Columns from One CASE Statement

  
I have some query logic that needs to return two dynamically created columns based on two conditional tests so that - If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2. - If Test1 is False then Test2 determines the value of Column2 Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1.  I was hoping that someone might have a different approach that would reduce the amount of testing being performed in the SQL.  I did see the post at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask. For what it's worth I'm posting the heart of my logic below.  The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access.  @Access will end up as a parameter in the stored procedure once I finalize this. DECLARE   @Access tinyint = 1 SELECT   Holding.HoldingID, ShortTitle, Access,   CASE     WHEN @Access &[Access] = @Access THEN (URL + [FileName])     ELSE null   END AS [HoldingURL],         CASE

can create table statement be used in and ssis expresssion?

  
I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic

Oracle dataset CASE statement

  
Hi,I'm trying to create a dataset linking to Oracle, but I'm having problems getting a case statement to work in the query.It works fine in SQLPlus, Sql developer.When I try and add the statement it either puts single quotes around the statement, or throws the error 'Invalid or missing expression'.Here is the query in question:SELECT DISTINCT (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time') WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time') ELSE DEPARTMENT.DESCRIPTION END) AS DEPARTMENT FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID AND COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID

Case statement in where clause

  
i trying following querySelect  FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email,  CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID  From ClientDetail with (nolock)  Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And  if(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted')) ELSE  (ClientStatus Is Null OR Lower(ClientStatus) = 'active') )  Order By ClientDetail.CreationDate DESC ;Select FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email, CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID From ClientDetail with (nolock) Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And case(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted'))ELSE (ClientStatus Is Null OR Lower(ClientStatus) = 'active') ) Order By ClientDetail.CreationDate DESC ;but is gives an error plz help

How to use switch - case statement in T-SQL..?

  
Hi, I want to use switch - case statement in T-SQL stored procedure. Can any one help regarding the same..?   for e.g. switch (exp) { case 1 : stmt 1; break; case 2 : stmt 2; break; case 3 : stmt 3; break; & so on....... }

Case Statement Evaluating 2 Conditions

  
Hello, I have a field A and B. I want A to be filterered based a condition  When B= 2 then  A should not be in (1,2,3,4) When B=1 then give me back all of A (no filters) I am trying to do something like: Case when B=2 then A Not in (1,2,3,4) else A How do I incorporate this in my case statement? Or do I need some other way to do this? Please help. Thanks to all in advance! KJ

CASE statement

  
Hi all, I have quite a few of the below in my query (not the same case statement but similar): CASE WHEN [funcyion_key] <> 2 THEN SUM(portfolio_amt]) ELSE 0 END as portfolio_amt Is there a better way of doing the above? please help

Error 14258: Cannot perform this operation while SQLServerAgent is starting. Try again later.

  
I'm getting the following error on two of my SQL Servers - Error 14258: Cannot perform this operation while SQLServerAgent is starting. Try again later. The only solution that I have seen is the same solution that Microsoft has published in the following article - KB303287 [http://support.microsoft.com/?scid=http%3a%2f%2fwww.support.microsoft.com%2fkb%2f303287%2fen-us%2f] The solution is to unflag the use of NT Fibers.  However, our software application provider is advising us against doing this.  Is there another fix anywhere that I could get my hands on - another solution? Thanks in advance.
Categories: 
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