BI Blog : StepByStep

StepByStep - SSIS Calling a WebService with Web Service Task

PedroCGD — Mon, 30 Nov 2009 19:09:00 GMT

Dear friends,

I'm back again and with a new StepByStep paper. This paper focus on a way to call a Web Service from a SSIS package.

There is lot of issues relating to this subject and I decided to publish about it... I hope this can help you.

Please let me know, if you check some errors/bugs in the attached material.

Read the entire StepByStep solution in the attached document below.

StepByStep - SSIS Extract data from multiple Excel worksheets

PedroCGD — Thu, 06 Aug 2009 11:22:00 GMT

Scenario

Every month a company receive customers data from an excel file to extract and load data into company data warehouse. In brief, is required to extract data from a single excel file with multiple worksheets with same schema into a single destination table in SQL server 2005. Example of an excel file with three worksheets

Purposed solution

The purposed solution is creating a SQL Server Integration Services (SSIS) package that in two steps:

1. Get a list of all the worksheet names inside excel file (assuming that is unknown before running the package)

2. Use the foreach loop task to pick each worksheet name that will be dynamically read by excel source component and loaded into destination component (Ex: SQL Server, Oracle…)

Read the entire StepByStep solution in the attached document below.

StepByStep - SSIS Moving huge amount of files

PedroCGD — Thu, 14 May 2009 16:09:00 GMT

Scenario

You have a huge amount of files in a file system folder and you need to move the new or modified files to another location. This example could be applied to several other scenarios, like reading data for some specific file (depending on file attributes) and in spite of using File System Task, you use a Data Flow task.

The source and destination folders are set dynamically, through SSIS variables. It’s also assumed that new files are those between current date and current date less n days (n days is a variable that could be 2 or more days)

Purposed solution

One possible solution is to use the for each loop task to pick each file in a specific filesystem folder and check the respective attributes of each within a script task.

Read the entire StepByStep example in the attached document below.

Reference: SSIS_EX_02_FELC

StepByStep - SSIS Using Package Configurations

PedroCGD — Wed, 13 May 2009 16:11:00 GMT

Dear friends,
Today I’ll add to the community a new type of post, a StepByStep post, based on the support that I usually provide to users from MSDN Forums and Experts-Exchange… some real examples and real scenarios from real world. I'm trying to purpose the best solution but could have several other solutions based on your feedback and suggestions… this is the goal of this community... share knowledge…

Scenario

After being created several packages for an ETL project, it became necessary to allow them to be copied between multiple environments (development, production and quality) with the greatest possible autonomy.

Purposed solution

One possible solution is to use the XML functionality available in SSIS package configuration, which allows the configuration (server name / Database name) of the main database be stored in a XML file. You need to modify this file only once in each environment and you should store in the file system of each environment
(Ex: c:\MyProjectName.dtsconfig)

Read the entire StepByStep example in the attached document below.

Reference: SSIS_EX_01_PCKCFG