As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue. Let me share that problem with all readers. With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ? This is very practical problem that we need to fix. To solve this, I come up with following solution. 1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.    2. Configure Derived Column:   As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression. By default it assign UNICODE STRING DataType to this new column. We need to change it by: A. Right click on "Derived Column", Go to Show Advanced Editor B. Set DataType to String as: 3. That's it. Now just add it to Destination Column Mapping with your Database column. Let me know your suggestions.

Labels: Data Transformation, Derived Column, For Each Loop Container, SQL, SQL Server 2005, SSIS, SSIS Task, Tejas Shah

Last Saturday, 19th December 2009, I presented session on "Write CROSS TAB Query with PIVOT". This was a regular User Group meeting held every third Saturday of month. This user group is running by Jacob Sebastian, President of PASS regional committee for the Asia and Middle East region. Last Saturday, I get a chance to present a session with Jacob Sebastian (SQL SERVER MVP) and Pinalkumar Dave (SQL SERVER MVP). I am member of this User Group from last 1.5 years. and I found that it such a great event to learn something new from great leaders. If you need PowerPoint Presentation and Demo script, send me an email at: tejasnshah.it@gmail.com. If you want to register for Ahmedabad User Group meeting, please register at: Click Here                                                              Tejas Shah (me) Tejas Shah (Me), Presenting session Tejas Shah (Me), Presenting session Pinalkumar Dave (SQL SERVER MVP), Presenting session User Group Members with President, Jacob Sebastian, Pinalkumar Dave If you want to register for Ahmedabad User Group meeting, please register at: Click Here

Labels: Ahmedabad User Group, PIVOT

The Transfer Jobs task can be configured to transfer all jobs, or only specified jobs. You can also indicate whether the transferred jobs are enabled at the destination.

The jobs to be transferred may already exist on the destination. The Transfer Jobs task can be configured to handle existing jobs in the following ways:

• Overwrite existing jobs.
• Fail the task when duplicate jobs exist.
• Skip duplicate jobs.

2. To configure a task, Right click on  Transfer Jobs Task, which we dragged to Design surface. Click on "Edit.", you will get page as:

3. SSIS Transfer Jobs Task - General : Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we need to design this task.

4. SSIS Transfer Jobs Task - Jobs : Jobs page of the Transfer Jobs Task Editor dialog box is required to specify properties for copying one or more SQL Server Agent jobs from one instance of SQL Server to another. 

Let's take a view how each properties are used.

SourceConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the source server

DestinationConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the destination server.

Labels: SQL Server 2005, SSIS Task, Tejas Shah, Transfer SQL Jobs

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])

INSERT INTO [Info]
SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

Labels: Import Excel file, SQL Developer, SQL Server 2005, T-SQL, Tejas Shah

Please find this code to remove cursor with Table variable:

--declare table to keep records to be processed
DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))
--populate table variable with data that we want to process
INSERT INTO @Table(Column1, Column2)
SELECT    Column1, Column2
FROM    <Table>
WHERE    <Conditions>
--declare variables to process each record
DECLARE @inc INT, @cnt INT
--Assign increment counter
SELECT @inc = 1
--Get Number of records to be processed
SELECT @cnt = COUNT(*)
FROM @Table
WHILE @inc <= @cnt BEGIN
    --As we have AutoID declared as IDENTITY, it always get only one record.
    --Get values in Variable and process it as you want.
    SELECT    @Column1 = Column1,
            @Column2 = Column2
    FROM    @Table
    WHERE    AutoID = @inc        
    --do your calculation here
    ....
    ....
         
     --Select next record
     SET @inc = @inc = 1 
END

By this way, we can remove CURSOR by Table variable. 

It is quite easy to implement.

One more benefit is: It will process one record at a time, so it locks only that record at a time.

Let me know if you have any questions.

Labels: Cursor, Query Performance, SQL Developer, SQL Server 2005, T-SQL, Table Variable, Tejas Shah

Labels: For Each Container Task, SQL Server 2005, SSIS Task, Tejas Shah

Today, I am going to explain SSIS For Each Loop Task. I am sure that it will be easy to configure SSIS Package with Foreach Loop task, after you read this article.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Many times, user asks that I want to process all files from my folder to database. This is my answer to all of those users. SSIS comes up with For Each Loop which did the same.

Now I'm assuming that you're familiar with using the SQL Dev Studio tools and building basic packages. If this isn't the case, I recommend working your way through the Integration Services using my earlier posts.

1. SSIS Foreach Loop Container - General : Here we need to assign unique name to this container and also we can specify brief description, so we will get idea why we need to design this container.

2. SSIS Foreach Loop Container - Collection : Use the Collection page of the Foreach Loop Editor dialog box to specify the enumerator type and configure the enumerator.

Select the enumerator type from the list. This property has the options listed as follows:

1. Foreach File Enumerator: Enumerate files
2. Foreach Item Enumerator: Enumerate values in an item
3. Foreach ADO Enumerator: Enumerate tables or rows in tables
4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
5. Foreach From Variable Enumerator: Enumerate the value in a variable
6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
7. Foreach SMO Enumerator: Enumerate a SMO object

Let me explain with Foreach File Enumerator

We can use this Foreach File enumerator to enumerate files in a folder.

The content of the folders and subfolders that the Foreach File enumerator enumerates might change while the loop is executing because external processes or tasks in the loop add, rename, or delete files while the loop is executing. This means that a number of unexpected situations may occur:

In Folder option, provide the path of the root folder to enumerate. In Files option, specify the files to enumerate.

If wildcard characters are specified in the Files option, then the fully-qualified paths that are returned match the filter.

1. Fully qualified: Select to retrieve the fully qualified path of file names.
2. Name and extension: Select to retrieve the file names and their file name extensions.
3. Name only: Select to retrieve only the file names.

3. SSIS Foreach Loop Container - Variable Mappings : Use the Variables Mappings page of the Foreach Loop Editor dialog box to map variables to the collection value. The value of the variable is updated with the collection values on each iteration of the loop.

Variable: Select an existing variable, or click <New variable...> to create a new variable.

Index: If using the Foreach Item enumerator, specify the index of the column in the collection value to map to the variable. For other enumerator types, the index is read-only.

Once we configured, For Each Loop container, Lets add the process that we want. Here what I am going to do is:

1. Data Flow Task.

NOTE: We can add any other tasks to this container. I took Data flow process, as its already explained by this BLOG.

Here lets see the logical scenario of For Each Container Task.

First, From the selected folder, fetch all the files and assign file path to the variable.

Second, For Data Flow Process, process the file from the folder and execute data flow.

NOTE: For Data Flow Process, we need to assign Connection from variable. ( as we assigned variable after each loop, we need to process new file from the folder). So How can we configure File connection to use new file?

Please refer to my next article for How to assign Connection from variable.

That's it. This is what we need to design for "For Each Loop".

Let me know if there is any difficulties you have to design this package.

Labels: DBA, For Each Loop Container, SSIS, SSIS Task, Tejas Shah

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.

Labels: For Each Container Task, SQL Server 2005, SSIS, Tejas Shah

Today I am going to explain SSIS - ActiveX Task. Here I am just explain that how to write VB Script in this task, which will execute at run time. This ActiveX Task is mostly used when we  move SQL 2000 DTS package to SSIS. As I found that many developers had used ActiveX with DTS packages in SQL SERVER 2000. The ActiveX Script task provides a way to continue to use custom code that was developed using ActiveX script, until such scripts can be upgraded to use the more advanced features provided by the Script task. Let's take an example to easily understand how to use ActiveX Task with SSIS. 1. Select and Drag, ActiveX Script Task, from Container Flow Items to designer surface.   2. Now we need to write a script which will execute when SSIS package loads. To write a script, Right click on  ActiveX Script Task, which we dragged to Design surface. Click on "Edit.", you will get page as: 3. SSIS ActiveX Script Task - General. Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we design this task. 4. SSIS ActiveX Script Task - Script Task: This is main section where we need to write script. Language: We need to specify which script language we are going to use for this task, either VB Script, JScript etc..Here we are going to use VB Script language for this demo Script: Here we need to write a script. Click on ".." button located at right side. It will open a dialog to write a script, as shown in figure:   Let's write VB Script code inside to test this task. I write this script: function Main() Dim Stuff, myFSO, WriteStuff, dateStamp dateStamp = Date() Stuff = "SSIS Text ActiveX Task" Set myFSO = CreateObject("Scripting.FileSystemObject") Set WriteStuff=myFSO.OpenTextFile("D:\SQLYoga\SSIS ActiveX Task.txt", 8, True) WriteStuff.WriteLine(Stuff) WriteStuff.Close SET WriteStuff = NOTHING SET myFSO = NOTHING End function Save it and click on Parse, which will parse this script and let us know if there is any problem in this script. If It is on then its allowed to proceed with the next step. Expression: Use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes. That's it. Let's run task, by right click on task and click on Execute Task, as shown in following figure. You can either Execute Package by right click on Package name, from Solution Explorer.   Once you run it and if logged in user has write access to the given path, SSIS package will create file at that place. Now go to that given path and see file is created or appended with content that we want to write. That's simple to configure and use SSIS ActiveX Script Task within BIDS. Let me know if you have any questions in designing SSIS Package

Labels: ActiveX Task, SSIS, SSIS Task, Tejas Shah