Some of SSIS stuff: variables and its assignations

Some of SSIS stuff: VARIABLES and its ASSIGNATIONS.

For a file connection log:

(ConnectionString)
@[User::RutaLog]+ @[System::PackageName] + ".log"

Where RutaLog stands for a string value which contains the path for you .LOG file

For a OLEDB Connection:
(InitialCatalog)
@[User::BD]

Where BD stands for string value which contains the name of your database

For a properties such as MessageSource (Send Mail Task) or whatever similar:

" El Modelo " + @[User::MODELO] + " del ejercicio " + (DT_WSTR,4)@[User::EJERCICIO] + " se ha cargado . \nSe han insertado " + (DT_WSTR, 8) @[User::REGISTROS] + " registros . \nSe puede comprobar desde la aplicación NS"

REMARK: I warn you the use for '\n' literals inside the main literal.

Raising EM through Microsof Management Console

From DOS session:

"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"

How many transactions has been commited in my database?

Just a beta code. Then, as you like you can schedule that stored procedure in a job
It uses internal esctructures such as LOP_BEGIN_XACT and are not documented in public forums.

CREATE TABLE [dbo].[TRA_TRANSACCIONESMENSUAL]
( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[OPERATION] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOTAL] [int] NULL,
[BASE_DATOS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FECHA] [datetime] NULL )
ON [PRIMARY]GO


CREATE PROCEDURE TRA_ListaTransaccionesMensuales
DECLARE @BD AS CHAR(25)
DECLARE @sql2 AS CHAR(500)

DECLARE cursorbd CURSOR FAST_FORWARD FOR
SELECT NAME FROM master.dbo.SYSDATABASES

OPEN cursorbd
FETCH NEXT FROM cursorbd INTO @BD
WHILE @@fetch_status = 0
BEGIN

SET @sql2= ('USE ' + @BD + ' INSERT INTO MASTER.DBO.TRA_TRANSACCIONESMENSUAL(OPERATION,TOTAL,FECHA) SELECT Operation, COUNT(*), GETDATE() FROM ::fn_dblog(null,null) WHERE Operation IN (''LOP_BEGIN_XACT'', ''LOP_COMMIT_XACT'') GROUP BY OPERATION UPDATE MASTER.DBO.TRA_TRANSACCIONESMENSUAL SET BASE_DATOS = ''' + UPPER(ltrim(Rtrim(@BD))) + ''' WHERE [id] = (SELECT SCOPE_IDENTITY()) OR [id] = (SELECT SCOPE_IDENTITY() -1 )')
-- print @sql2

EXEC (@sql2)
FETCH NEXT FROM cursorbd INTO @BD

END
CLOSE cursorbd
DEALLOCATE cursorbd

Querying for random results

--You can catch up rows at random by means of NEWID() function (either Sql2k or Sql25k)

SELECT TOP 1 *
FROM
ORDER BY NEWID()

--And this one is useful when you want to obtain a LONG random value (either Sql2k or Sql25k)

SELECT right(cast(rand(checksum(newid())) as decimal(15, 15)), 15)

How can I see data stored in MDF data pages ?

-- Just an example using a simple word ('pablo')

USE tempdb

CREATE TABLE p1(
nom CHAR(20)
)


INSERT INTO p1(nom) VALUES('pablo')
SELECT * FROM p1

SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('p1')

--It will do conversion for FIRST (0xnn) field to DECIMAL
DBCC TRACEON(3604)

DBCC PAGE (tempdb,1,34,1)

--disabling trace indicators DBCC TRACESTATUS (3604)
DBCC TRACEoff (3604)