How to give file path from table dynamiclly in SSIS packages

Question

How to give file path from table dynamiclly in SSIS packages

Surender-485963

SSC Veteran

Points: 256
More actions
March 7, 2008 at 2:15 am

#121052

Hi Buddies,
I am new for SSIS I want to process the list of excel or CSV file path from Table to process the Import data from Excel file or CSV file to Database and at the same time at the beginning of each I have to send the email to that particular user.
Plz help me in this .
Thank,
Surya

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Half Abude SSC Enthusiast Points: 158 More actions · Answer 1

Hi Surya,

that can be done with a Execute SQL Task (EST) to retrieve your file paths and store them in a variable of type Object. After the EST, add a ForEach Loop Container (FLC), that iterates over the Object variable you've created (In the FLC editor, "Collection" section, you choose "For Each from Variable Enumerator" in the Collection property). Configure the FLC to store the filepath in another String variable. Use Expression to set the Connection Manager to use this String variable.

I've done it a lot of times, and it works perfectly.

Just a comment: you can also iterate over files in a specific directory, with a specific pattern (you specify a mask in the FLC). That would be easier for you. In the FLC editor, "Collection" section, you choose "For Each File Enumerator" in the Collection property.

Check Books on Line to understant the For Each Loop Container, and the let me know if you have additional questions.

Surender-485963 SSC Veteran Points: 256 More actions · Answer 2

Thanks Half Abude,

I will try in your process way.

I am new to SSIS. can you give me more clear on this process system.

Regards

Surya.

Half Abude SSC Enthusiast Points: 158 More actions · Answer 3

For sure.

1. Create a new package.

2. Add a variable (i will refer to it as "Var") of Data Type "Object"

3. Add a Execute SQL Task (EST).

4. In the "General" page of the "EST Task Editor" (the dialog that opens with double clicking the EST) configure the connection manager, and the query that will retrieve the file paths.

Make sure that your query returns only the columns you need!

5. Set the "ResultSet" property on page General to "Full Result Set" (EST Task Editor).

4. In the Result Set page of the "EST Task Editor", click button "Add". Change "Result Name" to 0; in the variable name column, choose the variable you've created in step 1.

5. Now create another variable, that will hold the file paths you want to use. It will probably be a string, right?

6. Add a ForEach Loop Container. Connect the EST to the ForEach Container.

7. Open the ForEach loop Editor (double click). In the Collection page, choose "For Each ADO Enumerator" in Enumerator property. Note the box bellow will change. In the ADO Object Source Variable, choose the variable from Step 1. Enumeration mode: Rows in the first table.

8. In the page Variable mappings, click the first line in column Variable and the choose the variable created in step 5.

The Index column refers to the column from the result set. If only need the file path, then it will be on Index 0.

9. (Optional) If you want to perform debug, add a breakpoint in the ForEachLoop Container (right button, Edit Breakpoints; choose 'Break at the beginning of every iteration of the loop'). Execute the package and look the Watch Locals window (available from Debug menu).

Now, add inside the ForEach Loop Container all the processing tasks that must be performed for each file you have. That's it!

Chinna SSCertifiable Points: 5408 More actions · Answer 4

srry to interuupt what does it actually do ...

does he want to execute a bunch of files with .txt in a folder or ...make a list of files which are from .csv and .txt can i know that even i m new to ssis so dnt mind half ....i recently implemneted executing a .csv files from a folder at once into a table using for eaach loop ...could u plz let me know ???

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Half Abude SSC Enthusiast Points: 158 More actions · Answer 5

In fact I'm not sure what Surya really wants to accomplish 😀

If you want to load some files from a specific folder, the task is much simpler. ForEach Loop Container will let specify a pattern for the name (example: "Sales_*.txt") and the directory.

The approach I've explained applies only if the file paths are completely arbitrary; or, for example, they are dynamically supplied by an external application.

Chinna SSCertifiable Points: 5408 More actions · Answer 6

hey half but i know implemeted to excute a list of files froma folder but i couldnt understand what is exactly dng and ehat do u mean by if the file is externall application or smthng u said what does that mean ...plz dnt mind saying basics....thkz in advance

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

kvaramu2005 SSC-Addicted Points: 417 More actions · Answer 7

Hi friends

I am totally new for SSIS

I need to extract the data from the Three tables and insert into one table based on the conditions.

Is there any way that i can use loop containers to do this task.

If Yes!

Please give me step by setp process.

bye

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 8

Hello there guys.....

There are actually many ways to accomplish this. But you have to ask yourself first, what do you want to accomplish?

A.If you just want to bring in a flat file to a table in the Database of SQL Server(if only once a day), it is easier to just do the below:

In Control Flow:

1. Drag and drop Data Flow Task; Double click it and you will go to the Data Flow tab.

In DataFlow:

1. Drag and drop Flat file source; set all in here

2. Drag and drop OLE DB destination; set all here as well.

( in between, if you want to cleanse and validate anything that is not very clean in the flat file, then of course you will have to add some other transformation to do that).

Then schedule this as a job to run once a day to what is appropriate to your situation.

B. Otherwise, if you have to check, if the file exist, then import, other wise, wait until the file exist, then import.( for this, you have to use something like what "Half" has explained above. Or use some programming.

I want to advise some of you here that the process may(definately will) take more than what we can show here in the forum. It is impossible to show EVERYTHING in the forum. So, for those who need help, please don't get me wrong, go and read more on SSIS and its Task and transformations. If you can do this, then you will ask something very much into detail and then you will have more gurus here to help you. Other wise, spoon feeding everything will not take you anywhere.

I am trying to help you guys here. Please read more before asking every steps. 😎

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 9

Oh I almost forgot.......

Also, if the file that you are trying to take and put in SQL table is in the FTP, it is slightly different. Please let me know if you face this problem. I can show the steps.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 10

If this is not an ongoing thing, or if you want the user to be able to do this, don't forget about using package configurations which can actually pass values to a variable.

SSIS is a powerful tool, which allows many different ways to complete the same task. One thing to remember, don't go for grand when simple will do and SQL Server Central, as well as Google is your friend

Marvin Dillard
Senior Consultant
Claraview Inc