Tag: SSIS

SSIS: Use Sql connection inside script component

If the requirement is to run dynamic query on each row, you might get inside Script comoponent. Here is how you can grab SQL connection and run your queries;

Your connection is defined here on script component;

Edit Script…
public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyConn;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
    }

    public override void ReleaseConnections()
    {
        //base.ReleaseConnections();
        connMgr.ReleaseConnection(sqlConn);
    }

    public override void PreExecute()
    {
        base.PreExecute();
        string KeyValue = Variables.vFileOutputString;

        String vSqlStatement = "SELECT SqlStatement FROM [DataHub].[vw_LastStepForRglData] WHERE ADNumber = '" + KeyValue + "' "; 
        SqlCommand cmd = new SqlCommand(vSqlStatement, sqlConn);
        sqlReader = cmd.ExecuteReader();
    }

    public override void PostExecute()
    {
        base.PostExecute();
        sqlReader.Close();
    }

    public override void CreateNewOutputRows()
    {
        while (sqlReader.Read())
        {
            {
                DynamicSqlBuffer.AddRow();
                DynamicSqlBuffer.SqlStatement = sqlReader.GetString(0);
            }
        }
    }
}

Hope this will help.

Execute SSIS Package from SPROC

You can run SSIS package from stored procedure. Follow this;

ALTER PROCEDURE EXECUTE_SSIS_PACKAGE_DEMO
 @OUTPUT_EXECUTION_ID bigint output
AS
BEGIN
 DECLARE @EXECUTION_ID BIGINT
 EXEC SSISDB.CATALOG.CREATE_EXECUTION 
  @FOLDER_NAME = 'MyFolderName'
 ,@PROJECT_NAME = 'MyProjectName'
 ,@PACKAGE_NAME = 'MyPackageName.DTSX'
 ,@USE32BITRUNTIME=FALSE
 ,@REFERENCE_ID=1	--This is environment ID
 ,@RUNINSCALEOUT=FALSE
 ,@EXECUTION_ID = @EXECUTION_ID OUTPUT

 EXEC SSISDB.CATALOG.START_EXECUTION @EXECUTION_ID
 SET @OUTPUT_EXECUTION_ID = @EXECUTION_ID
END

You can execute this procedure;

--EXECUTE STORED PROCEDURE
DECLARE @OUTPUT_EXECUTION_ID BIGINT
EXEC DBO.EXECUTE_SSIS_PACKAGE_DEMO @OUTPUT_EXECUTION_ID OUTPUT
PRINT @OUTPUT_EXECUTION_ID

You can check the status of execution;

--CHECK STATUS
SELECT STATUS 
FROM SSISDB.CATALOG.EXECUTIONS
WHERE EXECUTION_ID = N''

SSIS package execution requires windows authentication. My client has SQL server authentication enabled so I can not take advantage of this approach.

In my next article, I will show you how to run SSIS Packages using SQL authentication.

Resources

https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

DistributedCom error when running SSIS package on windows box

When I run integration services package from inside of the windows box, they work. When I try to run them from outside of the box using my laptop, they fail. SQL Agent job works from either side.

Upon further investigation, I noticed these events in windows system event log;

It seems that DistributedCOM component has permission issue; Add user’s to this window group;

Now we need to perform some DCOM component configuration to grant access to the Integration services service.

Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying settings in the registry.

On the location tab, make sure “Run application on this computer” is checked.

On the security tab, click Edit in the Launch and Activation Permission area. Add users and assign appropriate permissions, and then click ok.

Repeat above steps for Access permissions.

Restart database server.

CS2001 Missing AssemblyAttributes.cs when executing SSIS package deployed to the server

If you get this error;

The reson is permissions;

Navigate to c:\windows\temp. On the temp folder, right click properties, security and advanced. Add your proxy account here and give it full permissions;

Resource

https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the

enabling / disabling xp_cmdshell

I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.

Here is how you can enable, disable and use.

To check, if xp_cmdshell is enabled and available to use;

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell';

Turn on xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

Turn off xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

Here is a simple use case;

EXEC xp_cmdshell 'dir c:\'

Here is how you can enable it, use it and disable;

DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM  sys.configurations
WHERE  name = 'xp_cmdshell' ;
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END
 
EXEC xp_cmdshell 'dir c:\';
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
END

You can create a stored procedure and use;

CREATE PROCEDURE sp_SuperCmdShell
(
    @commandToRun VARCHAR(1000)
)
AS
BEGIN
    -- check to see if xp_cmdshell is enabled, if its not
    --   then enable it and run the command then set it back
    --   to not enabled.
    DECLARE @originalSetting INTEGER = 0;
    SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell' ;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
    END
 
    EXEC xp_cmdshell @commandToRun;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 0;
        RECONFIGURE;
    END
 
END
GO
exec #sp_SuperCmdShell 'dir c:\';

This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.