Last Article, SSIS - Foreach Loop Container, We need to assign dynamic connection to file connection, so SSIS For each loop Task can take each file from folder.
Lets configure File connection from variable for SSIS - Foreach Loop Container.
What we need to do is, we need to process each file from folder, so we need to assign value from variable to File connection, so SSIS Task will read that file and process that file.
To assign FileConnection dynamicaly we need to do following.
1. Right click on File Connection, click Properties.
2. Set DelayValidation = "False", as we need to assign connection dynamically.
3. Click on "Expression", and enter variable name, which we used in SSIS - Foreach Loop Container.
It will assign connection from variable and process that file.
Let me know if you have any question for the same.
November 9, 2009
- Foreach File Enumerator: Enumerate files
- Foreach Item Enumerator: Enumerate values in an item
- Foreach ADO Enumerator: Enumerate tables or rows in tables
- Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
- Foreach From Variable Enumerator: Enumerate the value in a variable
- Foreach Nodelist Enumerator: Enumerate nodes in an XML document
- Foreach SMO Enumerator: Enumerate a SMO object
- Fully qualified: Select to retrieve the fully qualified path of file names.
- Name and extension: Select to retrieve the file names and their file name extensions.
- Name only: Select to retrieve only the file names.
November 3, 2009
We have used Database mail to send mail to client on each updates.This is a very simple process to configure. Let me share how to configure Database mail with SQL server 2005 with all of you. After setting up Profile and Account properly, you just need to write following code to send a mail to client: Step 1: Step 2: Step 3: Step 4: You might get this message: Step 5: Create Profile Step 6 : Create Account That's it. exec msdb.dbo.sp_send_dbmail Let me know if you have any complexity or comments in setting up Database mail.
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html
We have used Database mail to send mail to client on each updates.This is a very simple process to configure. Let me share how to configure Database mail with SQL server 2005 with all of you. After setting up Profile and Account properly, you just need to write following code to send a mail to client:
Step 4: You might get this message:
Step 5: Create Profile
Step 6 : Create Account
Let me know if you have any complexity or comments in setting up Database mail.
November 2, 2009
Today, I am going to explain SQL SERVER SSIS, For Loop Container.
The For Loop container defines a repeating control flow in a package. The loop implementation is the same concept of the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
- An optional initialization expression that assigns values to the loop counters.
- An evaluation expression that contains the expression used to test whether the loop should stop or continue.
- An optional iteration expression that increments or decrements the loop counter.
Let's take an example to easily understand how to use For Loop Container with SSIS. Here I take example to iterate ActiveX Task.
1. Select and Drag, For Loop Container, from Container Flow Items to designer surface and add ActiveX Script Task to run inside the loop.
2. To configure this container, right click on this and click on 'edit'.
First we need to create variables to run this package based on variables. We can create variables by: View -> Other Windows -> Variables. Please find variable screen as below:
Here, I specified both variables. Count and Increment, that I am going to use for this example. I specified value Count = 20. So loop will be executed 20 times.
Let's take a view how each properties are used:
For Loop Properties:
1. InitExpression: Type an Initialization Expression in the given textbox. Initialization ensures that we are starting by setting out increment counter to 1. Here I specified variable to 1.
2.EvalExpression: Type an Evaluation Expression in the given textbox. For each iteration the evaluation expression checks to see if we have reached our maximum iteration count as set above when we defined @Counter. Here I specified that @Increment <= @Count, code inside the for loop will execute @Count times.
3. AssignExpression: Type an Assignment Expression in the given textbox. This is used to increment the counter by one for each iteration of the loop, otherwise the loop would never finish. Here I specified to increment variable by 1.
That's it. We have configured SSIS For Loop container.
Now when we execute this package, it will execute ActiveX Task, 20 times (as specified in variable count).
Let me know if you have any questions.