1

My script task is generating a namespace identifier that I'd like to change: ST_02bde10d5e6a49418f4de5fd87c4a777. It's simply not saving my changes. When I go to a file and then change its namespace identifier, as soon as I execute my task, it resets to the original, but the execution is successful.

Renaming directly in the files is useless:

enter image description here

The edition is not available in the "Properties" of the Script Task:

enter image description here

I am using .NET Framework 4 because of limitations in my environment.

  • 1
    Why do you want to change the script namespace? – Nick.Mc Apr 15 '20 at 00:23
  • Renaming a namespace wouldn't make the code more readable or meaningful? Or isn't it necessary? I am just trying to apply some good practices. –  Apr 15 '20 at 00:26
  • 1
    In my opinion it isn't necessary. SSIS Script code is never called from other C# assemblies. In my opinion, SSIS script tasks should be avoided if possible. One reason is that they require special windows rights to run. Another reason is that they can be misused for data operations. – Nick.Mc Apr 15 '20 at 00:32
  • In my case, I'd gladly avoid but I don't know how to solve [this issue](https://stackoverflow.com/questions/61150117/best-strategy-to-automating-complex-calculation-tasks-on-sql-server-2012-from) purely with database techniques. Also, thank you very much for your insightful replies. –  Apr 15 '20 at 00:38
  • 1
    Wow you have a lot on your plate. Script namespace is quite inconsequential. I will comment on your other posts – Nick.Mc Apr 15 '20 at 00:58
  • 1
    I can't work out what "this issue" is from your other post. If you need to automate / do nasty stuff in Excel, I suggest using Powershell + PSExcel module. I suggest writing standalone PS script that does what needs to be done and pushes that into SQL Server, then SQL can pick up that data and work on it. But if there is no proprietary library that you need to use in Excel, then avoid it completely if possible. – Nick.Mc Apr 15 '20 at 01:04
  • 1
    If you take a Biml approach to developing your packages, then yeah, you can specify the namespace but I don't see how that's going to help. – billinkc Apr 15 '20 at 01:07
  • 1
    @Nick.McDermaid what do you mean by "special windows rights"? Don't think I've ever needed to do anything special there – Martin Smith Apr 15 '20 at 01:12
  • 1
    Last time I deployed a SSIS package with a script task I had to grant the SQL Server Agent account administrative rights to something like `C:\Windows\System32\Temp` – Nick.Mc Apr 15 '20 at 01:19
  • 1
    presumably something specific in the script using that path then. When I've provisioned new logins to use as proxies for SSIS packages to run under I've never had to do that and they run script tasks fine – Martin Smith Apr 15 '20 at 01:22
  • 1
    No it wasn't something in the script using that path. It was a script that did some file renaming on D Drive. It required that access to generate the assembly on the fly. I'l try and find a reference to it – Nick.Mc Apr 15 '20 at 01:23
  • 1
    OK it was a long time ago but this was the error I was getting. https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the Packages were deployed by prod DBA's from the ispac file. The DBA's did not want to grant admin to the folder (completely reasonable) so I removed the script and did what I needed with the file operation task. That was the last time I used the script task and I actively discourage it now for this and a few other reasons. – Nick.Mc Apr 15 '20 at 01:37
  • Thank you, guys! I adited the other post, and added more info. Well, I think I'm gonna follow your advices, break the problem and use tally tables. Let's see where will this lead me. –  Apr 15 '20 at 01:40
  • 1
    @Nick.McDermaid I haven't come across that and don't know why it would be trying to compile from source as the binary is stored base 64 encoded in the dtsx package itself. Maybe something that happens if there is a version mismatch and it tries to automatically upgrade the script from [this answer](https://stackoverflow.com/a/41674076/73226) – Martin Smith Apr 15 '20 at 01:58
  • 1
    Yeah I figured it was a issue arising from a very narrow set of circumstances. I believe we did have some version mismatches going on. At the time I couldn't troubleshoot, it being a remote production system so I just removed it. It's just another deployment risk I never wanted to run into again – Nick.Mc Apr 15 '20 at 02:11

1 Answers1

0

My answer to the question is YES!

You CAN CHANGE the written word of the namespace in a C# Script Task in SSDT at Design Time on VSTA, no problem. The C# Project Name, however, and the related Assembly properties at Design Time cannot be changed, because the project name is read-only due to the fact that metadata about the TYPE / Class ID is stored in the Registry at Design Time in order to retrieve it's content accordingly (to the design of VSTA). In fact, if you search the registry after saving out of VSTA, you will find the Class ID and Object ID matching that weird Project Name and the matched Namespace. Once you deploy, all of the artifacts required are encapsulated into the release structures, but the developer can still go back to VSTA with no changes to the stuff that he / she expected to be retained at DESIGN TIME.

Here is my Script Task with a changed Namespace:

using System;  
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Threading.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
namespace GeneratedCodeNamespace
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] //Attribute
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
        SqlConnection myADONETConnection = (SqlConnection)rawConnection;
        
        //Use the connection in some code here, then release the connection
        Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
        object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
        string filePath = (string)rawConnection;
        Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
    }

}
CubeSpark
  • 13
  • 4