How to best download multiple Excel Files and insert data into SQL Server database tables

it depends... if each import has a specifi and diffrent way to work, then I suggest using a differnt package for each import, because it'll be better in the future to mantain all the process, if not do all the work in one package.
Regards,
Pedro

Can you please first help me with the first item, i.e. Everytime I run the package, I want the table to be truncated and the refreshed data is inserted into the empty table
Please see the screen shot below on how the package looks like, the control flow has two tasks, the script task and the Data Flow task. The script task downloads the Excel file to a specified path on the server hard drive while the Data Flow task imports the Excel source file to the SQL Server database. Should I add another data flow task before the script task to truncate the database and how would I have it configured to specify the truncate table command?
SSIS-Control-and-Data-Flow.JPG

Is there any help available?

Can you please help?

To truncate the table:
- place an Execute SQL task before Data Flow Task
- in the Execute SQL task properties editor set:
     - Connection Type: OLE DB
     - Connection: choose the same Connection Manager you use for Destination in Data Flow
     - SQL Source Type: Direct Input
     - SQL Statement: truncate table your_table_name

If your Data Flow serves well all the Excel files - use Loop Container. Use a SSIS variable to pass the Excel filename to a script as a parameter for Httpconnection and the same value to the Excel Source (using expressions) in your Data Flow.

best regards
Grzegorz

Hi,
for first Item:
add a Execute SQL Task before Data Flow task, and truncate table in this task.

if you tried and your package hanged in data flow task after truncate, then i suggest to use delete all records in table with delete command not truncate. because truncate table has few problems in this way.

grzegorzs:
Your comment: "If your Data Flow serves well all the Excel files - use Loop Container. Use a SSIS variable to pass the Excel filename to a script as a parameter for Httpconnection and the same value to the Excel Source (using expressions) in your Data Flow."

Can I have a single httpconnection handle multiple urls? if yes how can I implment the multiple destination file  names in the vb script within the Script Task , can you or someone help me walk through?

Since I did not get the response in this thread, I opened another thread and find the answer to the first issue about adding an Execute SQL Task and configure it with the truncate table query. I am truncating all tables in this SQL task and until now the packege is not hanging in data flow task after the truncate. So I am fine here, plus the delete command consumes some of the transactional logging resources I do not unnecassary want to have.

Can you please see the second issue and how to best implement this, whether create separate http connections as well as script tasks and data flow tasks for each file to be downloaded within the same package or as grzegorzs suggested: "If your Data Flow serves well all the Excel files - use Loop Container. Use a SSIS variable to pass the Excel filename to a script as a parameter for Httpconnection and the same value to the Excel Source (using expressions) in your Data Flow.". Please shed light on this!

OK,

about your question:
you can use single http connection manager with dynamic url, just you need to use expressions in properties of http connection manager, in expressions you can assign your variable to ServerUrl, Domain, ServerUsername , ConnectionString and anything else related to http connection.

and in your script task, the only thing you need is to pass your variable to script task in Script Task Editor, Script Tab, ReadOnlyVariables section

also you can use expressions in excel source in dataflow to change your excel file source

I am sorry to ask you this, but can you please walk me through on how use expressions in properties of http connection manager. Lets say I have two urls, url1 and url2, how can I do this?

assume your credential properties is same in two urls, and the only change is url,
first create two package variables with names url1 and url2 of type string
then single click on http connection manager you created in connection manager section. and go to properties tab
click on ... front of expressions property. then in Property Expressions Editor select property as ServerUrl and expression as @[User::url1] for example.
after running the package http connection manager will connect to url1 address .

the above scenario was about how to make your http connection dynamic.
if you want to loop through the urls and change http connection based on this, you can do this:
create a fully functional SSIS package with one http connection manager , and one script task and  one data flow task , as your package contain them now, but make them dynamic but using expressions and variables. and then execute this package everytime with new values for example url1 , url2 ,....

does it make sense?

I am looking for the second loop through option, dynamic by using expressions and variables. Can you walk through this?

you can do loop in two ways:
1- create ssis package as i said in previous post , deploy  it, and execute it from an application which loops through urls and pass the values to ssis package variables.
2- craete ssis package with loop containers, deploy it , and just execute it from everywhere.

