Showing posts with label SSIS Task. Show all posts
Showing posts with label SSIS Task. Show all posts

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties



4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

image
Let me know your comments/questions.
 

January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

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.


 SSIS For Each Loop Container II 

2. Configure Derived Column:

SSIS Derived Column Data Flow Transformation Configuration 
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:

SSIS For Each Loop Container II Advanced Editor

3. That's it. Now just add it to Destination Column Mapping with your Database column.

Let me know your suggestions.


 

December 7, 2009

SQL SERVER: SSIS - Transfer Jobs Task

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.
Let's take an example to easily understand how to use Transfer Jobs Task with SSIS.

1. Select and Drag, Transfer Jobs Task, from Container Flow Items to designer surface.

SSIS Transfer Jobs Task

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:

SSIS Transfer jobs Task Editor

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. 

SSIS Transfer jobs Task Editor Jobs

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.

TransferAllJobs: Select whether the task should copy all or only the specified SQL Server Agent jobs from the source to the destination server.  This contains two options:

TRUE: Which copy all jobs
FALSE: Which copy only selected jobs.

JobsList: Click the browse button (.) to select the jobs to copy. At least one job must be selected.

SSIS Transfer jobs Task Editor JobList

IfObjectExists: Select how the task should handle jobs of the same name that already exist on the destination server.
This property has the options listed in the following table:

SSIS Transfer jobs Task Editor ObjectExist

FailTask: If job of the same name already exists on the Destination Server then task will fail.

Overwrite: If job of the same name already exists on the Destination Server then task will overwrite the job.

Skip: If job of the same name already exists on the Destination Server then task will skip that job.

EnableJobsAtDestination: Select whether the jobs copied to the destination server should be enabled. This contains two options:

TRUE: Enable jobs on destination server.

FALSE: Disable jobs on destination server.

5. SSIS Transfer Jobs Task - Expressions: Click the ellipsis to open the Property Expressions Editor dialog box.

SSIS Transfer jobs Task Editor Expression

Property expressions update the values of properties when the package is run. The expressions are evaluated and their results are used instead of the values to which you set the properties when you configured the package and package objects. The expressions can include variables and the functions and operators that the expression language provides.

Now 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.

SSIS Transfer jobs Task Editor Execute

Once you run this then all/selected jobs will be transferred to destination server as per given criteria.

November 28, 2009

SQL SERVER SSIS: How to assign Connection from variable

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.

SSIS Foreach Loop Container Connection

That's it.

It will assign connection from variable and process that file.

Let me know if you have any question for the same.

November 9, 2009

SQL SERVER: SSIS - Foreach Loop Container

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.

SSIS Foreach Loop Container

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.

SSIS Foreach Loop Container General

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.

SSIS Foreach Loop Container Collection


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:
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.

October 31, 2009

SQL SERVER: SSIS - ActiveX Task

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.
 1 SSIS ActiveX Script Task
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:

2 SSIS ActiveX Script Task
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:

3 SSIS ActiveX Script Task 
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.

 4 SSIS ActiveX Script Task

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

 

October 29, 2009

SQL SERVER SSIS: BULK INSERT Task

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK.

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way.

We can move data to SQL SERVER Table or View by BULK INSERT Task.

Let see How to configure SSIS BULK INSERT Task:

1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface:

Bulk Insert 1
2. Configure connection: Right click on Task and click on Edit

Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.

NOTE: The Bulk Insert task supports only OLE DB connections for the destination database.

Define Format:
We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement.

Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step.

Bulk Insert SSIS Task 2

3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers.

Bulk Insert SSIS Task 3

That's it. Now execute the package, you find that your Table/view is populated with data from provided text file.

Why we have both BULK INSERT AND DATA FLOW TASK ?
We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable.

The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.

With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import.

So, we can use either of these as per our requirements.

Let me know if you have  any questions.

Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)