SQL Server Integration Services – Sample 1

Standard

Some sample packages:

Here we are going to discuses about some scenario and sample packages.

  1. Control Flow : Package started with ‘Data Flow Task’, if task failed mail send to the package administrator, or else completed completion will be notified to concern person via email. Success will be transferred to another task like ‘Folder creation’ and continue with file system task.
  2. Data Flow Task: Getting some information from data base (SQL Server ) using OLE DB Source and then convert the data, stored into another destination. If data conversion failed data will be stored into the flat file. Otherwise, data output transfer to another task like copy column. [All tasks are under Data Flow Task]. This package is starting with Data Flow Task [Contains 3 different tasks, 2nd screen shot explains this flow]
    1. OLE DB Source :
      • either select or create the connection manager.
      • Select data access mode
      • Depend upon the data access mode, different type of access option like table or view name, SQL Text, table or view variable, SQL Query variable or Named query box opened.
      • Select the available column from the list
      • Set the value for Error Output choose from drop down either ‘Fail component’ or ‘Ignore Failure’ or ‘Redirect row’
      1. Flat file Destination: In this package I have used the flat file destination for error out put, Red color output path bottom of the OLE DB Source indicate error out put simply drag the red arrow mark drop into the flat file destination make connection between the two tasks. That means as mentioned earlier out put of the one task (OLE DB Source) taken as input of the following task (Flat file Destination)
      • Make connection with flat file.
      • Mapping input columns with out put columns of the flat file
      1. Copy Column : This package I have used copy column to make new columns from Input column without any changes in the input column. (Just example . We can continue the package as for our need.)

      1. Send Mail Task: This package I have used send mail task for failure as well as task completion. Failure task will be notified to package administrator. Package completion will be notified to corresponding business team.
      • General information just the name ans description
      • Mail: Using this option to set SMPT Connection, From, To, Cc, Bcc and Subject ….
      1. File System Task: In this package I have used File System Task for 2 different purpose. First for folder creation another one for copy file to another location. File system task having 9 different operations

Note:

1. Scripting components- transformation – having option to use transformation and destination. Sql client object able to support to initialize (acquired connection) and open the connection in the pre-execute event and able to close the connection in the post-execute event. And file object also working across those events. But oledb failed such manner.

2.Oledb command, ole db destination – exec sp with assign parameters using column mapping.

 

 

Best Site: Microsoft Integration Services site (!!!)

Documentation: Microsoft Visual Studio 2005 Documentation.

Book: Professional SQL server 2005 integration Services

Authors: Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson ….

Available: At National Library of Singapore

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s