none
Text to column by using C# code in Excel in Automate the SSIS Package

    Question

  • Hi,

    I have innovative idea in automation in SSIS as per Requirement in my project.

    I have been working  data Conversion assignments from Raw file

    So in a Data flow (Excel to OLedb), Some regularly arising  problems are

    1. Text to column in excel( to avoid NULL value in OLEDB from Excel Source)

     (Even I applied in IMEX=1 in connection string....,)

    So I got idea to Run the Macro code on Excel sheet , then it solved but it is a manual process.

    Now I desire to Automate the Macro code RUN on Excel sheet at required column by using C# code(Script Task latter ) by adding Script Task (before Data flow) in a SSIS package

    but I new to C# so please provide the C# code to Macro code RUN on Excel sheet at required column.

    Thanks

    Regards

    MADHU

    Monday, November 04, 2013 5:36 AM

All replies

  • The problem with calling a macro is that you probably have to install office on the machine that is running the macro, which is not recommended/supported.

    Adding IMEX=1 isn't a solution for null values. If for example the first 8 rows are numbers and the rest are strings then it will still result in a lot of null values.

    See:
    http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with
    http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html

    And the Excel forum is probably a better place to get your answer...


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


    Monday, November 04, 2013 7:39 AM
    Moderator
  • Thank your,

    Above link is very helpful to me but I have to Configure in Script task in SSIS

    So Please help with :

     Configure Script  Task Editor:

    Entry Point: Main

    Read Only variable: ???

    Read Write variables: ????

    Provide the Script in C# ::

    ---

    --------///for  required column:A1,A2,A9.....AA,Ab....like/////

    Please provide above all (If you dont mine)

    Thanks

    -Madhu

    Monday, November 04, 2013 8:42 AM
  • Thank your,

    Above link is very helpful to me but I have to Configure in Script task in SSIS

    So Please help with :

     Configure Script  Task Editor:

    Entry Point: Main

    Read Only variable: ???

    Read Write variables: ????

    Provide the Script in C# ::

    ---

    --------///for  required column:A1,A2,A9.....AA,Ab....like/////

    Please provide above all (If you dont mine)

    Thanks

    http://technet.microsoft.com/en-us/library/ms141752.aspx

    Variables refer to the SSIS variables which you can access/use within the Script Task. If you don't use the variables then you can leave those field empty.

    In the main method you can add your code for the Excel macro, but if you don't have any programming skills then the Script Task is probably not the best solution for you...


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

    Monday, November 04, 2013 8:53 AM
    Moderator
  • Thanks for above 2 Reply posts.

    I got  idea to Resolve:

    1.IMEX=1

    2.Type Guess Row=0(Might be if Row count()<16384)

    apart from Out side Task, I desire to Customize(Automation) Script Task/Custom component to avoid Excel DT errors/Manual Operation.

    I  can understand C# and can do edit also but innovative  ???

    So please help(PlZ HELP) on this C# in below mentioned code.

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    
    namespace ST_0c5451515deb4d9e8f101954604cda13.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
    
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
            public void Main()
            {
    
    //////------------------------------------------------------------------------------------
      ///////////PLZ HELP c# Code on  Macro RUN in Excel sheet :A,G, AA, AJ..,/////////
    ///--------------------------------------------------------------------------------------------
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }
    }
    

    Thanks

    -MADHU

    Monday, November 04, 2013 9:27 AM
  • And now start cutting, pasting, testing, debugging the example code provided by RSingh...

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

    Monday, November 04, 2013 11:25 AM
    Moderator