Ovewrite Excel file using SSIS

Create an SSIS package as shown below:

 

Step A: Add an SQL task and execute a command "Truncate Table Sales", where Sales is the name of the Excel table.

Step B: Add an SQL task and executye a command to create a table with necessary columns, see example below:

CREATE TABLE Sales(
SalesID NUMBER,
 InvoiceNo NUMBER,
SalesAmount NUMBER,
OrderQty NUMBER,
CustomerID NUMBER,

SalesDate DATE
 )

A new table will be created in sheet 'Sales', then create an DFT task to load data into excel sheet Sales.

T-SQL Query to get the SQL Agent job exectuted date time and duration

T-SQL Query to get the SQL Agent job exectuted date time and duration

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + 
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

SSIS Error: The Transaction Manager is not available

This type of error occurs when SSIS Transaction Option is set to "required". To resolve this error open a command prompt and restart the MSTDC service as shown below:

>NET START MSDTC

If you receive any messages like the Transaction service in not available, then execute below commands:

> MSDTC -install
>NET START MSDTC

Handling Null Date in SSIS Derived Column

To Replace Null Value with Current Date:

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)) ? (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)GETDATE(),1,10) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)

The below conversion replace null value with 'Null' instead of allowing column to insert default '1900-01-01....' value.

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)) ? NULL(DT_DATE) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)

SSIS Incremental Load using Package History

Incremental Load using package History

1. Create Package History table in Warehouse:

CREATE TABLE [dbo].[PackageHistory](
 [PackageHistoryId] [int] IDENTITY(1,1) NOT NULL,
 [PackageName] [varchar](50) NOT NULL,
 [RunDateTime] [smalldatetime] NOT NULL,
 [SourceDateTime] [smalldatetime] NOT NULL,
 CONSTRAINT [PackageHistory_PK] PRIMARY KEY CLUSTERED
(
 [PackageHistoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2. Create the variables as shown below:

3. Design ETL Package as shown below:

3. Config 'Load Max ETL Date' task as shown below:

 4. Load staging fact table.

5. Config 'Load Max ODS Date' task as shown below:

6. Create tasks to Load fact table. Select data whose modified/createddate > Max(RundateTime) in package history.

7. truncate Staging table.

8. Config 'Insert Package History' task as shown below:

8. When the package is run, the data whose created/modified dates are greater than Max(RundateTime) in package history is loaded in to warehouse.

Query to find the Scheduled Job status in SQL Server

The below query displays the history of Job processed in the sql server for last 5 days. it display the job status failed/passed with processed date and jobname:


SELECT    CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11))    AS RunDate
        ,job_id
        ,step_name
        ,step_id
        ,[message]
        ,CASE WHEN run_status =1 Then 'Passed'ELSE'Failed' END AS JobStatus
        ,run_duration
        ,[server]
FROM    msdb..sysjobhistory
Where Run_date >= CONVERT(char(8), (select dateadd (day,(-5), getdate())), 112)
AND step_id != 0
ORDER BY run_status,run_date

Regards
Mahadevan

ETL Package testing

Hi,
I am trying to use SSISUnit tool for testing ETL package. Can anyone post me how to use SSISUnit tool effectively in ETL package testing.

Thanks
Mahadevan