SSIS Script task varaible

• sathwik.em91

  SSC Eights!

  Points: 878

  More actions

  May 8, 2019 at 8:56 pm

  #3638646

  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

  Sathwik

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 8, 2019 at 9:22 pm

  #3638674

  Have you tried setting a breakpoint and then inspecting the contents of the various variables? Might provide some useful insight.

  Also, if you are looping round resp.ResponseData, what is the point of repeatedly setting the value of User::EntryData, which can contain only one value?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin