SSIS Script task varaible

  • sathwik.em91

    Right there with Babe

    Points: 773

    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: 243770

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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