SSIS Script task varaible

• sathwik.em91

  SSC Eights!

  Points: 878

  More actions

  May 8, 2019 at 8:56 pm

  #3638647

  Hi Team,

  I have a script task variable called User::EntryData of type String and I have a string value (Dictionary ) in C# to the Entry date SSIS variable and I am trying to pass it  to a Foreach loop enumerator  using foreachvariable enumerator and loop through all the elements in the variable.

  But it says the below error:

  The type of the value (Char) being assigned to variable "User::EntryData" differs from the current variable type (String).Variables may not change type during execution. Variable types are strict, except for variables of type Object.

   

  Code{

  String Host = "http://hq-pcm-imstest:9090";

  HttpClient client = new HttpClient();

  client.BaseAddress = new Uri(Host);

  client.DefaultRequestHeaders.Add("Username", Environment.UserName);

  client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

  string Url = Host + "/api/entry?repoId=271";

  var Response = client.GetAsync(Url).Result;

  if (Response.IsSuccessStatusCode)

  {

  var content = Response.Content.ReadAsStringAsync().Result;

  JavaScriptSerializer serializer = new JavaScriptSerializer();

  FormResponse<FormEntry> resp = serializer.Deserialize<FormResponse<FormEntry>>(content);

  if(resp==null)

  {

  throw new Exception("response was null");

  }

  if (resp.ResponseData != null )

  {

  using (var connection = new SqlConnection("Server =hq-dev-sqldw01 ; Database = DW_STAGE; Trusted_Connection = True"))

  {

  connection.Open();

  //string commandtext = "CREATE TABLE #tempdata (ID,SubmitterName,TimeSubmitted,IsArchived,IsDeleted,EntryData)";

  string commandtext = "INSERT INTO TestResponse (ID,SubmitterName,TimeSubmitted,IsArchived,IsDeleted,EntryData) VALUES(@ID,@SubmitterName,@TimeSubmitted,@IsArchived,@IsDeleted,@EntryData) ";

  foreach (FormEntry entry in resp.ResponseData)

  {

  SqlCommand command = new SqlCommand();

  command.Connection = connection;

  command.CommandText = commandtext;

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

  command.Parameters.AddWithValue("@SubmitterName",entry.SubmitterName);

  command.Parameters.AddWithValue("@TimeSubmitted",entry.TimeSubmitted);

  command.Parameters.AddWithValue("@IsArchived",entry.IsArchived);

  command.Parameters.AddWithValue("@IsDeleted",entry.IsDeleted);

  command.Parameters.AddWithValue("@EntryData",serializer.Serialize(entry.EntryData));

  command.ExecuteNonQuery();

  String showdata = serializer.Serialize(entry.EntryData);

  Dts.Variables["User::EntryData"].Value = showdata.ToString();

  }

  Any feedback or suggestion is really helpful!!

  Thanks

• Thom A

  SSC Guru

  Points: 99126

  More actions

  May 8, 2019 at 8:58 pm

  #3638649

  Double post of: https://www.sqlservercentral.com/forums/topic/ssis-script-task-varaible

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk