BI Paper - SSIS Extract Data Multiple Worksheets

Some time ago in 2009, I wrote several papers from a community that I created in that year. But after the community website broke, I received dozens of requests to provide the papers for those who didn’t downloaded it at that time. For that reason I have attached to this post the paper again for everyone.

Scenario
Every month a company receive customers data from an excel file to extract and load data into an enterprise 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.

I hope this paper could be useful

DOWNLOAD PAPER HERE

2012 Review

It passed 3 months from the previous post and I need to say sorry to all you that follow my blog. For all the visitors, friends and more than 500 followers I want to explain that the previous year were the most important of my life and for that reason I failed writing here more often. I started the most important project of my life with the birth of my daugter Diana, wrote an entire book with my friend Reza and moved from Lisboa to Oporto to embrace a new challenge in a new company (BI4ALL) and stay near my family

The most important project of my life and the first smile:

The most amazing city of the world: Porto

The book

... and he company I currently work and the event I organized in Porto

I promise that I'll write more often! 
I have several projects in mind to share with you all.
Pedro

BI Session – First Look to SSIS 2012

The day after...
The SQL Saturdays #115 that held in Portugal yesterday at Universidade Autónoma de Lisboa was in my perspective a fantastic conference. The organization provided by the Portuguese SQL user group SQL Port, was absolutely amazing! Everything worked perfectly and all the small issues raised, were faster resolved by volunteers that has a common passion like me: the SQL Serer. Speaking on the some track of persons that I follow for some years, like Alan Mitchel, Rui Quintino and Andro Moreira was a big honor to me… also, having the room complete on a Saturday at 9h30 AM was a big surprise. I just want to mention a fact that made me a little disappointed… some submitted sessions was not approved and I’m absolutely sure that the conference would be even better with the speakers like Rui Romano, Tiago Moreiras and Tiago Rente. But I understand that it’s impossible to have all speakers in action.

I've submitted two sessions about SSIS 2012, but just one session was approved by the organization. It were understandable but forced me to summarize the two sessions in only one. I was a little fast presenting my session, because my main goal was to provide the first impact to SSIS 2012 and cover the most new features and improvements to the attendees. I think almost attendees got the overall vision about this new version and I have no doubts that they will now go into deep detail on the new features and improvements they think more usefully.

I've included below my session slides presentation that I hope you enjoy and can share some feedback with me. As I already wrote on this blog, I’m finishing the book on the SSIS 2012 version and I hope you could help me to continue writing on the future about subjects I love!

A special thanks to the organizers, master BI students, friends, colleagues an all the attendees who chose my session.

BI Tool – SQL Server 2012 Released to Manufacturing

Today… I have one more reason to write here… Microsoft announced that SQL Server 2012 has released to manufacturing. Now it’s your turn to download and evaluate this new amazing version. Relating to SSIS 2012 I’ll write more often about it just because I passed the last 6 months investigating it through the SSIS book that I’m finishing with my friend Reza rad. For now, I want to highlight the following new updates:

Microsoft SQL Server Data Tools
Business Intelligence Development Studio known as BIDS there will not exist anymore. Now, the name is Microsoft SQL Server Data Tools. Take a look here.
Download SQL Server Data Tools

Microsoft SQL Server 2012 Data Mining Add-ins for Office 2010
I thought Data Mining strategy had died, but has not yet! Fortunately Microsoft update the old Add-In to the new SQL 2012 and Excel 2010 versions. This package includes two add-ins for Microsoft Office Excel 2010 (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2010 (Data Mining Templates)
Download Data Mining Add-In

Today, don’t forget to attend the SQL Server 2012 Virtual Launch Event, featuring keynotes by Ted Kummert and Quentin Clark and a number of video presentations about SQL Server 2012 by other SQL Server expert.

BI Book – SSIS 2012 Integration Services Book

I've never been so long without writing on the blog. The last months have been very complicated in terms of time, but I promise that I will not be so long time without writing! The main reason for such a long absence was undoubtedly the book I'm writing since September 2011! Yes…I’m writing a book and you know it now in first hand! This book is focused on the next version of SQL Integration Services (SSIS 2012 version) and is being written with my friend Reza Rad. This book is being reviewed by SSIS world reference professionals Phil Brammer and Rafael Salas that I need to thank for all the feedback they are giving to us!

The book is almost complete and has been an incredible challenge for me! It has been an authentic miracle working in a full-time job, teaching at the weekend and still has time to investigate and write a book! I want to thank all the effort that has been made also by Reza Rad (co-author), all reviewers, and especially PacktPub publisher staff. Thanks to Joana, my girlfriend, that always supported and motivated me to take this up until the end and never give up.
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Reza Rad, Pedro Perfeito

"SQL Server Integration Services (SSIS) is a leading tool in the data warehouse industry - used for performing extraction, transformation, and load operations. This book is aligned with the most common methodology associated with SSIS known as Extract Transform and Load (ETL); ETL is responsible for the extraction of data from several sources, their cleansing, customization, and loading into a central repository normally called Data Warehouse or Data Mart.

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook covers all the aspects of SSIS 2012 with lots of real-world scenarios to help readers understand usages of SSIS in every environment.

Written by two SQL Server MVPs who have in-depth knowledge of SSIS having worked with it for many years. This book starts by creating simple data transfer packages with wizards and illustrates how to create more complex data transfer packages, troubleshoot packages, make robust SSIS packages, and how to boost the performance of data consolidation with SSIS. It then covers data flow transformations and advanced transformations for data cleansing, fuzzy and term extraction in detail. The book then dives deep into making a dynamic package with the help of expressions and variables, and performance tuning and consideration"

Me and reza didn't wrote this book for money, but by the desire of writing about what we like to do. Help us continue to share knowledge with the comunity. The book is now in pre-order and will available soon in several places like Amazon, Lehmans and in the PacktPub Publisher Website

Order the Book NOW

BI Videos - SQL Server Denali Videos‏

Today I paste some very interesting SQL Server videos links for you... not only for BI professionals, but also SQL DBA's and DEV's. I hope you enjoy them!

The videos and links could be found below:

Upgrade Your Database (Denali)
Data Programmability Tools (Denali)
SQL Server appliance (Denali & R2)
Parallel Data Warehouse (R2)
SQL Server virtualization (Denali & R2)
SQL Server security (Denali)
SQL Server AlwaysOn (Denali)
SQL Server backup compression (R2)
SQL Server Integration Services (Denali)
SQL Server Non-Relational (Denali)
SQL Server CLR (Denali)
SQL Server Data Compression (R2)
SQL Server Manageability (Denali & R2)

BI StepByStep - SSIS Calling a WebService (Option 1)

Dear Friends,
There is lot of issues in the EE and MSDN communities relating to calling a webservice from SSIS. For that reason, I decided to write a new technical paper concerning to the subject.

And as I told before, technical papers and discussions, will be posted on the BI Community (http://www.biresort.net/)

And you can also directly download each paper from here:

SSIS Area

StepByStep - SSIS Moving huge amount of files

StepByStep - SSIS Using Package Configurations

StepByStep - SSIS Extract data from multiple Excel worksheets

StepByStep - SSIS Calling a WebService with Web Service Task (Added 30-11-2009)

I hope It can help you!

Regards.

SSIS - Script Component as Source From SQL

I’m not writing about technical issues for a long time ... but I’ll start to diversify more the content of this blog. Today I’m writing about script component in SSIS that people ask frequently in Microsoft forums and in http://www.experts-exchange.com/.

To access the SQL Server or other sources, there are some standard components provided with the SSIS that I always suggest for almost the cases, but for some complex issues that require some customization, this post could be useful. If you understand the script component logic, and know some .net programming, you can get data from any source.

1. Open Business Intelligence Development Studio (BIDS), and create a new package.

2. Add a dataflow to your controlflow (There is a script task in the controlflow, but I’ll write about Script Component inside dataflow)

3. Create the table Contry for this example

4.Create and configure ADO.NET connection with the name “cmSQL_ADONET”

5. Configure the script component setting the script connection to the connection created in the previous step, and add the columns you need to the SSIS pipeline

6. Open the design script to add the customized code and try to understand the logic of the component. You have five main procedures: AcquireConnections, CreateNewOutputRows and ReleaseConnections, preExecute and postExecute. In this example, I only use the first three procedures but you are free to use the procedures you want.

7. You can see the results from the pipeline grid view.

This example is not dificult, but its the first step to give you more complex packages that I'll try to show you in a future post. If you want the code of this package, let me know and I send you by email instantly.

Regars,

Pedro

BI Education - SQLBI Methodology

Marco helped me two years ago in a dimensional model design problem. He probably does not remember me, but I’m following his work, mainly all the stuff relating to dimensional modelling. Marco published last week the first draft for an alternative purpose to Inmon and Kimball approaches. I spent yesterday and today reading all the 44 pages of this first draft. And came to me all the issues and problems that I felt in the past… it’s really a very good approach not only for BI professionals using Microft tools.

I’m comment Marco Russo and Albert Ferrari draft, only because I want to contribute with my opinion for their project and advertise it. And I would be happy if professionals and friends like Subhash, Bruno, Amin, Tomislav, Alex, Ella and all other experts give their opinions about this subject.

It’s an approach near the real world and my next comments are only to clarify myselft and make this draft even better. Like they say in the draft:

“Always remember that these are not fixed and immutable rules. Your specific implementation of the architecture will probably be constrained by other considerations. You – as the BI analyst – are the one and only person that will be able to take these decisions in a correct way. “

Download the draft in the official website

http://www.sqlbi.com/sqlbimethodology.aspx

Some comments/questions

1. The main use of the configuration database is for dealing with bookkeeping information and with data that come from more than one source? An example is to deal with the same customer that comes from two different sources with a different name… Which more funcionalities this configuration database could have? I think this configuration database should be shared with several BI solutions and use SQL schemas to split them. If you have one database configuration for each BI solution you’ll have more cost and more work to maintain it.

2. I’m agree that mirror database, filters only the required data, and the staging area is a relational database that will be a bridge between mirror and datawarehouse denormalized database. If you need to process all the cubes again, datawarehouse database stores all the historical data or you need to go again to mirror database or operational sources?! (sometimes the historic data in operational sources is available only once)

3. SQL Schemas could create problems in third party products, and I always avoid them. What you think about it? In Data warehouse or Data Mart databases you change the schemas used in Staging Area, could be good for understand the solution, but I’m afraid that could be an issue in ETL and OLAP designs. What you think?

4. Your definition of Metadata OLTP DB makes me a confusion… do you have views to OLTP operational systems to work as a bridge to DW in case of not using a staging area, but normally metadata is contextualizing your data… describing what each field, each table, each database is … I think you could explain this better.

5. Following your draft, in Kimball approach, the Data Warehouse database has a denormalized structure in spite Inmon aproach that has a normalized structure. Am I correct? There is some confusion in this point…

6. I don’t think that in SCD type 2 the status column should be used. I only understand the use of this column in this type of dimensions, only for users that goes inside database and need a fast understand of the current row, because in practice, if you lookup for the status column, you may have a problem if you need to import rows from a previous date (late arriving facts)… and you will map this rows to the status and could not be true

7. My defition of ODS was a little different. Your ODS description (only used by softwares) may cause several views of “true”. What do you think?

8. I’m completed agree with you about Natural and Surrogate Key in date dimension. In my last project I used Surrogate Key as an identity integer, that make me felt lost. I always needed to join the fact table with date dimension to understand the mean of each fact.

9. In page 36 when you wrote “Mirroring is not part of the ETL. “, I think create the mirror is the extract step of an ETL (ExtractTransformLoad)… you don’t do any transformations… only the basics like change column names and extract the data, and if you can apply some filters. Changing column datatype could generate an error… and we must avoid accessing source operational systems more than once

10. In pag 42, in which database you store logs added by SSIS Log provider? (Sysdtslogs). I like the answer you gave to the question “what is the difference between log and audit?” and also the use of audit information directly in the cubes but hidden for business users. Some people are a little confused in this subject.

11. How BI analysts control the data lineage? And how we can make an impact analyst on a change in a database?

Improvements

1. I think could be interesting include some standards in database objects (tables, views and stopred procedures names). Which nomenclatures you normal use to each object in each database (Mirror, Staging, ODS and DW e DMart)?

2. More focus on business and technical metadata of each database.

3. Create an addittional shared database for SSAS cubes performance using used based optimization. (Activating query logs of olap cubes and creating aggregations based on those logs)

4. In configuration database described, could be interesting the use of data profiling

5. Include a data mining structure

The original image was mofified by me and got it from draft 1.0.

I think that some comments could be basic for some of you, but because there aren’t any standards and any ISO for this subject, BI Analyst still some has doubts about the best pratiques to follow implementing a BI solution

So, I hope to receive Marco and Albert here to clarify me the points described before. It’s a very important step that these guys are doing, because they are creating rules, standards and make we speak the same language.

Good Luck!!

Updated 29-09-2008 with Albert Ferrari response. I will reply in the comments section.

Albert and Marco response:

Pedro,

I'll try to give you some answers, even if the space here is not enough for a complete discussion:
1. Yes, the config DB can be shared between different solutions for the same customer even because, in the DWH world, "different solutions for one customer" means "One complex solution for one customer, composed of different subject areas". :)

2. In some situation we store in the OLTP Mirror several snapshots of OLTP tables, in order to be able to rebuild the history. Clearly the DWH contains all the history but, sometimes, you will need to rebuild the DWH too...

3. I would not avoid schemas in order to satisfy third party tools. If some tool need to query the DWH then it will have its views and, if it cannot use schemas, then its views will live in the dbo schema.

4. OLTP Metadata is just a set of views. If you cannot build them in the OLTP DB and you cannot afford an OLTP Mirror, then an OLTP Metadata might help.

5. The Kimball approach is de-normalized but the extent of normalization that is needed in an Inmon one is not very clear, it all depends from the analyst. Inmon leaves all these "details" to the analyst, giving just some advices.

6. I agree with you. What else could I say? :)

7. What is your definition of ODS?

8. Yep. Even if, sometimes, I had to change the key, using some very creative way of expressing a date. If you have several billions rows then the INT representation of YYYYMMDD might be a problem and a smallint solution might be helpful. Clearly 20080101 is not a smallint... so you'll need some differente ways to express the date.

9.I don't understand your note. During mirroring you should not do any kind of transformation and/or column rename. If a column is called C1P8D3B0 ans is of type INT, then you will have that column name and type in the OLTP mirror and a VIEW that will change name and type when you will extract from the mirror.

10. I think the log DB is the perfect place for thos logs.

We are about to publish a second whitepaper that might be interesting to look at, it will provide a demostration of the methodology on the very (ab)used AdventureWorks DB.
Thanks alot for your comments, I will use them and your improvement section for a future review of the whitepaper.
Alberto

SSIS - Updating rows from a FlatFile

Dear Friends,
Today I will describe a subset of an ETL I made and I think that could be useful for you and it will be useful for me if I receive your feedback.

The Problem
I have customer’s data (sent by an application by text files to a specific file system share), that must be imported to a SQL Server 2005 database, more precisely, to the customers database table.

All Fridays, all the data in the text file, must be extracted, transformed and loaded into the SQL Server 2005 database. After the extraction the file must be moved to another directory in the file system. (…\ProcessedFiles)

The text file is a fixed column type, and there is a column that describes the event to follow when ETL update the Customers table.
N (means new customer to insert)
A (means update existent customer)
D (means update/inactivate existent customer)

The Solution
The data will be extracted from the text file into a preTable (“preCustomer”) without any transformation/rules (to avoid risks). For each row imported to this preTable, I will have an extra column that is a foreign key to my audit table information. Next, I read all the records from the last extraction, transform and load into Customer table.

The end user, from an asp application, will be able to correct or ignore the redirected rows, and next time the ssis package executes, these errors will disappear. (Note: the warning and error rows will be stored in redirectCustomer table, until some action (correct or ignore) from the end user)

Logical Data Flow
I have several steps that could be useful to explain, but today I will focus only in the subset that I think is more interesting. Below you can see the logical data flow for the ssis package number 3, that transform and load the customer data from the preCustomer table into customer table.

SSIS Package
And finally the ssis package...
(Note that the schema of ETL_Customer is identically to the ETL_redirectCustomer, for this reason you can union all the two sources in the pipeline)

Operational Risk
Remember that in a business intelligence project 70% of the risks occurred in the ETL module. So pay attention to the ETL and always try to identify the existence of an operational risk. I use the ssis error logs only for the real unexpected errors, if you think that there is a probability of some task fail, don’t wait to see the error log, try to mitigate the error before it!!!

