none
How to write query to concatenate two columns of excel in ssis package

    Question

  • Hi,

    I am imoprting excel data into three tables. Now i need to concatenate two column(First name and Last name).

    No how can write a query in Excel Source using ssis

    Tuesday, April 02, 2013 2:33 PM

Answers

  • Excel source is unable to conect to the source file. Can you check if there is any expression set for excel connectin manager? Are you able to configure the excel source properly?


    Nitesh Rai- Please mark the post as answered if it answers your question

    • Marked as answer by vsp.santu Wednesday, April 03, 2013 5:52 AM
    Tuesday, April 02, 2013 3:57 PM

All replies

  • The query language for excel sources in SSIS is very limited. It's probably not possible or giving you problems, but try something like a normal tsql concatenation F1 + ' ' + F2 as fullname.
    Best alternative is to use a Derived Column.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Tuesday, April 02, 2013 2:40 PM
    Moderator
  • Hi,

    I am writing query like as below

    select DISTINCT id, name, mob, firstname + ' ' + lastname as fullname

    from Sheet$.

    But i am getting error as below

    "Syntax error (missing operator) in query expression

    id

    name

    mob

     firstname + ' ' + lastname as fullname

    What's the problem

    Tuesday, April 02, 2013 2:48 PM
  • Hi,

    Can u please tell me

    Tuesday, April 02, 2013 2:59 PM
  • 
    Select Distinct  <columns>, FName+" "+LName as fullname from sheet1$

    Nitesh Rai- Please mark the post as answered if it answers your question

    Tuesday, April 02, 2013 3:02 PM
  • Start with a very basic query and add more items if the query is successful

    select firstname from Sheet$
    
    select firstname as fullname from Sheet$
    
    select firstname + ' ' lastname as fullname from Sheet$
    
    select distinct firstname as fullname from Sheet$
    


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, April 02, 2013 3:04 PM
    Moderator
  • Hi,

    Still i am getting error. My actual query as below

    SELECT DISTINCT 

    Id
    Name
    Sales_Stage
    Primary_Sales_Rep_Login
    Product
    Description
    First_Name + ',' + Last_Name AS Full_Name,
    Street_Address + ',' + Street_Address_2 + ',' + Street_Address_3 AS Street,
    City
    State
    Postal_Code
    Country 
    Territory
    Account_Id 
    Account
    Main_Phone_Number
    Main_Fax_Number
    Record_State 
    HP_Customer_Agreement_
    Legacy_opportunity_ID 
    LegacyAccount_ID 
    LegacyProduct 

    FROM SiebelIndigoData$
     

    And this query writing in Excel Source Editor

    Tuesday, April 02, 2013 3:06 PM
  • I tried your code,

    But i am getting error as 

    Syntax error in FROM clause

    Tuesday, April 02, 2013 3:12 PM
  • Please tell me anybody. 
    Tuesday, April 02, 2013 3:20 PM
  • Hi,

    I changed like below

    SELECT DISTINCT 
                             Id, Name, Sales_Stage, Primary_Sales_Rep_Login, Product, Description, 'First_Name' + ' ' + 'Last_Name' AS Full_Name, 
                             'Street_Address' + ' ' + 'Street_Address_2' + ' ' + 'Street_Address_3' AS Street, City, State, Postal_Code, Country, Territory, Account_Id, Account, Main_Phone_Number, 
                             Main_Fax_Number, Record_State, HP_Customer_Agreement_, Legacy_opportunity_ID, LegacyAccount_ID, LegacyProduct,
    FROM            SiebelIndigoData $

    But still i am getting below error

    Missing or incomplete SELECT clause.
    Unable to parse query text.

    Tuesday, April 02, 2013 3:27 PM
  • Put table name in [] as: from [SiebelIndigodata$]

    Nitesh Rai- Please mark the post as answered if it answers your question

    Tuesday, April 02, 2013 3:27 PM
  • Hi

    tried like this 

    [tblOLPB] as: from [SiebelIndigoData $]

    But i am getting below error

    Missing or incomplete SELECT clause.
    Unable to parse query text.

    Tuesday, April 02, 2013 3:30 PM
  • Your query is incorect. Try this:

    SELECT DISTINCT 
    Id, Name, Sales_Stage, Primary_Sales_Rep_Login, Product, Description, 'First_Name' + ' ' + 'Last_Name' AS Full_Name, 
    'Street_Address' + ' ' + 'Street_Address_2' + ' ' + 'Street_Address_3' AS Street, City, State, Postal_Code, Country, Territory, Account_Id, Account, Main_Phone_Number, 
    Main_Fax_Number, Record_State, HP_Customer_Agreement_, Legacy_opportunity_ID, LegacyAccount_ID, LegacyProduct
     FROM   [SiebelIndigoData$]


    Nitesh Rai- Please mark the post as answered if it answers your question

    Tuesday, April 02, 2013 3:36 PM
  • Hi,

    Thanku so much its working,

    And i add mulitcast and 3 OLEDB Destination.

    After debuggin i am getting error as below

    [Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    [SSIS.Pipeline] Error: One or more component failed validation.

    Tuesday, April 02, 2013 3:47 PM
  • Excel source is unable to conect to the source file. Can you check if there is any expression set for excel connectin manager? Are you able to configure the excel source properly?


    Nitesh Rai- Please mark the post as answered if it answers your question

    • Marked as answer by vsp.santu Wednesday, April 03, 2013 5:52 AM
    Tuesday, April 02, 2013 3:57 PM
  • Hi 

    Thanku nitesh package working fine.

    But in sql table Full name column the data is displaying First_Name Last_Name and Street column also.

    Its not displaying data, but remaining columns are ok 

    Tuesday, April 02, 2013 3:59 PM
  • That's because you have written a SQL Statement to concat two strings First Name and Last Name, not values of two columns

    Regards, Dinesh


    SELECT 'First_Name' + ' ' + 'Last_Name' AS Full_Name
    FROM TABLE1 
    --Not the Same as
    SELECT First_Name + Last_name AS Full_Name
     FROM TABLE1

    Tuesday, April 02, 2013 6:29 PM
  • Hi Dinesh, I tried this SELECT First_Name + Last_name AS Full_Name Now name is working. But Street is not working. It showing NULL, I wrote like as below Street_Address + Street_Address_2 + Street_Address_3 AS Street
    Wednesday, April 03, 2013 3:10 AM
  • And in excel sheet total records are 10225. After i debugged the package its showing 10225 rows correctly, but in tables showing only 1000 records.
    Wednesday, April 03, 2013 3:32 AM
  • Hi,

    Please tell me anybody below two issues.

    1. I tried this SELECT First_Name + Last_name AS Full_Name Now name is working. But Street is not working. It showing NULL, I wrote like as below Street_Address + Street_Address_2 + Street_Address_3 AS Street

    2. And in excel sheet total records are 10225. After i debugged the package its showing 10225 rows correctly, but in tables showing only 1000 records.

    Wednesday, April 03, 2013 4:03 AM
  • Hi, 

    I am importing excel data into three tables using SSIS Package. 

    I used DataflowTask, Excel Source, Multicast, OLEDB DESTINATION three times. 

    After debugging, my issues are 

    1. I tried this SELECT First_Name + Last_name AS Full_Name Now name is working. But Street is not working. It showing NULL, I wrote like as below Street_Address + Street_Address_2 + Street_Address_3 AS Street 

    2. And in excel sheet total records are 10225. After i debugged the package its showing 10225 rows correctly, but in tables showing only 1000 records. 

    And i wrote query in ExcelSource 

    SELECT DISTINCT 
    Id, Name, Sales_Stage, Primary_Sales_Rep_Login, Product, Description, First_Name + Last_Name AS Full_Name, 
    Street_Address + Street_Address_2 + Street_Address_3 AS Street, City, State, 
    Postal_Code, Country, Territory, Account_Id, Account, Main_Phone_Number, 
    Main_Fax_Number, Record_State, HP_Customer_Agreement_, Legacy_opportunity_ID, LegacyAccount_ID, LegacyProduct 
    FROM [SiebelIndigoData$] 
    • Merged by SSISJoostModerator Wednesday, April 03, 2013 5:29 AM Please don't create a new thread if the other one is still open.
    Wednesday, April 03, 2013 4:26 AM
  • Hi,

    Please tell me anybody.


    Wednesday, April 03, 2013 5:14 AM
  • Hi Nitesh,

    Can u help me out

    Wednesday, April 03, 2013 5:38 AM
  • Whenever you concatenate the fields, you should check for NULL values also. You are getting corect values for FullName but you might end up getting NULL in case either of FirstName or LastName is NULL (or both are NULL). Same is the case for Street. What I would suggest is to have a derived column after excel source to get the concatenated values (Street and FullName):

    Expression for FullName is

    (DT_STR,40,1252)((ISNULL(FName) ? "" : FName)

    + "-" + (ISNULL(LName) ? "" : LName))

    Similarly you can do for Street also


    Nitesh Rai- Please mark the post as answered if it answers your question

    Wednesday, April 03, 2013 8:12 AM
  • If you see carefully. you have added one more column just below the Full_Name which is orpahn; it has neither name nor expression and lot more. I think you don't need this. Simply delete it and then click OK

    Nitesh Rai- Please mark the post as answered if it answers your question

    Wednesday, April 03, 2013 9:33 AM