SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job:

This is the most common issue faced when we deploy packages in 64 bit system. When we create any package with Run64bit set as false, this issue occurs.

In order to overcome the 32\64 bit environment issue, it better we use command line in our job to execute the packages.

Use the below command:

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec /DTS "\MSDB\ETLFolder\ETLExtractMaster" /SERVER DW01P /CHECKPOINTING OFF  /REPORTING V

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running:

While I was running my data warehouse job (containing many ETL Master Packges) in SQL SERVER 2008 R2, environment I was frequently facing Timed Out issue and SQL server got disconnected, but when I ran single package manually, it ran without failure.

So I performed the below tasks to monitor the performance of the Job:

1. Monitor performance in Task manager: The CPU usage was more than 90% when the job was running.

2. Tracked job even in EVENT VIEWER when the job failed, and got the below error message:

3. Executed below query in SQL Server:

SELECT [name] AS [Name]
      ,[configuration_id] AS [Number]
      ,[minimum] AS [Minimum]
      ,[maximum] AS [Maximum]
      ,[is_dynamic] AS [Dynamic]
      ,[is_advanced] AS [Advanced]
      ,[value] AS [ConfigValue]
      ,[value_in_use] AS [RunValue]
      ,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Max server memory (MB)','Min server memory (MB)')

On executing the above query, I found that the server is configured with maximum config value:

4. Executed the below query to trouble shoot the issue:


EXEC sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'min server memory', '1024'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max server memory', '6000'
RECONFIGURE WITH OVERRIDE

I set Minimum Allowed Memory to 1 GB and Maximum Allowed Memory to 6 GB and now my data warehouse job runs without any issue and consumes less CPU usage.

Batch File Script to deploy SSIS packages via DTUTIL command

The below code helps to deploy ssis packages to o MSDB folder in a server:

@Echo Off

Echo.
Echo.
Echo SSIS Package Installation Script
Echo.

if %1a == a goto Error
if %2a == a goto Error
if %3a == a goto Error

Echo.
Echo.
Echo Deployment Server: %1
Echo -----------------------------------------------------
Echo --This will delete any %3 data mart files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause
REM Goto Out


REM Remove Existing files and directory on Server
for %%f in (%2"\*.dtsx") do (
Echo Now Removing: %%~nf
dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del
)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;%3"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause

:Out
REM copy the SSIS Packages to the server
for %%f in (%2"\*.dtsx") do (
Echo Now Copying: %%~nf
dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"
)


Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]
Echo.
Echo.

Pause

:End

1. Copy the above code and crete a bat file (e.g., DeploySSIS).
2. Open Command Prompt and navigate to th ebatch file folder
3. execute the command DeploySsis.bat [SERVERNAME] [FILEPATH] [MSDB Sub-Folder]

Determine Size of SQL Table

How to determine size of SQL server table:


Use built-in code: sp_spaceused ‘Tablename’

Example: sp_SpaceUsed 'Employee'

C-Sharp Script to Derive HashValue for Multiple Columns

The below script help us to create hashvalue which can be used to compare records while inserting/updating rows in a table using SSIS packages.

In script task select the columns you want to consider for deriving hashvalue and copy-paste the below code:


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using System.Text;
using System.Security.Cryptography;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

{

private PipelineBuffer inputBuffer;

public override void ProcessInput(int InputID, PipelineBuffer Buffer)

{

inputBuffer = Buffer;

base.ProcessInput(InputID, Buffer);

}



public override void Input0_ProcessInputRow(Input0Buffer Row)

{

var counter = 0;

var values = new StringBuilder();



//loop through input columns

for (counter = 0; counter < inputBuffer.ColumnCount; counter++)

{

object value;

value = inputBuffer[counter];

//add each column value to one big string

values.Append(value);

}

//set output column as results of hash method

Row.HashColumn = CreateHash(values.ToString());



base.Input0_ProcessInputRow(Row);

}



private string CreateHash(string data)

{

//get byte array of long data string

var dataToHash = (new UnicodeEncoding()).GetBytes(data);

//create hash provider and compute hash of byte array

var sha1 = new SHA1CryptoServiceProvider();

var hashedData = sha1.ComputeHash(dataToHash);

RNGCryptoServiceProvider.Create().GetBytes(dataToHash);

//convert results to hexadecimal string (SQL friendly format)

var result = BitConverter.ToString(hashedData).Replace("-", "");

return result;

}

}

C-Sharp Script to get IncrementalDate

The below script helps to get maximum date used for incremental load for the SSIS packages:

In below example, we have "TimeStarted" as a incremental date column. You need to create 2 string variables 'IncrementalDate' and 'NewIncrementalDate' in the package.

In script task set 'TimeStarted' (incrementaldate column) as Readonly variable and copy paste the below code and modify the highlighted text (in Yellow) as per the input column name. On executing the package 'NewIncrementalDate' variable will get updated with MaxIncremantal date.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
      DateTime NewIncrementalDate;
      String strNewIncrementalDate;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        strNewIncrementalDate = Variables.IncrementalDate.ToString();
        NewIncrementalDate = DateTime.Parse(strNewIncrementalDate);
        //MessageBox.Show("Before: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
              
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */

        /*NewMaxEventDate.AddMonths(-1);*/
        //MessageBox.Show("After: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        Variables.NewIncrementalDate = NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff");

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        try
        {
            if (!Row.TimeStarted_IsNull  && Row.TimeStarted  > NewIncrementalDate)
            {
                //MessageBox.Show("CreateDate: " + Row.createdate.ToString("yyyy-MM-dd hh:mm:ss.fff") + "; NewDate: "
                 //              + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));
                NewIncrementalDate = Row.TimeStarted;
                //MessageBox.Show("NewDate: " + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));


            }

            Row.etlcreateddate = Variables.ContainerStartTime;
            Row.etllastmodifieddate = Variables.ContainerStartTime;

        }
        catch (Exception ex)
        {
            throw new Exception("Error in ExtractTestSample script.  Message: " + ex.Message);
        }
    }

}