“Operational risk is defined as the risk of loss resulting from inadequate or failed internal processes, people and systems or from external events. This defi nition includes legal risk, but excludes strategic and reputational risk.”
The European Commission’s Directive 2006/48/EC9

Some tips for using the best practices

I selected some important tips that could be followed in order to implement a good ETL project, using the best pratices.

- Always comment you project

- Always create the control flow and dataflow logical model and always before implementation of ssis package.

- For all the documents created, insert each document inside a template with all the important information to share with all the teams involved in the project. It’s very important that when you look for a document, you could identify the subject and scope of the document fast. For example, for the template I created for Logical Data Flow, I added important information as project, package, dataflow, developer/team, date, version, purpose, reference…
- Use naming conventions for the tasks and components of the SSIS Packages

- Use SSIS log provider to catch error and warning events.

- Could be useful implement not only the SSIS log provider, but also some custom logs, using event handlers.

- Test you package! How?! Use SQL Data Generator to generate thousands of rows to your source data. This software is very good, for each column you can select all the possible values that you want to random. (These values could come from a database, text file…)

I hope this post could be useful for you, and I also hope that you can give some feedbak about this.

Next post I will try to write something about data mining!!!

Cheers!!

BI Tools - SQL2008 CTPFebruary Bug (SSIS)

Dear friends,
I found the gold… no...no… I found the bug! J This mistake is because I never thought that a simple person like me, here in my small country with the name of Portugal, that sometimes people think the country is a state of Spain would found a Microsoft bug in SQL 2008 (SSIS)... ok… but let’s talk about the bug. Sorry for this introduction…

As I told you in a previous post, I’m developing a SSIS project for my master BI degree, using the new SQL 2008 CTPFebruary version. I found a bug, that I think is critical and very important.
If you already worked with SSIS 2005, you are probably familiarly with the warning messages that advise you for unused columns in your pipeline:

For those people who never saw the messages, I made a very simple example. (Note: One of the best practices to develop an ETL package is to try to minimize the size of each row in your pipeline. If you forget to delete these unused columns you will be decreasing ETL performance. If you already used SSIS 2005 and never saw the messages, take care)

This simple example in previous version of SSIS (2005) only executes a query in the table “Person.Contact” on AdventureWorks and inserts the returned rows into a temporary table.

Steps:
1. Add a dataflow “DFT TestBug” to the ControlFlow
2. Goto the dataflow created in the previous step
3. Add an OLEDB Source to get some data from AdventureWorks database:
SELECT Title, FirstName, MiddleName FROM Person.Contact
4. Add an OLEDB destination to insert the data into a #tempTable. Map the source and destination columns title and firstname and intentionally leave the column MiddleName without map.

As you can see for the image below, the SSIS 2005 advice you for the unused columns with warning messages. In the new version, these messages doesn't appear!!!

I posted this bug in MSDN Forums
http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3044877&SiteID=1&mode=1
and James Thomson and Bob Bojanic kindly suggested me to submit this bug to Microsoft. I submit the bug as you can see for the link below:

Sorry MSFT team for this post, but for me this messages are very important and it will be a problem if the final version doesn’t correct this!
Cheers!!!
Pedro

BI Conference - SQLBits - Birmingham (UK) - After

Dear Friends,
I’m just arriving from Birmingham (UK) and I only have one Word to describe the experience: FANTASTIC! I have several things to write but if you weren’t there you missed a fantastic day!
As you already know, I’m currently living in Portugal but last Friday I pick my girlfriend and went to Birmingham (UK) to attend the SQLBits conference and back to the city where I’ve finished my degree some years ago. Microsoft Portugal is not investing too much in SQL BI tools, and in the last summer it had only one person specialized in these tools… To prove it, the next TechDays 2008 in Portugal there will be just a few sessions in SQL BI tools and probably too basic… The possibility to hear some “gurus” and the authors of my preferred books from Wrox and Wiley, it was an opportunity that I couldn’t missed!
I will describe in general how it was this journey to Birmingham and I will detail my preferred sessions on detail in a next post.

The Conference
There was several sessions in this conference, and I attended the sessions related with Business Intelligence MS SQL tools. Darren Green, Allan Mitchell and Chris Webb didn’t disappointed me in their sessions, and the money expended in this journey was a very good investment on my career and a good opportunity to “open my eyes” more and more to the world.