the second way is better, but it needs to find urls from somewhere,
let me explain it with a sample:
you stored your urls in a sql server table.
1-create a package scope variable of type Object name it for example UrlLoopVar
2-create a package scope variable of type string name it for example UrlString
3-create a data flow task , with oledb source , and recordset destination. in recordset destination choose VariableName as UrlLoopVar, this will fetch urls from sql server table and save results into UrlLoopVar object,
4-create a Foreach Loop container, in collection tab, choose Foreach ADO Enumerator, and in ADO object source variable select UrlLoopVar,
5- in Variable mapping tab of foreach loop container editor, select UrlString variable and set index as 0 ( assume first column in your sql server table contains urls data)
6- inside foreach loop container put your script task, the script task everytime will run with new url connection made by http connection manager. remember you must set expressions property ServerURL to UrlString variable in http connection manager.


Let me know if you have problem in above steps

i FINISHED BTHE FIRST THREE STEPS, HOW TO create a Foreach Loop container?

Please Ignore the last post. I have completed the first five steps. Can you please elaborate step 6:

"6- inside foreach loop container put your script task, the script task everytime will run with new url connection made by http connection manager. remember you must set expressions property ServerURL to UrlString variable in http connection manager."

Do I have to first create an http connection manager and would I somehow reference the first variable string UrlString we have defined as package level? please walk me through this.

OK,
the only change in http connection manager is:
set expressions, ServerURL with @[User::UrlString]

you don't need to change http connection manager within foreach loop , because it will change whenever variable value changed, you just need to put script task inside foreach loop container.

OK I have done it and have added the script task as suggested. How can I change the folling line from the script in the script task (see the code section):

' Save the file from the connection manager to the local path specified  
        Dim filename As String = "C:\Matrix Data Feed\1.xls"

Do I have to somehow replace the hard coded path with the package lavel variable UrlString I have defined?

' Microsoft SQL Server Integration Services Script Task 
' Write scripts using Microsoft Visual Basic 
' The ScriptMain class is the entry point of the Script Task. 
Imports System 
Imports System.IO 
Imports System.Text 
Imports System.Windows.Forms 
Imports Microsoft.SqlServer.Dts.Runtime 
Public Class ScriptMain 
    ' The execution engine calls this method when the task executes.  
    ' To access the object model, use the Dts object. Connections, variables, events,  
    ' and logging features are available as static members of the Dts class.  
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
    '  
    ' To open Code and Text Editor Help, press F1.  
    ' To open Object Browser, press Ctrl+Alt+J.  
    Public Sub Main() 
        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"  
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing) 
        ' Create a new HTTP client connection  
        Dim connection As New HttpClientConnection(nativeObject) 
 
        ' Download the file #1  
        ' Save the file from the connection manager to the local path specified  
        Dim filename As String = "C:\Matrix Data Feed\1.xls" 
 
        connection.DownloadFile(filename, True) 
         
         
        Dts.TaskResult = Dts.Results.Success 
    End Sub 
 
 
End Class

Select allOpen in new window

You can use a variable instead of direct address of file,
for example
create a variable in package scope as string type name it FilePath and set value as C:\Matrix Data Feed

and double click on script task, in script tab, in ReadOnlyVariables add the variables: FilePath and UrlString
and in your script change this line:

Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + Dts.Variables("User::UrlString").Value.ToString()

Select allOpen in new window

sorry , try this code, i missed .xls extension.

Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + Dts.Variables("User::UrlString").Value.ToString() + ".xls"

Select allOpen in new window

the above steps will force script task to generate .xls file name based on url string, but your url string must be less than 255 character i think, because of valid file name in operation system .

now in your dataflow, you must use same work in excel source expressions

Question,

How and what is going to be stored in the UrlString variable?

In my data flow, how would I fill multiple tables from the multiple downloaded Excel files?

>>How and what is going to be stored in the UrlString variable?
as I said in post 26567890 step 3, your url values will fetch from a table in sql server database and filled in ado.net recordset, in foreach loop you will loop in records in this recordset and UrlString will filled by first cell value in the record(assume first cell value is url).

>>In my data flow, how would I fill multiple tables from the multiple downloaded Excel files?
select excel connection manager , in expressions, set ExcelFilePath with this expression:
@[User::FilePath]+"\\"+ @[User::UrlString]
look at attachment 1

and select oledb destination , double click, set Data Access Mode: Table Name or view name variable,
and set Variable name as User::UrlString
look at attachment 2

IMPORTANT NOTE: I assumed that you created tables with the same names as your urls before data flow, I mean I assumed you have a table with the same name as any url in list of urls, if your table names are different, you must use another variable for table names and fetch table names from a source too.



111.jpg
222.jpg

Reza_rad,

Let's move a step back for a while, I have tried to first download the files based on the steps you have provided, somehow, the value for the following line of code:

 Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + Dts.Variables("User::UrlString").Value.ToString() + ".xls"

comes out to be:

"C:\Matrix Data Feed\C:\AUTOEXEC.BAT.xls"

to be more specific, the value for Dts.Variables("User::UrlString").Value.ToString() + ".xls" wrongly comes out to be:
"C:\AUTOEXEC.BAT.xls"

It should be "MaterialHierarchySource.xls" as the first url in the table is the following:

http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=cFFXYXFKOUhGS3lpRVVJSkVSZ3dxS082SG1PT29LRGxEeHE0QUQ9PQ==&tabs=MaterialGroup&outputFileName=MaterialHierarchySource.xls

any clues what is wrong here?

I think your foreach loop enumerates files in c:\
and then it will fetch c:\autoexec.bat  .
It must be ADO.NET enumerator

could you upload your ssis package here, or any screenshot from enumerator setting window in foreach loop container?

I apologize as I did not follow your step 5 correctly. Now I did and below is the screenshot. However, now at the same line of code as mentioned above, the value for Dts.Variables("User::UrlString").Value.ToString() + ".xls" comes out to be:

"C:\Matrix Data Feed\http://emat:7001/emat/common/JPOServlet.jsp?service=true&cmd=WS_ABO_CreateAccessoryReport&sk=cFFXYXFKOUhGS3lpRVVJSkVSZ3dxS082SG1PT29LRGxEeHE0QUQ9PQ==&tabs=MaterialGroup&outputFileName=MaterialHierarchySource.xls.xls"

Can we extract only file name from the UrlString variable?

For-Each-Loop.JPG

good,
now,
If you want to extract only file name from urlstring, then you must use a rule for it,
for example i think the rule is that, your file names always come with outputfilename= before and .xls after.
so you must extract this

and then you must use FullUrlString as your file name

Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

Select allOpen in new window

this is full script task code now:

' Microsoft SQL Server Integration Services Script Task 
' Write scripts using Microsoft Visual Basic 
' The ScriptMain class is the entry point of the Script Task. 
Imports System 
Imports System.IO 
Imports System.Text 
Imports System.Windows.Forms 
Imports Microsoft.SqlServer.Dts.Runtime 
Public Class ScriptMain 
    ' The execution engine calls this method when the task executes.  
    ' To access the object model, use the Dts object. Connections, variables, events,  
    ' and logging features are available as static members of the Dts class.  
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
    '  
    ' To open Code and Text Editor Help, press F1.  
    ' To open Object Browser, press Ctrl+Alt+J.  
    Public Sub Main() 
        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"  
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing) 
        ' Create a new HTTP client connection  
        Dim connection As New HttpClientConnection(nativeObject) 
 

       'exteract filename for urlstring
        Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

        ' Download the file #1  
        ' Save the file from the connection manager to the local path specified  
        Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString  + ".xls"
 
        connection.DownloadFile(filename, True) 
         
         
        Dts.TaskResult = Dts.Results.Success 
    End Sub 
 
 
End Class

Select allOpen in new window

Now it fails on this line of code and apparently because during debugging, the ServerUrl property of the connection object comes hardcoded as:
connection.ServerURL="@[User::UrlString]"

any clues?

Forgot to mention that it fails on this line of code:

connection.DownloadFile(filename, True)

and apparently because during debugging, the ServerUrl property of the connection object comes hardcoded as:
connection.ServerURL="@[User::UrlString]"

Here is the screen shot for the http connection manager (please see below)
HTTP-Connection-Manager.JPG

Not in this way!

you must do this:
put first url in serverURL in http connection manager, as you worked with it before, i mean exact url not variable.
then right click on http connection manager in connection managers section, select properties
you will redirect to properties window
find expressions there
click on ... front of expressions
in the new window dialog box, choose ServerURL property, and
in expressions area enter @[User::UrlString]

This worked great. However, last question for this thread (I will open a new one and redirect you to the link for inserting into tables). The last file downloaded seems like a big one and somehow there is a timeout error generated as below:

[Connection manager "HTTP Connection Manager"] Error: The request has timed out.This error can occur when the timeout specified was too short, or a connection to the server or proxy cannot be established. Ensure that the server and proxy URL are corrrect.

I am wondering where should I set the timeout property. In the http connection manager, I have already set the max allowed timeout value of 300 seconds. Any clues?

yes , this is timeout issue, and  you must set timeout property to appropriate value,
setting value with 300 doesn't changed anything? it must solve problem. how about greater values like 600 or more?

It does not allow for any timeout values greater than 300, is there any other property that can be set?

maybe the problem is in instantiating connections, try releasing connections like below:
add the bold line in specified location in script task and test whole package again.

        MessageBox.Show(data)

       Dts.Connections("HTTP Connection Manager").ReleaseConnection(nativeObject)
        Dts.TaskResult = ScriptResults.Success