How to view the value of SSIS variable updated from SQL task

How to view the value of SSIS variable updated from SQL?

In order to view the value passed to SSIS Variable from SQL Task add an script task after the SQL Task and include the code given below:

Consider the variable name is "User::IncrementalDate":


Public Sub Main()
        MsgBox(Dts.Variables("User::IncrementalDate").Value)
        Dts.TaskResult = ScriptResults.Success
End Sub

Note: Check User::IncrementalDate variable as ReadOnly variable i n Script task.

System DSN ODBC Connection Missing While Creating SSIS Connection Manager

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:

1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.

3. Create a new system DSN there.

4. Now try creating Connection Manager in SSIS package

Query to get list of packages in a Integration Server

Below is the SQL query to get the list of packages deployed to an Integration Server:

WITH ChildFolders

AS

(

    SELECT PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

        CAST('' AS SYSNAME) AS RootFolder,

        CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,

        0 AS Lvl

    from msdb.dbo.sysssispackagefolders PARENT

    WHERE PARENT.parentfolderid IS NULL

    UNION ALL

    SELECT CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

        CASE ChildFolders.Lvl

            WHEN 0 THEN CHILD.foldername

            ELSE ChildFolders.RootFolder

        END AS RootFolder,

        CAST(ChildFolders.FullPath + '/' + CHILD.foldername AS VARCHAR(MAX))

            AS FullPath,

        ChildFolders.Lvl + 1 AS Lvl

    FROM msdb.dbo.sysssispackagefolders CHILD

        inner join ChildFolders ON ChildFolders.folderid = CHILD.parentfolderid

)

SELECT F.RootFolder, F.FullPath, P.name AS PackageName,

    P.[description] AS PackageDescription, P.packageformat, P.packagetype,

    P.vermajor, P.verminor, P.verbuild, P.vercomments,

    CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData

FROM ChildFolders F

    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid

ORDER BY F.FullPath ASC, P.name ASC;

How to Fix Transaction Manager Connectivity Error in SSIS?

When we set Transaction Option to 'Required' in SSIS containers, often we get an error on Connection Manger

and data connection.

To work around this problem, follow these steps on the computer that Windows Server 2003 or Windows XP SP2 is installed on:

1.       Make sure that the Log On As account for the MSDTC service is the Network Service account. To do this, follow these steps:

a.       Click Start, and then click Run.

b.       In the Run dialog box, type Services.msc, and then click OK.

c.        In the Services window, locate the Distributed Transaction Coordinator service under Name in the right pane.

d.       Under the Log On As column, see whether the Log On As account is Network Service or Local System. 

If the Log On As account is Network Service, go to step 2. If the Log On As account is Local Systemaccount, continue with these steps.

e.       Click Start, and then click Run.

f.         In the Run dialog box, type cmd, and then click OK.

g.       At the command prompt, type Net stop msdtc to stop the MSDTC service.

h.       At the command prompt, type Msdtc –uninstall to remove MSDTC.

i.         At the command prompt, type regedit to open Registry Editor.

j.         In Registry Editor, locate the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

registry key.

Delete this key.

k.       Quit Registry Editor.

l.         At the command prompt, type Msdtc –install to install MSDTC.

m.     At the command prompt, type Net start msdtc to start the MSDTC service.

Note that the Log On As account for the MSDTC service is set to Network Service account.

    Enable MSDTC to allow the network transaction. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type dcomcnfg.exe, and then click OK.

b.       In the Component Services window, expand Component Services, expand Computers, and then expandMy Computer.

c.        Right-click My Computer, and then click Properties.

d.       In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.

e.       In the Security Configuration dialog box, click to select the Network DTC Access check box.

f.         To allow the distributed transaction to run on this computer from a remote computer, click to select theAllow Inbound check box.

g.       To allow the distributed transaction to run on a remote computer from this computer, click to select theAllow Outbound check box.

h.       Under the Transaction Manager Communication group, click to select the No Authentication Requiredoption. Set No Authentication Required on both the client and the remote systems.

i.         In the Security Configuration dialog box, click OK.

j.         In the My Computer Properties dialog box, click OK.

    Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:

 .         Click Start, and then click Run.

a.       In the Run dialog box, type Firewall.cpl, and then click OK

b.       In Control Panel, double-click Windows Firewall.

c.        In the Windows Firewall dialog box, click Add Program on the Exceptions tab.

d.       In the Add a Program dialog box, click the Browse button, and then locate the Msdtc.exe file. By default, the file is stored in the <Installation drive>:\Windows\System32 folder.

e.       In the Add a Program dialog box, click OK.

f.         In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list.

g.       Click Add Port on the Exceptions tab.

h.       In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

i.         In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK.

j.         In the Windows Firewall dialog box, select the name that you used for the exception in step j in thePrograms and Services list, and then click OK.

    Test pinging from the host server to the remote server, and from the remote server to the host server, using the netbios name (server name, without the domain). Microsoft Distributed Transaction Coordinator uses the netbios name, not the fully qualified domain name, to locate servers. If name resolution fails, distributed transactions will fail. If pings using the netbios name fails, refer to the following knowledge base article: