0

I have a SSIS Package which is pulling data from ODBC source and loading it in OLEDB destination. The destination table does not contain an Identity column. I need to get the Max(Id) from the column and increment it by 1 for the next set of records. MaxId is retrieved and stored in a variable and the increment part is handled through C# code inside Script Component. The package execution is successful from Visual Studio, however when I try to deploy and run the package from Integration Service Catalog it throws below error:

"Script Component" failed validation and returned validation status "VS_ISBROKEN". The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

The script build and rebuild is successful and the solution build was also successful.

The Visual Studio Solution was created in VS 2017, this is a new package that I have created using VS 2019. SSMS version is 2019. The TargetServerVersion is set to SQL 2017 in the solution. There are 4-5 projects inside the solution, approximately 40-45 packages and changing the TargetServerVersion is not a feasible solution.

Please find below the C# script from the script component: int i = 1;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    /*
     * Add your code here
     */
    Row.RowNumber = this.Variables.MaxUID1  + i;    
    i = i + 1;
}
jarlh
  • 42,561
  • 8
  • 45
  • 63
Abhishek Dubey
  • 1
  • 1
  • 1
    I suggest you add a try catch to your code, to have the real error, in the catch statement put the following code to extract the error message : `Dts.Events.FireError(-1, "Main()", e.Message, "", -1);` – Dordi Dec 09 '21 at 08:16
  • From Script Component we can not access DTS object – Abhishek Dubey Dec 09 '21 at 09:46
  • You can use ComponentMetaData to access FireError Method, re-reading your question the problem in validation, you're not using custom assembly in your code? if so you need to load it to the gac. – Dordi Dec 09 '21 at 10:07
  • Two questions: If you disable the Data Flow Task and redeploy, do the other Tasks in your new package work (this will identify if it's specific to the Script Component/Data Flow or is the error just the _first_ reported error). Secondly, could you explain better what `The Visual Studio Solution was created in VS 2017, this is a new package that I have created using VS 2019` means? – billinkc Dec 09 '21 at 13:39
  • @billinkc - Yes, when i disable the DFT then the package works from SSMS. Infact the entire package with the scipt ran successfully earlier, I was missing access on couple of databases and had disabled other components(Execute SQL Tasks etc) in the package. But after getting the access i enabled these components and deployed the code but it throws error from SSMS , i have tried enabling/disabling the component. Yet to figure out the issue. - The SSIS Solution was created using Visual Studio 2017. I am using Visual Studio 2019 and have created the new package using Visual Studio 2019. – Abhishek Dubey Dec 13 '21 at 07:25
  • If you change `Row.RowNumber = this.Variables.MaxUID1 + i;` to `Row.RowNumber = 11 + i;` does it work? If so, the script component is having trouble addressing the Variable. If that's the case, then declare a class member variable. Add an override to the OnPreExecute and assign the starting value to the SSIS Variable value. `myMaxId = this.Variables.MaxUID1;` – billinkc Dec 13 '21 at 14:29
  • Replaced the code with Row.RowNumber = 1510 + i , the package works from Visual Studio but throws the same error message when deployed and executed from SSMS. – Abhishek Dubey Dec 14 '21 at 12:55

0 Answers0