Feeds:
Posts
Comments

Archive for the ‘SSIS’ Category

Sometimes we don’t need static file name reason can be many, you already have a file with the same name in your target
folder and you don’t want to override or you can’t override it or maybe you want to keep track of all historical files or may be because of some xyz reasons.

Right click on Control Flow pane, choose variables, and then add three variables FilePath, FileName and FileExtension of data type String

1. FilePath-: Assign your directory path say C:\Output

2. FileName:-Select property pane of this variable and modify below two properties

EvaluateAsExpression=TRUE

Expression = "OutputFileName"+(DT_STR,4, 1252)  YEAR( GETDATE()  )+RIGHT("0" + (DT_STR, 2, 1252)  MONTH(GETDATE()  ), 2) + RIGHT("0" + (DT_STR, 2, 1252)  DAY( GETDATE() ), 2)

 

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

3. FileExtension: Assign extension value “.txt”



4. Next, Right-click the Flat File connection and choose Properties and set below two properties

DelayValidation=TRUE 

ConnectionString =Choose “Connection String” property and assign value of Expression like below

@[User::FilePath]+@[User::FileName]+ @[User::FileExtension]

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

Read Full Post »

Checkpoints in SSIS

What and why?

This feature enables us to re-run SSIS package from last point
of failure rather than running this package from beginning

How?

Enable checkpoint property of package, this will enable to save
execution history of tasks in a checkpoint file and this enables SSIS to
identify from where it need to restart package (last point of failure)

How to configure this
property?

Go to package properties (Right click on control flow tab and
choose properties), shown below

Configure to look like below

Step1 – CheckpointFileName – Already pointed above about this
checkpoint file, this file gets created only if package fails

Step2 – CheckpointUsage

·        
Never:
Do not look for checkpoint file even if the file exists

·        
Always:
always look for checkpoint file, this will result in error.

·        
IfExists: this
is the best option , look for checkpoint file if it exists

Step3 SaveCheckpoints – True, of course, we
want create check point file. If you set False no file will be created in Step1

 

Once you have followed above steps, you must set the FailPackageOnFailure property
to true for all the containers/tasks in the package to enable them to
be identified as restart point.

Note: Just to add
checkpoint can only be applied for the control flow tasks

 

 

 

 

Read Full Post »

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 »

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 »

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 »

1. Open BIDS, go to control flow

2. Create a variable ResultSetXML of data
type string

3. Drag one “Execute SQL Task”

4. Create a connection to AdventureWorks
database

5. Edit “Execute SQL Task” and use below SQL
to get XML output

SELECT * FROM Person.Contact FOR XML AUTO,ELEMENTS,ROOT(‘Contact’)

6.Go to Result Set tab and use variable ResultSetXML to map like below

7. Now drag a Script Task component

8. Use variable ResultSetXML as ReadOnlyVariables
in Script Task component

9. Now click on design script and use below
code

—————–*————————*——————-*—————-*——————–*———-

Imports
System

 

Imports
System.Data

 

Imports
System.Math

 

Imports
Microsoft.SqlServer.Dts.Runtime

 

Imports
System.Data.SqlClient

 

Imports
System.Text

 

Imports
System.IO

 

Public
Class ScriptMain

 

   
Public Sub
Main()

 

       
Using Out As
StreamWriter = New StreamWriter("C:\Contact.xml")

 

 

           
Out.Write(Dts.Variables("User::ResultSetXML").Value.ToString())

 

 

           
Out.Close()

 

       
End Using

 

       
Dts.TaskResult = Dts.Results.Success

 

   
End Sub

 

End
Class

—————–*————————*——————-*—————-*——————–*———-

 

10. Above will create C:\Contact.xml
file containing XML output

 

 

 

Read Full Post »

 

Create
a dummy or EOF (End of File) as flag to show completion of certain process,
this is very important when you are sharing same FTP location across many Users/Clients

 

One
way is to copy from a source location and rename that file

 

But
same can achieved using below

 

1.
Drag a Execute SQL Task

 

2.
Create a variable BatchID having data type as string

 

3.
Use below SQLin Execute SQL Task

 

DECLARE @DATE DATETIME

 

SET   @DATE =  GETDATE()

 

SELECT      ‘OUTPUTFILENAME’

           
+ CONVERT(VARCHAR(4),DATEPART(YY,@DATE))

           
+ CASE

           
WHEN LEN(CONVERT(VARCHAR(2),DATEPART           
(MM,@DATE)))>1

           
THEN CONVERT(VARCHAR(2),DATEPART         
(MM,@DATE))

           
ELSE

           
‘0’+CONVERT(VARCHAR(2),DATEPART          
(MM,@DATE))

           
END

           
+ CONVERT(VARCHAR(2),DATEPART(DD,@DATE))

           
+‘_’+ CONVERT(VARCHAR(2),DATEPART        
(HH,@DATE))

           
+ CONVERT(VARCHAR(2),DATEPART       (MINUTE,@DATE))

 

 

4.
Use BatchID in Result Set tab of Execute SQL Task to hold value retuned by
above

 

 

5.Drag
a script component and choose BatchID as readonlyvaraibles in script task

 

Then
use below code inside script task

 

 

Imports System

 

Imports System.Data

 

Imports System.Math

 

Imports
Microsoft.SqlServer.Dts.Runtime

 

Imports System.Data.SqlClient

 

Imports System.Text

 

Imports System.IO

 

 

Public Class
ScriptMain

 

   
Public Sub
Main()

 

 

       
Dim BatchID As String

 

       
BatchID = CStr(Dts.Variables("User::BatchID").Value)

       
‘BatchID is ReadOnlyVariable

       
‘Assuming BatchID having value like ‘OUTPUTFILENAME20110317_1752’

 

 

       
Using Out As
StreamWriter = New StreamWriter("C:\" & BatchID & ".eof")

 

           
Out.WriteLine("End of File")

 

           
Out.Close()

 

       
End Using

 

 

       
Dts.TaskResult = Dts.Results.Success

   
End Sub

 

End Class

 

Note: On can rename “eof” in above code with any other type of file depending
upon your requirements

 

 

Read Full Post »

Older Posts »

%d bloggers like this: