Introduction
Sometimes we need to download the files from the FTP, a Web Site or another resource as the source data for an ETL process. In this article, we will show how to download multiple files from internet using SQL Server Integration Services (SSIS) and the script task. We will learn 4 things:
- We will first show how to download a single file using the Script task.
- Secondly, we will show how to download multiple files using the foreach loop>item enumerator and the script task.
- We will also show how to send all the files to download using a C# array.
- Finally, we will use the ADO object enumerator using the foreach loop with the script task.
Requirements
- SQL Server installed.
- SSIS and SQL Server Data Tools (SSDT).
- Excel installed (for the example 3 only)
How to Download a Single File Using the SSIS Script Task
In the first example, we will download a single file from internet. You can use any website of your preference. In this example, we are using a website named www.textfiles.com/adventure. This URL contains several files to download:
We will open SSDT and create a new SSIS project. In the SSIS Project, go to the menu and click SSIS>Variables:
We will create a string variable named filename and specify the name of the internet text to download. In this example, 221baker.txt is the name of one of the files on the www.textfiles.com\adventure web site:
In SSIS Toolbox in the SSIS project, drag and drop the Script Task:
You can program in C# (by default) or VB. We will work in C# this time. Also in ReadWriteVariables, add the filename variable created before and press Edit Script:
In the C# code expand region and add the System.Net namespace. System.Net will be used to download files in internet:
In the public void main function, add this code:
try
{
string myURI = "http://www.textfiles.com/adventure/";
string fileName = Dts.Variables["User::filename"].Value.ToString(), myWebString = null, myLocalPath = null;
myWebString = myURI + fileName;
myLocalPath = "c:\\sql\\" + fileName;
WebClient myWebClient = new WebClient();
myWebClient.DownloadFile(myWebString, myLocalPath);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(18, "The process failed", ex.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}The code will download a file named 221baker.txt. We first use the try and catch code. Try and catch are used to handle errors. If the code in the try fails, catch will show the error message.
We have in the code the variable definition. myURI stores the website URL with the files. The filename is a string that will receive the SSIS variable named filename created previously. The SSIS variable contains the filename. myWebString is the web concatenated with the filename. myLocalPath is the local drive where we will store the file created (c:\sql).
string myURI = "http://www.textfiles.com/adventure/";
string fileName = Dts.Variables["User::filename"].Value.ToString(), myWebString = null, myLocalPath = null;
myWebString = myURI + fileName;
myLocalPath = "c:\\sql\\" + fileName;
The next section is used to download the file. We create a webclient object and then we use the download function to download the file using the variables created before:
WebClient myWebClient = new WebClient();
myWebClient.DownloadFile(myWebString, myLocalPath);
Once the code is done, save it and close it. Right click the script task and select the Execute Task option:
If everything is OK, you will be able to find the 221baker.txt file in your local c:\sql folder:
How to Download Multiple files Using the Foreach Loop
In the first example, we learned how to download a single file. In this new example, we will download several files from a list using the Foreach Loop Container. This Container is used to use a Loop a task. The Item enumerator allows creating a list manually. In this example, we will create a list of files and the script task will copy the files using that list.
In SSIS, double click the Foreach Loop Container:
In the design pane, move the script task created in the example one inside the Container:
In the Foreach Loop Container, select Foreach Item Enumerator and press the Column button:
In the column created, add the list of files names in the web:
In Variable Mapping, select the filename variable:
If everything is OK, you will have all the files of the list created when you run the package.
How to Send All the Files to Download Using a C# Array
The example 2 is the best option if you have few files. However, in our web page, there more than 400 files in the website. It would take a long time to copy and paste the names using the item enumerator. The new example will show how to create an array with the list of files.
We will first go to www.textfiles.com/adventure and copy the filenames:
Copy and paste the results into Excel:
We will pivot the table to create a list of files in the format requested by C#. Select the Filename column and go to INSERT>Pivot Table:
In Create PivotTable, press OK:
Drag Filename to COLUMNS:
Copy the file names to a new Excel file:
Save the file as a CSV file:
You will receive a message about some features that will be lost. Press Yes:
Open the csv file with a text editor:
The file is in the following format (text1.tx;text2.txt, etc):
We need the text in this format:
"text1.txt","text2.txt",etc. To do this we are going to replace ; with "." using the Edit>Replace option in notepad:
Add manually " at the beginning and at the end of the text:
In SSIS, drag and drop a new Script Task and add the following code:
public void Main()
{
string[] arr = new string[] { "221baker.txt", "221bakerst.txt", "2400ad.txt", "2400adfaq.txt", "aaow.sol", "aas.faq", "abyssal.txt", "accoladecomics.txt", "adventhint.txt", "adventur.txt", "adventure.txt", "adventureland.txt", "adventureland2.txt", "advland.adv", "advquest.txt", "aencounter.txt", "aitd2rev", "aiw.sol", "al_dark.nfo", "aland.txt", "aland2.txt", "alondark.sol", "alone2.sol", "altered.sol", "amforever.txt", "amfv.txt", "amnesia.sol", "ampasswords.txt", "aod.txt", "aquest.txt", "archipel.txt", "arkwalk.txt", "atalan.sol", "authorsh", "aztectomb.sol", "aztectomb.txt", "ballyhoo.sol", "ballyhoo.txt", "bardsolv.txt", "bardstale.sol", "bardstale.txt", "bardstale3.sol", "bardtal2", "bastard.sol", "battech.sol", "beyondzork.txt", "blackca.sol", "blood.sol", "bond.sol", "borderzone.txt", "borrowt.sol", "brebynj.txt", "brimstone.txt", "bt1solv.txt", "bt2maps.txt", "bt_doc.txt", "bttf.sol", "buck.sol", "bureau.sol", "bureaucracy.txt", "bz1.wt", "bz2.wt", "bz3.wt", "bz4.wt", "ca-tsa.adv", "cadaver.txt", "camelot.sol", "cauldron", "cauldron.txt", "cblood.txt", "cbsolve.txt", "ccamelot.txt", "cdown.sol", "chamber.sol", "chamscip.txt", "chrono", "chrono.sol", "circedge.txt", "circuit.sol", "circus.txt", "civhints.txt", "cn-iman.sol", "cntdnsol", "codeeman", "colonel.txt", "colony.sol", "colorofmagic.sol", "colossal.sol", "comic.sol", "conqsolv.txt", "conquest.opl", "count.sol", "countdwn.sol", "cptblood.sol", "cracks_of_file.sol", "cranston.adv", "crime_time.sol", "crowley.adv", "curse.sol", "cutthroats.sol", "cyborg.sol", "d-dgsolv.txt", "dallasquest.sol", "danceofvampires.sol", "dandare.sol", "dantesinferno.sol", "dark.txt", "darkhart.sol", "darklod.sol", "darkseed", "darksolv.txt", "darthvader.sol", "deadline.sol", "dejasolv.txt", "dejavu.txt", "dejavu1.sol", "dejavu2.sol", "demons_winter.sol", "desrtwlk.txt", "destiny.txt", "detectivegame.sol", "discwrld.wlk", "ditkafix.nfo", "dizzy.sol", "dizzyprince.sol", "dkok.sol", "dlux.txt", "dracula.sol", "drag_sph.sol", "dragla.sol", "dragonskulle.sol", "dragonw.sol", "dragonworld.sol", "dragworld.txt", "drakkhen.sol", "dreamzone.txt", "ds2cheat.txt", "ducktale.sol", "dungeon.txt", "dungeonadventure.sol", "dungma.sol", "dutchmansgold.txt", "earthquake.sol", "earthquake.txt", "earthris.txt", "eco.txt", "eco1solv.txt", "eco2solv.txt", "eco_qus2.sol", "ecoqu1.sol", "ecstatic.slv", "ecstatic.txt", "ecstwalk.txt", "elite", "elvdunge.txt", "elvenemy.txt", "elvira.sol", "elvira1.sol", "elvira2.sol", "elvira2.txt", "elvira_2", "elvirasp.txt", "elvmaze.txt", "elvspell.txt", "emerald_isle.sol", "empireofkarn.sol", "epic.txt", "eriksol.sol", "eureka.sol", "eureka.tip", "eureka.txt", "expeiditionamazon.txt", "fahrenheit451.sol", "fairlight.sol", "farmersdaughter.sol", "feasibility.sol", "ferret.adv", "fgth.tip.txt", "finderskeepers.sol", "fish.sol", "fish.txt", "flunky.sol", "frankenstein.sol", "future.txt", "futurwar.txt", "garfield.sol", "gateway.sol", "gateway.txt", "ghosttown.sol", "goblins2.txt", "goldenbaton.sol", "grail.txt", "greatescape.sol", "gruds.txt", "guildofthieves.txt", "hacker2.txt", "hackersolution.txt", "hampstead.sol", "hellsend.sol", "herbertsdummy.txt", "heroesofkarn.txt", "heroqst1.txt", "heroqust.txt", "hhijinx.txt", "hitchikers.sol", "hobbit.sol", "hobbit.txt", "hollywoodhijinx.sol", "hqsolve.txt", "hquest.txt", "ht01.txt", "ht02.txt", "hugo1.sol", "hugo2.sol", "hugo3.sol", "hunchback.sol", "iceman.sol", "iceman.txt", "icftd.txt", "imagination.sol", "impossiblemis.sol", "impossiblemission2.sol", "incrediblehulk.sol", "indjones.txt", "indy3hnt.txt", "infidel.sol", "ingridsback.sol", "institute.sol", "institute.txt", "irish.txt", "jacknipper.sol", "jackripper.sol", "jinxster.sol", "jinxter.txt", "journeywt.txt", "karateka.adv", "kayleth.sol", "kcamelot.txt", "kentilla.sol", "kingiv.txt", "kingqst2.txt", "kingqst3.txt", "kingslve.txt", "kingtuts.sol", "kobayashi.sol", "kq-4.sln", "kq3-hint.txt", "kq4sol.txt", "kq5.txt", "kq5_1.txt", "kq5_2.txt", "kq5sol.txt", "kqiiihlp.txt", "kqiisolv.adv", "kquest.adv", "krynn.txt", "kukulcan.txt", "kyrandia.txt", "labyrinth.sol", "larry1.txt", "lastninja.sol", "lastninja2.sol", "lawofthewest.txt", "leathergoddess.txt", "legendapachegold.txt", "lhorror.txt", "lifeboat.sol", "loadsofmidnight.txt", "longbow1.txt", "longbow2.txt", "loomslv.txt", "lotrsol.txt", "lotsol.txt", "lsl2.txt", "lsl3.txt", "lsl3slv.txt", "lsl5.hnt", "lsl5hint.txt", "lsl6-wt.txt", "luciferssol.txt", "lurkinghorr.txt", "magicland.txt", "majik.sol", "man-eng.txt", "maniac.txt", "maniacmansion.txt", "mardrms.txt", "maskofsun.sol", "masquerade.sol", "maze.zk1", "mazmap.adv", "metalwarrior.txt", "mghtmag2.txt", "mh2hints.txt", "miamivice.sol", "mightandmagic.txt", "mightmagic2.txt", "mindfighter.sol", "mindshadow.txt", "mine.zk1", "miser.sol", "miser.txt", "missionasteroid.sol", "mm2codes.txt", "mm2hintf.txt", "mmiiihin.txt", "monkey-i.txt", "monkey.txt", "montyonrun.sol", "moonmist.sol", "moonmist.txt", "mordonsquest.txt", "mountainsket.txt", "ms-dvi.txt", "ms-sg.txt", "ms-un.txt", "murderonm.txt", "musketeers.sol", "mysterfh.txt", "neuro.txt", "neuroman.sol", "neuromancer.sol", "neverending.sol", "nightofaliens.sol", "oo-topos.adv", "para.txt", "pawn.sol", "pawn.txt", "perseus.sol", "phantasie1.sol", "phantasie2.sol", "pilgrim.sol", "pirate.sol", "plan9.txt", "planetfall.sol", "plundered.sol", "plundered.txt", "pn.txt", "pq2.txt", "pq2_solv.txt", "pqhints.txt", "pyjamarama.sol", "pyramidod.sol", "quest.sol", "quest.txt", "questforgrail.txt", "questforwhorehouse.txt", "questii.txt", "questron.sol", "questron.txt", "questron2.sol", "rebelplanet.txt", "redhawk.sol", "returntooz.txt", "ringofpower.sol", "risedrag.txt", "roadwar2000.sol", "robinhood", "robinofsherwood.txt", "s4king.txt", "sbardt1", "sbardt3", "scamelot", "scapeghost.sol", "sceptreofb.sol", "schron", "scirce", "scodeic", "scolonq", "scotadam.adv", "scryptm", "sdejavu", "sdemonfo", "se-kaaofassiah.sol", "seabasedelta.txt", "sexvixen.txt", "sexvixens.txt", "shadowgate.txt", "shadowsofmordor.sol", "sherlock.txt", "sherlockholmes.sol", "sinbadgs.sol", "skooldaze.sol", "sky_walk.txt", "smashed.sol", "smugglersinn.sol", "solve.txt", "solvefor.txt", "sorcerer.sol", "sorcerer.txt", "sorcererofclaymorgue.sol", "sorceria.txt", "spaceace.sol", "spaceace.txt", "spaceii.txt", "spelcst.txt", "spell101.txt", "spellbound.sol", "spellbreaker.sol", "spellbreaker.txt", "spiderman.sol", "spiderman.txt", "spidermountain.sol", "spiii.txt", "spot.txt", "spytrek.sol", "sq4.hnt", "sq4debug.txt", "sq5solve.txt", "sqstory.txt", "srcastle.txt", "st25.txt", "staffofkarnath.sol", "starcross.sol", "startrek.txt", "startrekpromethean.txt", "stationfall.sol", "stationfall.txt", "stoneville.txt", "strikeforcecobra.txt", "sudan.txt", "sundog.txt", "suspect.sol", "suspended.sol", "sydneyaffair.sol", "t7g.hlp", "tasstimes.sol", "tasstimes.txt", "tenlittle.sol", "terrormolinos.sol", "threeweeks.sol", "timetunnel.sol", "timezone.sol", "tirnanog.sol", "tobeontop.sol", "tracesanction.sol", "transylvania.sol", "trapdoor.sol", "trapdoor2.sol", "trinity.txt", "twinkingdom.sol", "u72items.nam", "u72npcs.nam", "u72spell.nam", "ugadventure.txt", "ugf.txt", "ulgf.txt", "ult5hint.txt", "ult6.txt", "ult6adna.txt", "ult6lang.txt", "ult6magc.txt", "ult6rfnc.txt", "ult6spls.txt", "ultima06.txt", "ultima3.sol", "ultima3.txt", "ultima7.txt", "uninvite.txt", "uninvited.sol", "uninvited.txt", "uppergumtree.sol", "urbanupstart.sol", "uukrul.hlp", "uukrul.sol", "vacstle2.txt", "valkyrie_17.sol", "vamphunt.drv", "vcastle1", "velnors_lair.sol", "venom.sol", "voodoo_castle.sol", "vquest.txt", "wally.sol", "waxworks.sol", "waxworks.txt", "webwalk.txt", "wega_station.sol", "willyb.sol", "wishbringer.sol", "witness.sol", "wizardofakyrz.sol", "wizardofoz.sol", "wizardscrown.sol", "wolfman.sol", "youngones.tips", "zork1.sol", "zork1b.sol", "zork2.sol", "zork3.sol", "zzzzzz.sol" };
try
{
string myURI = "http://www.textfiles.com/adventure/";
string myWebString = null, myLocalPath = null;
WebClient myWebClient = new WebClient();
foreach (string fileName in arr)
{
myWebString = myURI + fileName;
myLocalPath = "c:\\sql\\" + fileName;
myWebClient.DownloadFile(myWebString, myLocalPath);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(18, "The process failed", ex.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}sting [] arr is an array with the list of file names:
string[] arr = new string[] {"filename1.txt, "filename2.txt", etc."};We use the foreach loop to download each file:
foreach (string fileName in arr)
{
}When you execute the code, you will be able to see all the files downloaded in the local folder.
Use the ADO object Enumerator in the Foreach Loop
If you do not want to work with arrays in C# like we did in the example 3, there is another option. This example will import the list of files to SQL Server and then download the files using the list.
We will first go to www.textfiles.com/adventure and copy the filenames:
Copy and paste the results to Excel:
Save the first column in a file named listoffiles.txt.
We will import the files in SSIS. In the SSIS project, drag and drop Data Flow Task:
Double click Data Flow Task and in Data Flow drag and drop a Flat Fie Source and an OLE DB Destination:
In Flat file, select the listoffiles.txt file:
In columns, check the filenames:
Go the OLE DB Destination and connect to a database. In this example, we are connecting to the AdventureWorks2016 Database in SQL Server. We will create a table in that database. In the Name of the table or view, press the New Button:
Change the name of the table to listoffiles file or any table name of your preference:
Press mapping to map the flat file source and SQL Server destination column:
Run the package. If everything is OK, you will have the list of files in a SQL Server Table:
In a SSIS project, create a project with the Execute SQL Task, the Loop Container and inside the loop container, add the script task with the C# code used in the example 1 and 2:
In the Execute SQL Task, connect to AdventureWorks2016CTP3 Database. In ResultSet specify Full Result Set. In SQLStatements do a select filename from listoffiles:
In Result Set, specify 0 as the Result Name. In Variable Name, add the variable named filename created in the example 1:
In the Foreach Loop Container, go to Collection page and in enumerator select Foreach ADO Enumerator. This enumerator will get the information from the SQL table created. In ADO object source variable, select the filename variable and in Enumeration mode select Rows in the first table:
In variable Mapping, select the User:filename variable and in Index put 0. We are mapping here the foreach loop with the results of the Execute SQL Task:
A typical error is that the value DBNull is assigned to variable. In the progress page, you can see the error message:
The complete error message is the following:
Error: The type of the value (DBNull) being assigned to variable "User::filename" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
This error occurs because the parameter is a string and it is compared with a database object. To fix this problem change the filename variable data type to object:
If everything is configured correctly, you will be able to download all the files with SSIS.
Conclusions
In this article, we show how to download a single file and 3 ways to download multiple files. The item iterator is the best option if there are few files. The second option (C# array) is good if it is easy to convert the list of files to an array. The third option with the ADO Enumerator. Is the best option if there are multiple files and it is not easy to convert the list of files to an array.
