Script task not working

  • Hey Everyone,

    I have a script task pulls data from an api I have added splunk client as a reference and it builds perfectly fine.

    When I execute the script task it says

    Error: Cannot execute script because the script entry point is invalid.

    namespace ST_5116c0770a3343b5bc2024cad475731d

    {

    /// <summary>

    /// ScriptMain is the entry point class of the script. Do not change the name, attributes,

    /// or parent of this class.

    /// </summary>

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

    #region Help: Using Integration Services variables and parameters in a script

    /* To use a variable in this script, first ensure that the variable has been added to

    * either the list contained in the ReadOnlyVariables property or the list contained in

    * the ReadWriteVariables property of this script task, according to whether or not your

    * code needs to write to the variable. To add the variable, save this script, close this instance of

    * Visual Studio, and update the ReadOnlyVariables and

    * ReadWriteVariables properties in the Script Transformation Editor window.

    * To use a parameter in this script, follow the same steps. Parameters are always read-only.

    *

    * Example of reading from a variable:

    * DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;

    *

    * Example of writing to a variable:

    * Dts.Variables["User::myStringVariable"].Value = "new value";

    *

    * Example of reading from a package parameter:

    * int batchId = (int) Dts.Variables["$Package::batchId"].Value;

    *

    * Example of reading from a project parameter:

    * int batchId = (int) Dts.Variables["$Project::batchId"].Value;

    *

    * Example of reading from a sensitive project parameter:

    * int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();

    * */

    #endregion

    #region Help: Firing Integration Services events from a script

    /* This script task can fire events for logging purposes.

    *

    * Example of firing an error event:

    * Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);

    *

    * Example of firing an information event:

    * Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)

    *

    * Example of firing a warning event:

    * Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);

    * */

    #endregion

    #region Help: Using Integration Services connection managers in a script

    /* Some types of connection managers can be used in this script task. See the topic

    * "Working with Connection Managers Programatically" for details.

    *

    * Example of using an ADO.Net connection manager:

    * object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);

    * SqlConnection myADONETConnection = (SqlConnection)rawConnection;

    * //Use the connection in some code here, then release the connection

    * Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);

    *

    * Example of using a File connection manager

    * object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);

    * string filePath = (string)rawConnection;

    * //Use the connection in some code here, then release the connection

    * Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);

    * */

    #endregion

    public class DndRecord

    {

    public int Id { get; set; }

    public DateTime DateTime { get; set; }

    public int Extension { get; set; }

    public string Status { get; set; }

    }

    public class Splunkcredentials

    {

    public string username { get; set; }

    public string password { get; set; }

    }

    class Program

    {

    //static HttpClient client = new HttpClient();

    public static List<DndRecord> DndRecords { get; set; } = new List<DndRecord>();

    public class APIResponse

    {

    public int id { get; set; }

    public DateTime DateTime { get; set; }

    public int extension { get; set; }

    public string status { get; set; }

    public List<string> ResponseData { get; set; }

    }

    static async void CreateProductAsync()

    {

    string textFile = @"C:\PCMWSAPPS\INFORMATION_MANAGEMENT\Splunk_Reporting\pass.txt";

    string[] lines = File.ReadAllLines(textFile);

    Splunkcredentials spk = new Splunkcredentials();

    foreach (string line in lines)

    {

    string[] value = line.Split('=');

    string key = value[0].Trim();

    string val = value[1].Trim();

    if (key.Equals("username"))

    {

    spk.username = val;

    }

    else

    if (key.Equals("password"))

    {

    spk.password = val;

    }

    }

    var service = new Service(new Uri("https://splunk.paycomhq.com:8089/services/search/jobs/export"));

    await service.LogOnAsync(spk.username, spk.password);

    string early = "10/17/2019:00:00:00";

    string latest = "10/18/2019:00:00:00";

    //use earlist and latest date from SSIS

    Job job = await service.Jobs.CreateAsync("search index=cisco_net sourcetype=DND_MySQL earliest=" + early + " latest=" + latest + " |eval DateTime=strftime(_time, \"%Y-%m-%d %H:%M:%S.%3N\")|table id,DateTime,extension, status");

    SearchResultStream stream;

    using (stream = await job.GetSearchResultsAsync())

    {

    try

    {

    foreach (SearchResult result in stream)

    {

    //Console.WriteLine(string.Format("{0:D8}: {1}", stream.ReadCount, result));

    DndRecords.Add(new DndRecord

    {

    Id = int.Parse(result.GetValue("id")),

    DateTime = DateTime.Parse(result.GetValue("DateTime")),

    Extension = int.Parse(result.GetValue("extension")),

    Status = result.GetValue("status").ToString()

    });

    }

    }

    catch (Exception e)

    {

    Console.WriteLine(string.Format("SearchResults error: {0}", e.Message));

    }

    }

    using (var connection = new SqlConnection("Server =001sqldw01 ; Database = db; Trusted_Connection = True"))

    {

    connection.Open();

    string commandtext = "INSERT INTO table ([ID],[DateTime],[Extension],[Status]) VALUES (@ID,@DateTime,@Extension,@Status)";

    SqlCommand command = new SqlCommand

    {

    Connection = connection,

    CommandText = commandtext

    };

    foreach (DndRecord record in DndRecords)

    {

    command.Parameters.Clear();

    command.Parameters.AddWithValue("@ID", record.Id);

    command.Parameters.AddWithValue("@DateTime", record.DateTime);

    command.Parameters.AddWithValue("@Extension", record.Extension);

    command.Parameters.AddWithValue("@Status", record.Status);

    command.ExecuteNonQuery();

    }

    }

    }

    /// <summary>

    /// This method is called when this script task executes in the control flow.

    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    /// To open Help, press F1.

    /// </summary>

    public void Main()

    {

    // TODO: Add your code here

    CreateProductAsync();

    Console.ReadKey();

    //object Dts = null;

    //Dts.TaskResult = (int)ScriptResults.Success;

    }

    #region ScriptResults declaration

    /// <summary>

    /// This enum provides a convenient shorthand within the scope of this class for setting the

    /// result of the script.

    ///

    /// This code was generated automatically.

    /// </summary>

    enum ScriptResults

    {

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    };

    #endregion

    }

    }

    I have tried different methods but no luck can anyone help me why this is not working

    Let me know if you have any questions.

  • If you check the Script Task's properties, what is the EntryPoint set to?


  • Phil,

    It is set to Main.

     

    Thanks

    se

  • sathwik.em91 wrote:

    Phil,

    It is set to Main.

    Thanks

    se

    OK. I think your problem may be down to the fact that your

    public void Main()

    should be contained by

    public partial class ScriptMain

    and not by the Program class.

     


Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply