March 14, 2022 at 5:43 pm
Hey everyone,
The issue I'm having is the strangest thing so I figured I post it here to get more eyes on it and to see if there is anything I'm missing. So, I have a SSIS package (SSIS 2019) where I have a script task that is getting some data from a web API and loading a data table (System.Data.Datatable) with it. The last step saves the data table to an SSIS object variable. Last step of code below:
Dts.Variables["User::objSalesExport"].Value = ToDataTable(SalesExport);
Method:
private static DataTable ToDataTable<T>(List<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values = Props.GetValue(item, null);
}
dataTable.Rows.Add(values);
}
//put a breakpoint here and check datatable
return dataTable;
}
This works as expected and saves it to the variable.
In a script component I'm going to use that data table to do some stuff prior to output so I start by casting the object to a data table in my script component:
public override void CreateNewOutputRows()
{
DataTable dt = (DataTable)Variables.objSalesExport;
}
Here's where it gets weird. This works great from Visual Studio both on my dev machine and the server (both has VS 2019). I step into the script component in both places and can see row count and such. Everything seems good and I should be able to proceed with utilizing the data table in my script component. I deployed it to the catalog right click the package and execute just to make sure I get the same result as when I test it from VS. I don't. I get an invalid casting error:
Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataTable'.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)
I've done this same process in tons of dev projects in the past with other employers and have never had an issue where I've had a error like this not occur in VS but occur when deployed. Only anomally I can think of is this is the 1st time I'm done this in SSIS 2019. Wanted to throw that out there just to give all the information about this. To add I've tried different casting methods, i've initialized the table first ie:
public override void CreateNewOutputRows()
{
DataTable dt = new DataTable();
dt = (DataTable)Variables.objSalesExport;
}
and even tried it in VB just to rule that out and I've hit the same error. Works in VS, but not on the server once deployed.
Am I missing something here?
Thanks!
James
March 14, 2022 at 5:51 pm
I have seen a problem in the past which may be related. In my case, the version of .NET being used by SSDT appeared to be a later version than that used by the SQL instance. To make things work after deployment, I had to work out which part of my code relied on very new features and change the implementation to use older tech.
I did not spend any time working out
a) Which version of .NET was being used, or
b) Whether it was possible to change this
– both of which are interesting questions.
I've just remembered what it was that I tried to use that would not work after deployment ... String Interpolation.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2022 at 6:29 pm
Hi Phil,
Thanks for your response and that does make sense. My only question though is what version of the .net framework wouldn't allow something as simple as a type cast?
DataTable dt = (DataTable)Variables.objSalesExport;
Even if SQL Server is outdated from what SSDT has it seems like even the oldest versions of .net should be able to perform the above type cast (and were using SQL 2019 which I'd imagine already comes with a newer .net framework installed).
Lastly is getting SQL Server using the same of newer .net framework as SSDT just downloading and installing a newer service pack or patch from Microsoft? Or is it installing something else to get it?
James
March 14, 2022 at 7:27 pm
You're right, it's probably not the same issue. But it was the only thing I could think of which was at all similar.
Lastly is getting SQL Server using the same of newer .net framework as SSDT just downloading and installing a newer service pack or patch from Microsoft? Or is it installing something else to get it?
Great questions. I don't know the answer. Anyone else??
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply