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 »

 

 

Open BIDS


Create new integration Service Project, create a new
package say “ImportMultipleExcelFiles.dtsx”

 

1. Create a Folder ExcelFiles (C:\ExcelFiles)and then create multiple
Excel files in folder C:\ExcelFiles but make sure all Excel files have identical schema (format) 

 

Assuming all Excel files above contain data in worksheet “Sheet1
with below schema

 

City

Value

AY

2000

BY

3000

CY

4000

 

You can have your own sheet name but keep a note, worksheet
name has to be uniform across all Excel files that contains data to be uploaded.

 

2. Right Click on Control Flow
Window, Select Variables

Add a variable “FileName
at Package Level having data type string

 

 

3. Go to Control Flow add a “For Each Loop Container” Component.

 

Right-click on the Foreach Loop container and select Edit.
Then, Click on Collection “Collection” tab. Assign folder path and file type as
shown below

 

 

Then go to “Variable
Mappings
“ tab and map variable created above like
below

 

 

 

6. Drag a “Data Flow Task “inside
“Foreach Loop Conatainer”, double clickson Data flow task

Drag one “Excel Source” task ,
double click on this to get “Excel Source Editor “ Window

Now choose new and new window will
open , now browse to your first excel file that is C:\ExcelFiles\First.xls

UnderName of the Excel Sheet” on “Excel
Source Editor “, choose Sheet1$

 

7. Now drag “OLE DB Destination”
task  , connect “Excel Source” to “OLE DB Destination”

Point this conenction to your database and create new table or use an existing table

Map both like below

 

8. Go to the Properties
of “Excel Connection Manager”

Expand “Expressions”

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

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”



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

10.Set property DelayValidation=TRUE on the Data Flow task

10.Execute your Package

 

Read Full Post »


1.  Create a new variable FullPath of
string data type to hold file name.

 

2. Drag a new “Foreach File Enumerator“task
in BIDS.

 

3. Click on “Foreach File Enumerator
and go to Collection tab and browse your source folder and leave *.* for
files

 

4. Go to Variable Mappings tab now and map
variable FullPath like below

 

5. Drag one “Execute SQL Task” and “File
System task
” inside “ForeachFile Enumerator“container.


6. First execute SQL task is just dummy so that we can apply Precedence
constraint


7. After connecting both execute SQL task, apply Precedence constraint like
below

 

UPPER(SUBSTRING(@[FullPath],FINDSTRING(@[FullPath],”.”,1)+1,LEN(@[FullPath])))==”CSV”
||UPPER(SUBSTRING(@[FullPath],FINDSTRING(@[FullPath],”.”,1)+1,LEN(@[FullPath])))==”DOCX”

 

8. I have used above to identify all files in C:\Test
Folder with “csv” and “docx” extension and move to a different folder.

 

Above can be modified to include more or
different file types

Read Full Post »

This is in continuation of my last post https://dwhanalytics.wordpress.com/2010/06/14/zip-file-using-ssis/

 

1. Create a variable FullPathtoZip of string data
type to hold full path to zip file
created in last post that is C:\DestinationFolder\NameofZipFile.zip

 

Create another variable TargetFolder to hold value C:\TargetFolder,
where files are to be extracted

 

2. Repeat Step3 and Step 4 as per my last post

 

3. Step 5 will be
almost identical but slight change in argument

 

"e"
+" "+ @[User::FullPathtoZip]+"  "+"-o"+
@[User::TargetFolder]
 

 

Note:-o before folder path

 

4. Run your package

 

5. If the
file is password protected then argument will be modified like below

 

"e"
+" "+ @[User::FullPathtoZip]+"  "+"-o"+
@[User::TargetFolder]"+"-p"+"PASSWORD"

 

6. What if
during extraction same file name already exist in output folder?

What you
want to do! Use switches mentioned as per below

Switch:
-aoa
Overwrite all destination files.

Switch:
-aos
Skip over existing files without overwriting.
Use this for files where the earliest version is most important.

Switch:
-aou
Avoid name collisions.

Then
Argument will be modified like below

"e"
+" "+ @[User::FullPathtoZip]+"  "+"-o"+ @[User::TargetFolder]+"
"+"-p"+"PASSWORD" +""+"-aoa"

 

 

Read Full Post »

Older Posts »

%d bloggers like this: