Executing dtsx on Microsoft SQL 2005 Express with schedule

Let say you need to run scheduled DTS package on Microsoft SQL 2005 Express and
as you probably know there is no SSIS and Agent Services in Microsoft SQL 2005 Express
edition but you can do this job actually very easy :

1.
   Assure that you do have the DTExec util, it should be in
   C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder ,
   if you do not have one means you should install SQL Toolkit .

2.
   Next step is to make a batch file to run the DTS package.
   Create batch file with DTExec formatted command for example

             setlocal
             Set upPath=[Some folder path where you have your package or whatever ]
             Set logFileName=DTS_Batch_Log

             cd "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

             set startDate=%date%
             set startTime=%time%

             set sdy=%startDate:~10,4%
             set sdm=%startDate:~4,2%
             set sdd=%startDate:~7,2%
             set sth=%startTime:~0,2%
             set stm=%startTime:~3,2%
             set sts=%startTime:~6,2% 0

             DTExec.exe /F "%upPath%\DTS\[MyDTSName].dtsx"
             /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
             /SET "\Package.Variables[My DTS Variable name].Value";
             "[Here is comes the My DTS Variable  value]"
              >> "%upPath%\%logFileName%_%sdy%.%sdm%.%sdd%-%sth%.%stm%.log"
             IF %ERRORLEVEL% == 0 GOTO [Some label ] ELSE GOTO End
             : Some label
                   your code comes here
             : End
                  

   The example above shows how to run DTSX Package from File and Log
   the results of execution to some log file.

3.
   Last step is schedule and Yes there is one in Windows it called " Scheduled
   Tasks" you could find it in System Tools – just create one with call of the batch
   that you created earlier to run dtsx packages.

And that's all you need for the job to be done ;)...

CAST and CONVERT (Transact-SQL) - Truncating and Rounding Results

Here's some "puzzle" for you dudes .
Before you opening SQL Management Studio and pasting
from clipboard , answer -
what is the result for lines below?


DECLARE @D INT
SET @D = 12345

SELECT CAST(@D AS VARCHAR(2))

Using CTE

This post is a part of Mohd Nizamuddin very interesting article
Sending multiple rows to the Database from an Application
Albert

Table valued function using Number List

First I will explain the pieces of code, which form the building blocks of the final table valued function.
We need to create a number list using the CTE as below
      ;WITH
            L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
            L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
            L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
            L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
            L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
            Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
      SELECT * FROM Numbers

This CTE is creating the list of numbers from 1 to POWER(POWER(POWER(POWER(2, 2), 2), 2), 2), i.e. until 65536.
Now Consider the below code snippet, where @list and @delim variables have been assigned.

 DECLARE
 @list NVARCHAR(MAX), @delim NCHAR(1)
 SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
 
                ;WITH
              L0 AS(SELECT 1 AS
c UNION ALL SELECT 1),
              L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
              L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
              L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
              L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
              Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
         SELECT
         @list List,
         SUBSTRING(@list, Number, CHARINDEX(@delim,
@list + @delim, Number) - Number) AS   Value,
         Number AS StartingFrom,
         CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition
         FROM Numbers
         WHERE Number <= CONVERT(INT, LEN(@list))
         AND SUBSTRING(@delim + @list, Number,
1) = @delim

The SUBSTRING statement, cuts characters from @list starting from character position (1, 5, 11 and 16).
SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - Number)

The number of characters to be cut is decided by CHARINDEX which will
return 4, 10, 15, 20 in each row, where it finds the delimiter character.
CHARINDEX(@delim, @list + @delim, Number) - Number
The above SELECT only works until the number of characters present in the
@list variable due to the condition
Number <= CONVERT(INT, LEN(@list))

The duplicate values are filtered out from the output list by the "WHERE" condition created
using the SUBSTRING function which will only return a value when it finds the delimiter
SUBSTRING(@delim + @list, Number, 1) = @delim

The output of the code snippet above would be:

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4
aaa,bbbbb,cccc,dddd	bbbbb	5	10
aaa,bbbbb,cccc,dddd	cccc	11	15
aaa,bbbbb,cccc,dddd	dddd	16	20

