SSIS - Load File's information to SQL Server Table

Scenario:

You are working as SQL Server Integration Services(SSIS) developer, You need to create an SSIS Package that should be able to load file's information form a folder to SQL Server table. The SSIS Package should load file name, file created date-time and file size in kilo bytes.

Solution:

First of all we need to create a table in which we would like to save file's information from a folder.

use YourDatabaseName
go
CREATE TABLE dbo.FileInformation
(
Filename VARCHAR(100),
FileCreatedDateTime DATETIME,
FileSize INT
)

Step 1:
Open BIDS or SSDT and create new SSIS Package, In your SSIS Package, create a variable VarDirectoryPath of string type. Provide value to this variable C:\MyTestFolder as you would like to create the file's information from this folder. You will be providing the path of folder in which you have the files.
Create variable in SSIS Package to load file's information to SQL Server Table


Step 2:
Create an ADO.NET Connection to your Database and rename to DBConn.
Create an ADO.NET connection in SSIS Package to use in Script Task to load File's information to SQL Server Table

Step3: 
Drag Script task to Control Flow Pane and add  VarDirectortyPath as input variable as shown below
Add user variable to Script Task in SSIS Package to load file's information to SQL Server Table



Step 4: 
Click on Edit Script Button and then copy below code to your Script Task Editor.

Under #Region NameSpaces, paste below namespaces.

using System.IO;
using System.Data.SqlClient;


Got to public void Main()
{
and paste below code.

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
//MessageBox.Show(myADONETConnection.ConnectionString, "ADO.NET Connection");

string DirPath = Dts.Variables["User::VarDirectoryPath"].Value.ToString();
//get all files from directory
string[] files = Directory.GetFiles(DirPath);
SqlCommand sqlCmd= new SqlCommand();
sqlCmd.Connection = myADONETConnection;

//loop through files
foreach (string filename in files)
{

FileInfo file = new FileInfo(filename);

sqlCmd.CommandText = "Insert into dbo.FileInformation Values('" +
 file.Name + "','" + file.CreationTime + "','" + file.Length/1024 + "')";
//MessageBox.Show(sqlCmd.CommandText);

sqlCmd.ExecuteNonQuery();
}

Dts.TaskResult = (int)ScriptResults.Success;
}


Step 5:

Save the script and close the script task editor window. Run your SSIS Package, it should load the file's information from folder to Test.dbo.FileInformation table.

