SSMS : Preventing from Saving Table using designer

Sometime, we wanted to change the table design by renaming the column name, changing the data type and resizing the data type.  After changes are applied and change the save the table, you will get the dialog box saying “Saving is not permitted”. For example, I wanted to change the “Product” table in the “Adventure Works” database. I am changing the Name column from nvarchar(50) to nvarchar(100).

When you try to save you will get the following dialog box.

We have the data in the table. We don’t want to drop and recreate the table for this small change.

Configuration in SSMS:

1. Click on Tools –> Options.
2. Click on Designer. You will design the following screen.
3. Uncheck “Prevent saving changes that requires table re-creation”. Take a look at the following screen.

Now, you could save the table without any problem. If there are any dependency table, the dialog box will appear which warns about affected table, but this one wont harm anything.

Hope this helps someone.

Thanks,

Gowdhaman

Running SSIS package using Script component

We can run the SSIS package in many ways using SSIS package. If you have lot of child package to be called in your master package, We can use the “Execute Package Task”. But if you have to pass some dynamic variables to be passed from the parent package to the child package. We can use the “Script Component” to run the SSIS package. We have Dts.Variables to assign the variables dynamically. Following are Steps.

1. Create SSIS package
2. Drag “Script Component” to control flow.
3. Double click on “Script Component”. You can see “ReadOnlyVariables” and “ReadWriteVariables”. To test, we are selecting two variables.
   1. ChildPackageParam
   2. ChildPkgLocation
4. If you have any parameters to pass, then you can use PackageParam. Otherwise, we don’t need to map package parameter.

Following is the code used to run the SSIS package using the script task.

public void Main()
{
    string pkgLocation = Dts.Variables["ChildPkgLocation"].Value.ToString();

    try
    {
        Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgLocation, null);

        pkg.Variables["Varaible1"].Value = Dts.Variables["ChildPackageParam"].Value.ToString();

        pkgResults = pkg.Execute();

        bool isFieragain = false;
        
        Dts.Events.FireInformation(0, "Calling package", String.Format("Package Execution successfull and package path is:[{0}]", pkgLocation), string.Empty, 0, ref isFieragain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "Calling package", String.Format("Error in calling your master package:[{0}]", ex.Message), string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Hope this helps.

Thanks,

Gowdhaman