Feeds:
Posts
Comments

Archive for April, 2011

Script all jobs

SELECT
* FROM  msdb.dbo.SysJobs

 

 

Script all job Steps

SELECT
* FROM msdb.dbo.SysJobSteps

 

To get all steps of one Job ,say ‘Job 1’

SELECT
SJ.name AS
JobName,JB.Step_Name,JB.Step_id FROM

msdb.dbo.SysJobSteps JB

INNER
JOIN msdb.dbo.SysJobs SJ

ON
JB.job_id=SJ.job_id

WHERE
SJ.name=‘Job1’

 

Output

JobName  

Step_Name 

Step_id

Job1

Step 1

1

Job1

Step 2

2

Job1

Step 3

3

 

As shown above,’Job1′ has three steps.

 

Execute this job

EXEC msdb..sp_start_job @job_name = ‘Job1’

 

To Execute this job to start from certain step

EXEC msdb..sp_start_job @job_name = ‘Job1’, @step_name=‘Step 2’

Above can be used in “Execute SQL Task” to call job/Job Step

 

Read Full Post »

To find a column name in entire database  and then generate SELECT
script on all those tables

 

SELECT

‘SELECT * FROM’+‘ ‘+NAME+‘ ‘

FROM
sys.tables

WHERE
object_id in

(

SELECT
object_id FROM
sys.columns

WHERE
NAME=‘ColumnName’

)

Read Full Post »

 

Determine Table Size 

For one table:

USE DatabaseName

GO

EXEC sp_spaceused  TableName

 

For All tables:

Below SQL will generate script for all tables

 

USE DatabaseName

GO

 

SELECT  ‘EXEC’+‘ ‘+ ‘sp_spaceused’+‘ ‘+NAME FROM SYS.TABLES

Run the genreated script above to get size of all tables

Read Full Post »

If your target is Excel and you
want to refresh/load Excel with new set of data without creating a new file and
you want your destination sheet name fixed too, yes it’s possible!

How? Create a new excel table
(Sheet) instead of creating a new file each time.

Open BIDS

Create a new package, go to
Control Flow tab

Drag two “Execute SQL Task”
task and one “Data Flow Task”

Give name “Drop Excel Table”
and “Create Excel Table” to two “Execute SQL Task” above

Open/Double click “Data Flow
Task”

Drag an “OLE DB Source” Source

Note: Since already have AdventureWorksDW sample database, I will
use this for my example

Create a connection
AdventureWorksDW to database table DimPromotion or use below SQL

SELECT [PromotionKey]

      ,[PromotionAlternateKey]

      ,[EnglishPromotionName]

      ,[SpanishPromotionName]

      ,[FrenchPromotionName]

      ,[DiscountPct]

      ,[EnglishPromotionType]

      ,[SpanishPromotionType]

      ,[FrenchPromotionType]

      ,[EnglishPromotionCategory]

      ,[SpanishPromotionCategory]

      ,[FrenchPromotionCategory]

      ,[StartDate]

      ,[EndDate]

      ,[MinQty]

      ,[MaxQty]

  FROM
[AdventureWorksDW].[dbo].[DimPromotion]

 

Create a new connection to a Excel file in C:\DimPromotion.xls

Now drag “Excel Destination”
and configure this to use connection created above and then create a new excel table as shown below

Copy above generated create script to notepad as we need this later!

Next step is to configure
“Create Excel Table”, choose ConnectionType as EXCEL and point this to
connection create above, use below for SQLStatement

CREATE TABLE
`Excel Destination` (

    `PromotionKey` INTEGER,

    `PromotionAlternateKey` INTEGER,

    `EnglishPromotionName` NVARCHAR(255),

    `SpanishPromotionName` NVARCHAR(255),

    `FrenchPromotionName` NVARCHAR(255),

    `DiscountPct` DOUBLE
PRECISION,

    `EnglishPromotionType` NVARCHAR(50),

    `SpanishPromotionType` NVARCHAR(50),

    `FrenchPromotionType` NVARCHAR(50),

    `EnglishPromotionCategory` NVARCHAR(50),

    `SpanishPromotionCategory` NVARCHAR(50),

    `FrenchPromotionCategory` NVARCHAR(50),

    `StartDate` DATETIME,

    `EndDate` DATETIME,

    `MinQty` INTEGER,

    `MaxQty` INTEGER

)

 

Now configure ““Drop Excel
Table”, choose ConnectionType as EXCEL and point this to connection create
above, use below for SQLStatement

DROP TABLE `Excel Destination`

Finally package looks like
below

 

 

 

Read Full Post »

Generate SELECT script
for all tables in a database

 

USE DBName

GO

 

 Get all information about tables in
database

 

SELECT * FROM sys.tables

 

Get only names of tables
in entire database

 

SELECT NAME FROM sys.tables

 

 Now use above to generate select
script for all tables

 

SELECT ‘SELECT *
FROM’
+‘ ‘+‘[‘+NAME +‘]’+‘ ‘+‘GO’ FROM sys.tables

 

 

 

Read Full Post »

Go to event handler, choose OnError

Drag a Send Mail Task

Configure SmtpConnection and other details except MessageSource

Go to Expressions tab and then expand this, then click on browse
button this will open Expressions designer

Choose MessageSource under Property window and then again
click on browse button this will open the second editor, the Expression
Builder.

Then substitute values in Expression window like below

"XYZ Job
Failed"+"!"+"\n"+"Error Details:"
+"\n"+"On Server – "+ @[System::MachineName]
+"\n\n"+"Package Name – "+
@[System::PackageName]+".dtsx" +"\n\n"+"Task Name(
Task where job failed ) – "+ @[System::SourceName]
+"\n\n"+"Task Type(SSIS Component) – "+
@[System::SourceDescription]+"\n\n"+ "Error Description –
"+@[System::ErrorDescription]+"\n\n"+ "Error Code – "+
(DT_WSTR, 12) @[System::ErrorCode]+"\n\n"

image

 

Read Full Post »

%d bloggers like this: