Archive

Archive for the ‘Copy Objects’ Category

Creating Excel Files (.xls) dynamically from SSIS

April 25, 2011 1 comment

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14th Jan. To do this,

  1. go to properties window of the excel connection manager
  2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
  3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

Continue reading..

.

Advertisements

Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”

August 18, 2009 4 comments

sometimes, you may need to copy some/ all objects from one database to another.  well this can  easily done in SSIS using the task “Transfer SQL Server Objects Task“. The usage of this task is very simple. Drag the task from toolbar for control flow. Double click on the task to start using it.

task

The editor for the task opens. First thing that you need to do is to create the SMO connection managers for both source and destination SQL servers. This can be done by clicking on the drop down provided for source and destination server tabs as shown below.

filetask

Then select the source and destination database. You can then select the objects that need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy all the tables and two views.  To achive this, set the property “copy all table = true”.

task3

You  also need to select few views hence, click on “collectionlist/browse button” in  “viewlist”. A pop up appears with the list of views, check the views that need to be copied.

task4

click ok and then execute the task to copy all selected objects to destination database. You can also copy the data by setting the property “Copy Datato true. To copy all objects in the database, you need not set each property, Instead you can set “CopyAllObjects” to true.