I tried this but still getting the same timeout error:

[Connection manager "HTTP Connection Manager"] Error: The request has timed out.This error can occur when the timeout specified was too short, or a connection to the server or proxy cannot be established. Ensure that the server and proxy URL are corrrect.

How does the http manager deal with files that are larger in size? would it timeout after 300 seconds or there is a way around it?

I hadn't such experience on downloading large files in SSIS, but,
what about setting timeout with 0 ?
sometimes Microsoft consider 0 as infinite.

I tried to set the timeout property to 0 in the http connection manager editor, two things:

(1) Even the first file in the loop fails to download with the same timeout error as i mentioned above
(2) When I opened the http connection manager again, I found out that the timeout property is set back to 30 seconds (may be that is the minimum it can be set)

any clues?

Let me try to download large file and tell you the result

I checked with a 18MB file,
and it download correctly without any error for timeout.
that was interesting because i didn't even change timeout value, i Used the default that was 30 seconds!

did you checked you download links? maybe the download link has problem

Thanks reza-rad, I will talk to the web service providers and let you know

Reza-rad, I know you deserve points for this question, for reference purposes, can I use the same thread and let you reply for the post , and then open a new question refering to this link and you reply as refering to the same link directing to this question and I accept the question.

My question for the post 26575165, your comments:
"and select oledb destination , double click, set Data Access Mode: Table Name or view name variable,
and set Variable name as User::UrlString
look at attachment 2"

When I try to do that, I get the error:

"A destination table has not been provided"

Please see the screen shot below:

OLEDB-Destin-Error.JPG

I think it's better to use another variable for tableName, for example create a package scope variable of type string and name it DestTableName, set default value to your first destination table  (default value is important because you will got same error if you don't fill it with a correct table name at first)

and you must fill this variable in Script Task with table name,
I assume that your table name is same as your file name without .xls . and I assumed that all your tables exist in database.

now double click on script task
and in script task editor add DestTableName variable to ReadWriteVariables
then click on edit script and change script task like below:
Dts.Variables("User::DestTableName").Value = FullUrlString

I attached Main() method whole script in script task here:

Public Sub Main()

        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"  
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
        ' Create a new HTTP client connection  
        Dim connection As New HttpClientConnection(nativeObject)


        'exteract filename for urlstring
        Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

        Dts.Variables("User::DestTableName").Value = FullUrlString
        ' Download the file #1  
        ' Save the file from the connection manager to the local path specified  
        Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString + ".xls"

        connection.DownloadFile(filename, True)

        Dts.TaskResult = ScriptResults.Success
    End Sub

Select allOpen in new window

I tried this way and it seems like I am close to have it worked. It does download the two files from the two urls in a table to the correct drive path, however, it only fills the first table and does not fill the second. Here is how the control flow and data flow diagram looks like (see below):

Any clues what is missing here to stop the data from the second download inserted into the table (I made sure that the destination table name and the file name is the same)?
Control-Data-Flow-Diagram.JPG

Structure of both tables must be same? are those same?
and what is that OLEDB source and recordset destination ?????

structure is the same. It's not going to the second table from the loop. The package runs succssfully with no errors

OLEDB Source is the table where the file url is fetched from. The recordset destination is where this url is stored as an input column (from the OLEDB source) in the variable name  User::UrlLoopVar

Oh, the problem is:
you must have two Data Flow Tasks not one !
first one must be there it located now, I mean before foreach loop container, but it must have only oledb source and recordset destination in it.
second one:
add another data flow task inside foreach loop container,after script task, connect green arrow from script task to this data flow task
and you must have excel source and oledb destination here in this data flow task, because this data flow task will execute in loop.

I tried as you suggested, now it does the follwoing succesully:

(1) It downloads the first file correctly
(2) It inserts the data from the first downloaded file to the first table correctly (Loop 1 done)
(3) It downloads the second file correctly (loop 2 started)
(1) It fails to insert the data from the second downloaded file to the secon table and throws the follwoing error:

[OLE DB Destination [100]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Material Group" needs to be added to the external metadata column collection. The "external metadata column "Level 4" (193)" needs to be removed from the external metadata column collection. The "external metadata column "Level 3" (192)" needs to be removed from the external metadata column collection. The "external metadata column "Level 1" (191)" needs to be removed from the external metadata column collection.

Note the first file and first table has the following columns:
Name, Description, Level 1, Level 3, Level 4

and the second file and second table has the following columns:
Name, Description, Material Group

From the description of the error it appears that somehow its tryiing to insert the first file into the second table, what do you think?

It's Impossible Skaleem1 !
first rule for using dynamic source and destinations in Data Flow is that you MUST HAVE same structure at all.
you can no thave columns like Name,Description,Level1,Level3,Level4 in first file and first table . and in other hand have different structure like Name,Description,Material Group in second one !
mapping columns in SSIS is static,you can not do them dynamically, when you design data flow task , columns mapped statically, this is the reason of why you need a default value for table variables, because SSIS needs to map columns for first time and use them after this.

So, you have 2 ways :
1- create separate data flows for each file and its own table and don't do any dynamic data flow.
2- use same structure in all files and tables.

Regards,

um,
there is a third way too:
don't use Data Flow to transfer data !
use Execute SQL Task only, in this execute sql task , you need to use OPENROWSET to select data from excel file and insert into sql table.

simple syntax of OPENROWSET is:
SELECT * INTO yourtable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
but there are few tricks here:
1- using OPENROWSET in sql server ( specially 2008 ) needs special permissions.
2- you must make your tablename and also excel file path dynamic in that query.
making these things dynamic needs to create a dynamic sql query, and this needs create a varchar first and then run it by sp_execsql .

This seems lots of work, but if you can not choice previous 2 ways, this is the only possible way!

Regards,

I would'nt mind using the Execute SQL Task and generating dynamic queries. Is there a way to get the file and table names in the Execute SQL Task dynamically right after the script task? If yes would I be using package level variables to do that?

Using the dynamic sql query should not be a great issue i believe. Would you be able to help me with that, should I rather remove the current data flow task and add one Execute SQL Task within the foreach loop container followed by the script task? and then dynamically genearate my query based on the destination table name and the xls file name etc? Please shed some light?

I followed all the steps as you suggested and the first loop in the Script task was executed correctly as well as the Execute SQL Task correctly inserted the rows in the related table but then the process failed with the following two errors and the next loop did not even execute in the script task because of the failure:

First Error:
Error: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'MaterialGroup'.  Make sure the object exists and that you spell its name and the path name correctly.".

Second Error:
[Execute SQL Task] Error: Executing the query "SELECT * INTO AccMaterialHierarchySource FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Matrix Data Feed\AccMaterialHierarchySource.xls', 'SELECT * FROM [MaterialGroup]')" failed with the following error: "Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Note that the records from the first Excel file were correctly inserted into the destination table, and then it throws the errors afterwards. Any clues?

Riza_rd,

Don't worry about the last post, it now works perfectly and I have made the following changes in my script task script (please note (1) how I have changed the query to Insert Into as the tables would always be existing and (2) all sheet names are different from the different files so I am taking this into consideration)::

Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    ' The execution engine calls this method when the task executes.  
    ' To access the object model, use the Dts object. Connections, variables, events,  
    ' and logging features are available as static members of the Dts class.  
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
    '  
    ' To open Code and Text Editor Help, press F1.  
    ' To open Object Browser, press Ctrl+Alt+J.  
    Public Sub Main()
        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"  
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
        ' Create a new HTTP client connection  
        Dim connection As New HttpClientConnection(nativeObject)

        'exteract filename for urlstring
        Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

        Dts.Variables("User::DestTableName").Value = FullUrlString


        ' Download the file #1  
        ' Save the file from the connection manager to the local path specified  
        Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString + ".xls"

        'Dts.Connections("HTTP Connection Manager").ReleaseConnection(nativeObject)
        Dim SheetName As String

        Select Case FullUrlString
            Case "AccMaterialHierarchySource"
                SheetName = "MaterialGroup"
            Case "AccAllPartsSource"
                SheetName = "Parts"
            Case "AccPartsDocMappingSource"
                SheetName = "PartsDocMapping"
            Case "AccVendorMappingSource"
                SheetName = "VendorMapping"
            Case "AccBomSource"
                SheetName = "BOM"
            Case "AccWhereUsedSource"
                SheetName = "WhereUsed"

        End Select

        Dts.Variables("User::SqlString").Value = "INSERT INTO " + FullUrlString + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"

        connection.DownloadFile(filename, True)


        Dts.TaskResult = Dts.Results.Success
    End Sub


End Class

riza_rad,

You deserve to get points for two questions as I promised. I will accept your answer for this question and then
open another question and that will have the link directed to this question and then you can reply (may be the
same link in your reply) and then I can accept it as a valid answer to reward you the points you deserve. Thanks a
lot for your help.

Thanks a lot Riza-rad

Riza_rad,

Here is the link you can go to have your points refunded:

https://www.experts-exchange.com/questions/25158641/SSIS-How-to-dynamically-import-data-from-multiple-spreadsheets-to-multiple-SQL-Server-tables.html?fromWizard=true

Thanks again