Table valued function using Numbered List: Implementation
Now combining all the above explained pieces of SQL, we create our
table valued function which will parse the string and return a table having two columns viz. ID and Data.
      CREATE FUNCTION [dbo].[TableFormDelimetedString]
      (
            @param      NVARCHAR(MAX),
            @delimeter  NCHAR(1)
      )
      RETURNS @tmp TABLE
      (
            ID      INT      
        IDENTITY    
          (1,    
          1),
            Data        Varchar(MAX)
      )
      BEGIN
 
            ;WITH
                  L0   AS(SELECT 1 AS c   UNION   ALL   SELECT   1),
                  L1   AS(SELECT 1 AS c FROM L0 AS   A,   L0   AS   B),
                  L2   AS(SELECT 1 AS c FROM L1 AS   A,   L1   AS   B),
                  L3   AS(SELECT 1 AS c FROM L2 AS   A,   L2   AS   B),
                  L4   AS(SELECT 1 AS c FROM L3 AS   A,   L3   AS   B),
                  Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
            INSERT INTO
               @tmp (Data)
            SELECT
             LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
             SUBSTRING(@param, Number,
             CHARINDEX(@delimeter, @param + @delimeter, Number) - Number)
          ))) AS Value
         FROM   Numbers
         WHERE    Number <=
           CONVERT(INT,     LEN(@param))
          AND  SUBSTRING(@delimeter+ @param, Number, 1) = @delimeter
     RETURN
    END
Table valued function using Numbered List: Usage
So if we now invoke the above function like
SELECT * FROM [TableFormDelimetedString]('Andy:Roger:Thomas:Rob:Victor',':')

We will obtain the following result set:

ID	Data
1	Andy
2	Roger
3	Thomas
4	Rob
5	Victor

Table valued function using recursive CTE

Here again I will first explain the pieces of code,which form
the building blocks of the final table valued function.
As we know, in a recursive CTE, we have one anchor part and one recursive part.
But if we create a CTE having only the anchor part it would look something like
     DECLARE
      @list NVARCHAR(MAX), @delim NCHAR(1) 
      SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim
= ','   
      ;WITH CTETable (start, stop) AS  
      (
       SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
      )
       SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
              CASE
              WHEN
stop > 0
              THEN
stop - start
              ELSE
0
              END
               )))
AS Data
         start AS StartingFrom, stop
AS   DelimiterPosition
       FROM CTETable
The output of the SQL above will be like

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4

Now by adding a recursive member to the above CTE, which iterates over the stop variable, the SQL looks like
    DECLARE
    @list NVARCHAR(MAX),
    @delim NCHAR(1)
 
    SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ',' 
    ;WITH CTETable (start, stop)  
        AS 
        (
        SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
        UNION ALL       -- added for recursive part of CTE
        SELECT start = stop + 1, stop = CHARINDEX(@delim, @list +  
        @delim, stop+ 1) FROM CTETable WHERE
stop > 0 -- added for recursive part of CTE
        )
        SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
        CASE
        WHEN stop > 0
        THEN stop - start
        ELSE 0
        END
        ))) AS Data
        start AS StartingFrom, stop AS DelimiterPosition
        FROM CTETable
        WHERE stop > 0

And gives the following result set

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4
aaa,bbbbb,cccc,dddd	bbbbb	5	10
aaa,bbbbb,cccc,dddd	cccc	11	15
aaa,bbbbb,cccc,dddd	dddd	16	20

Table valued function using recursive CTE: Implementation
Finally we create a table valued function from the above code blocks, which looks like
      CREATE FUNCTION [dbo].[TableFormDelimetedStringWithoutNumberList]
      (@list NVARCHAR(MAX),
      @delim  NCHAR(1)   = ','
      )
       RETURNS @tmp TABLE
       (
        ID  INT IDENTITY   (1, 1),
        Data Varchar(MAX)
       )
      BEGIN
       ;WITH CTETable (start, stop)
        AS
        (
         SELECT start = CONVERT(bigint, 1),
           stop = CHARINDEX(@delim, @list + @delim)
         UNION ALL   -- added for recursive part of CTE
         SELECT start = stop + 1,
         stop = CHARINDEX(@delim, @list + @delim, stop + 1) -- added for recursive part of CTE
         FROM CTETable
         WHERE  stop > 0
        )
       INSERT INTO @tmp (Data)
        SELECT LTRIM(RTRIM(SUBSTRING(@list,
             start,
            CASE
            WHEN stop > 0
            THEN
            stop - start
            ELSE
            0
            END))) AS Data
            FROM CTETable
        WHERE stop > 0
     RETURN
    END
Table valued function using recursive CTE: Usage
So if we now invoke the above function like
     SELECT * FROM[TableFormDelimetedStringWithoutNumberList]('Andy:Roger:Thomas:Rob:Victor',':')
We will obtain the following result set:

ID	Data
1	Andy
2	Roger
3	Thomas
4	Rob
5	Victor

Why I like these two implementations is because the looping has been handled
by the SQL server database engine itself, which would definitely be more efficient
than explicit SQL looping code written by a developer.