Allan Mitchell Session
Allan spoke about the new features of SSIS 2008 and compares it with the SSIS 2005. He shows some details that usually some developers don’t pay much attention, the performance… he show some details behind the scene, and in my opinion he done it good!
MultiCast
Pipeline Limiter
LookUp Component (Cache Options)
Data Profiling Task
Change Data Capture

Chris Webb Session
This session was very interesting… I expecting to see something about new features and good advantages of SSRS, and what I saw was the opposite and I’m totally in agree with him and this session prove that this type of events are not to sell products but is a place where we can receive very good and sincerely tips and tricks from developers to developers. And Microsoft was one of the sponsors…

Darren Green Session
Darren session was very cool and he spoke about a subject that I normally in SSIS avoid but after the session I will take more attention… and the subject is custom data components. I bought the WROX book that Darren wrote and refer this subject but I didn’t read those chapters… But is really interesting , not only to understand better the pipeline engine but also to develop more specific and complex transformations.

The City and BullRing
As I told you before, I finished my degree in Birmingham some years ago. There are several improvements in the city but it still beauty! A very good improvement was the BullRing Center that is a big shopping place with an area equivalent to 26 football stadiums!!!





The Hotel
Normally, hotels in England are not comfortable or modern, but this Hotel is very modern and I knew a Portuguese man that works there!



The NightLife
The night bars and discos are incredible on pressure of safety guards…

So... in resume, was a fantastic journey!! I will try to detail more about the conference in a next post!

Cheers!!

BI Conference - SQLBits - Birmingham (UK)

Dear Friends,
Today I'm very happy!! I received the confirmation for the next SQLBits conference in Birmingham(UK). And I'm happy for several reasons...

• I will return to Birmingham where I finished my IT Degree some years ago (NewMan College 2001)
• I will attend to the sessions of Chris Webb and Darren Green that are one of my main sources of inspiration and Allan Mitchell that is one of the authors of my favorite SSIS book at WROX.
• Change knowledge with the BI comunity and see old and new friends.
  
• Learn more about BI and SQL 2008 tools
• Enjoy Birmingham lifestyle again!

I'm sure you have a lot of reasons like me to the conference. Dont waste more time, there are more than 400 people confirmed!!

For registations follow this link: http://www.sqlbits.com
There will be several sessions, I selected these ones:

Slot 1:
Master Data Management (MDM)
Taken by Sutha Thiru
Slot 2:
Extending SSIS with custom Data Flow components
Taken by Darren Green
Slot 3:
Understanding LINQ in .NET Framework V3.5 and Beyond
Taken by Mike Taulty
Slot 4:
Making more of SSIS in 2008
Taken by Allan Mitchell
Slot 5:
Using Analysis Services as a data source for Reporting Services reports
Taken by Chris Webb

The agenda: www.sqlbits.com/information/MainAgenda.aspx
I will book my flight and hotel tonight!!

And for some reason you cannot go to Birmingham, prepare to go to Glasgow on 10-May-2008... I will try to be there... so, vote in the sessions you want to see!! Follow the link image!

See you around!
Regards!

SSIS - Execute a package from an ASP.NET page

Dear Friends,
Today I'm describing the way I executed a package from an ASP.NET page and passing a parameter.

The Problem
I need to enable the user of an ASP application to execute a package and passing a parameter to it when the user press a button.

Solution

The first thing you must know, is where you ssis packge will be stored. You or the DBA Team could stor the SSIS package inside database, in the filesystem or in DTS. Each one has advantages and disadvantages, and in my opinion the package inside database could be better when you must unsure more security.

So, in my SSIS package I have created a global variable "strCFG_TextPath" and I will update this value from ASP page. I have created a function fExecSSISPackage in a clean webservice to make the call.
(You can create the function in the ASP page, but in my case I made like this because I insert all the connections to the database inside a WebService)

WebService

In this example the SSIS package is stored in the filesystem, but the function is prepared to accept each of the 3 types that I described before.

You only need to write few lines: (SourceLocation is the string value I want to pass to the SSIS package)

Dim myPackage as Package
Dim integrationServices As New Application
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
myPackage.Variables("strCFG_TextPath").Value = sourceLocation
myPackage.Execute()

ASP.NET Page
To call the function in the WebService is simple... see the image:

I hope this post could be helpfull for you.
Kind Regards,
Pedro