43 comments:

  1. This is a nice article..
    Its very easy to understand ..
    And this article is using to learn something about it..

    c#, dot.net, php tutorial

    Thanks a lot..!

    ReplyDelete
  2. I sent you an email about sqlconnection in the c# script

    ReplyDelete
  3. One imperative manner by which information technology is influencing work is by diminishing the significance of separation. bezoek website

    ReplyDelete
  4. Of course, you can get a customary 800 number from the telephone organization, yet how well is it getting down to business for you? https://callgear.com/product/phone-numbers/

    ReplyDelete
  5. Tracking the drone operator! Since the URBAN DYNAMITE® RF Drone Detector detects both the drone (by its downlink data signals) and its corresponding remote control, the movement of both can be tracked immediately. Drone Jammer

    ReplyDelete
  6. In order to make this law stand, it is the committee’s job to contact the manufacturers of these cellular intercept systems before selling them to local and state law enforcement agencies and they must notify the FBI first. CUAS System

    ReplyDelete
  7. Hi guys, I've followed this through and it runs "succesfully" but doesn't seem to output any results... is there a clash if this is being run on later editions of ssis / target deployment of ss2017?

    ReplyDelete
  8. The intensity of this thought ought not be disparaged, it enables individuals to settle on choices dependent on masses of cutting-edge data. The military, instructive foundations and enormous business have since quite a while ago comprehended the intensity of this thought yet it has just been over the most recent ten years that standard society has likewise grasped the intensity of data for both social developments and independent venture. information technology

    ReplyDelete
  9. An organization name, brand or exchange mark name ought to consistently be remembered for the body of the instant message. SMS API Service

    ReplyDelete
  10. Do your research. Check the people behind the course that you would like to sign up to cursos de ti

    ReplyDelete
  11. This is why as suggested earlier, that writers should do their study before diving into the writing project. Helphub Review

    ReplyDelete
  12. Support technology proposition for enormous scope national and universal level tasks
    top cat breed

    ReplyDelete
  13. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... Sioux Falls IT Services

    ReplyDelete
  14. Succeed! It could be one of the most useful blogs we have ever come across on the subject. Excellent info! I’m also an expert in this topic so I can understand your effort very well. Thanks for the huge help Make money online

    ReplyDelete
  15. We are pretty much convinced that using this information, you as our readers will be able to take better decisions and understand the base of this topic in a better manner. 360DigiTMG data science training in hyderabad

    ReplyDelete
  16. This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data sciecne course in hyderabad

    ReplyDelete
  17. Learn more craft glitter tips by visiting the website for Rainbow Turtle, a retailer of fine glitters in various sizes and colors Visit Buy glitter in bulk

    ReplyDelete
  18. Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.
    Data Science Course Training in Hyderabad

    ReplyDelete
  19. If you use decorative cosmetics make sure that the color additives are permitted by FDA. Color additives that not approved by FDA sometimes are colors for textile, not for food and cosmetic. panda gift

    ReplyDelete
  20. They have a competent team of expert freelance writers who have vast experience in all areas such as commercial, social and academic. Essay Writing Service

    ReplyDelete
  21. Good to become visiting your weblog again, it has been months for me. Nicely this article that i've been waited for so long. I will need this post to total my assignment in the college, and it has exact same topic together with your write-up. Thanks, good share.
    data science training

    ReplyDelete
  22. I curious more interest in some of them hope you will give more information on this topics in your next articles.
    data science training

    ReplyDelete
  23. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. Orlando sales recruiters

    ReplyDelete
  24. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me !data science training in Hyderabad

    ReplyDelete
  25. As of now, it influences such huge areas as correspondences, account and retail exchange and may grow to territories, for example, training and wellbeing administrations. It infers the consistent utilization of data and correspondence innovation along the whole worth chain of a business that is led electronically.
    Alfresco Training

    ReplyDelete
  26. In molding the construction and elements of work IT company Toronto associations, plants, and office, current information technology is viewed as one of central players among numerous businesses.

    ReplyDelete
  27. This comment has been removed by the author.

    ReplyDelete
  28. We've had a superior possibility of standing out enough to be noticed of Tom Cruise strolling honorary pathway than our children. Security Cameras

    ReplyDelete
  29. I’m happy I located this blog! From time to time, students want to cognitive the keys of productive literary essays composing. Your first-class knowledge about this good post can become a proper basis for such people. nice one vape attic olathe

    ReplyDelete
  30. Easily, the article is actually the best topic on this registry related issue. twitter trend topic

    ReplyDelete
  31. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    DevOps Training in Hyderabad
    DevOps Course in Hyderabad

    ReplyDelete
  32. Si deseas aprender las denominaciones oficiales (en catalán) utiliza el juego en catalán: comarcas de catalunya. On es? Localización. Localización. Continente.
    mejortorrent1- Podrás descargar peliculas y series divx en español con torrents para bittorrent, siempre a la última con películas y otras muchas descargas!
    La forma más rápida de consultar tu cita SEPE, es hacer una rápida llamada al número de teléfono oficial del INEM: 900 81 24 00
    Mapamundis de todo tipo para imprimir ? Políticos y Físicos ? Imágenes de mapas del mundo de gran tamaño y definición HD ? Encuentra tu planisferio
    Shillong Teer Common Number — Shillong Teer Result 13-12-2021, Shillong Teer Hit Number, Morning Common Number. Are you searching Shillong Teer Result,
    Get fastest updated Teer results online here. We provide shillong teer results, Khanapara Teer results, Juwai Teer Results, Night Teer results online fast.
    For the eleventh year in a running, dusty tuckness net worth has been named Bullfighter of the Year, thereby making the 32-year-old Dubois native

    ReplyDelete
  33. Very interesting blog. Alot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definately interested in this one. Just thought that I would post and let you know.
    cyber security course malaysia

    ReplyDelete
  34. I like your post. I appreciate your blogs because they are really good. Please go to this website for Data analyst course in Bangalore. These courses are wonderful for professionals.

    ReplyDelete
  35. It will cost organizations overall 500 billion bucks to "clear the excess of upkeep" and arrive at a completely upheld current technology climate. NE Solutions Ltd

    ReplyDelete