Unable to create a SQL Job in my local machine

Error while creating a SQL Job in local SQL Server DB Instance.

I got below error when I try to create a SQL job in SQL Server 2005 Instance.

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. 

When i check for a fix, i see lot of users asking to uninstall and install the SSIS, but that does not resolve this issue.

This happens on 2 reasons.

1. If we don't have latest service pack installed (SP3) in your machine.
2. If we try to run the job with SQL Agent service account and that does have privilege to execute a SSIS package.

you can check the below code project link, which helps to create a proxy account for you domain account, using that you can run the job. 

Hope this helps.


CodeProject

SSIS Error code DTS_E_OLEDBError 0X8004005 ...

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
sometimes we get as "Login failed for user" or "User might not have permission to do the activity".

I was getting this error for quite sometime when I execute the package using "Execute Package utility" I tried looking this msdn site for , but that does not help. Even though i changed the settings and try to execute it is prompting the same error.

Reason: At last i got the solution from this msdn blog (which also contains solution for different kinds of SSIS errors). While setting the DB connection, I check the save password check box to avoid entering multiple times. That is reason for it. Normally if we have to set the password permanently for the connection then we are suppose to set the password for package also. And also the package encryption level must be set to "EncryptSensitiveWithPassword" or "EncryptAllWithPassword". This both go in pair. If you set the encryption level and not setting the package password it will throw an error.

Solution:
1. While setting the connection details, we should not check the Save Password option and package encryption can be default. As a best practice, we can set the package configurations through config file.

Reference for package configurations in msdn site

Prerequisite:
1. Make sure you have the latest version of BIDS.
2. Update the SQL server with all available service packs.
3. While setting the connection string using config file, please make sure you are setting the connection provider also.

Business Intelligence Development Studio 2005 (BIDS)

I recently want to update visual studio 2005 with service pack. The moment i installed in my machine, my studio started behaving very funny. IDE is having repeated menu items with repeated sub menu. I tried repair the studio with reinstalling the service pack and the problem is still more worse. Menus got doubled than before. I tried setting the IDE using Import and Export setting and that does not help. All I need is BIDS and not full version of VS2005 as my projects are migrated to higher versions. So I uninstall the VS2005 from my machine. I uninstall Visual Studio Premier Partner Edition also (by mistake), Please do not uninstall that as it is required for BIDS.

I have SQL Server 2005 database services installed already in my machine, and I downloaded the latest version of service pack for Microsoft SQL Server 2005 (Service Pack 4 RTM) and installed in my machine. During the installation wizard there is a option to select the tool which you want to upgrade, select all and proceed.

Installation went well with one fix failed, and it opened the provisioning tool to set the access for my SQL server database engine. Once the installation is done, IDE started configuring on its own and it ready. I got this information in one of the site mentioned below. But still I want to post the full history to understand the history behind this.

Info I found in that site:

You should make sure that Visual Studio is still installed. If you didn’t previously have VS installed, the BI Dev Studio installation will install a VS shell called Visual Studio Premier Partner Edition. Look in Add or Remove Programs for an entry like this. If you don’t find any entry for Visual Studio go to the location for SQL Server setup and run .\Tools\Setup\vs_setup.exe. This will install the VS Shell. After this is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS

ForEach File Enumerator

It is a Container element in control flow group in the SSIS. The container iterats through the object collection it belongs to. The ForEach File Enumerator iterates all files and directories in a specifies directory. Let us wal through that with an example.

.Create a new package.
.Drag and Drop the Foreach Loop Container from the Toolbar.
.Right Click, Edit the Container Properties.
.In General tab, set the name of the container

.In Collection Tab, set the Enumerator to Foreach File Enumerator. Set the Folder path to the required path. the Files field can be used to filter the type of files. the file name retrieval format can be set. suppose if the specified contains sub folders and if we want to check inside those folders also then check the "Traverse subfolders" Check Box.
.In Variable Mappings tab, Create a new variable, for example "Var" with datatype as string.

also set the index to 0, as the collection manipulates with one object in a loop cycle.

.The Container is set and ready to use, let us check the container using script task.
.Place a Script Task inside the Foreach Loop Container.
.Right Click, Edit the task.
.Goto to Script tab, and set the ReadOnly Variable Field with the variable used in the Foreach Loop. here set to "Var".

.Click Design Script from the Script tab, a new VS window environment opens with pre set already.
.Type the content, as given below in the existing function

Public Sub Main()
MsgBox(Dts.Variables("User::Var").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

.Save it, close the environment and click OK in the container Property box.
.The sript task will be evaluvated on every loop cycle. the loop object is passed as a read only parameter for script task. the main functioin in the task displays the value stored in the variable. In this way we can do file or manipulation kind of task through the SSIS. when the package is runed, the files in the specfied folder are displayed as message box to the end user.

SSIS - For Loop Container

This works on the tradition counter logic, where it has 3 steps.
1. Intialization,
2. Incrementation,
3. Condition.
Let us see in detail, Stepwise...
. Drag and Drop a For Loop Container from the toolbox to the Control Flow Tab
. Select the Container, add a variable from the Variable box. Variable box can be obtained from the Menu SSIS->Variables. say variable name is "count" of data type int.
(When we select the container and create a variable the scope of the variable is set only for Container level, means the variable will not be available for other components outside the container.)
. Right Click on the container and select Edit from the Context Menu displayed.
. In the InitExpression, Enter @[User::count]=0. this assigns the value 0 to the user created variable count.
. In the Eval Expression, Enter @[User::Variable] <10. this sets the loop condition.
. In the Assign Expression, Enter @[User::Variable] = @[User::Variable]+1. this increments the value of the variable by 1 for every loop.
. Select Ok and close the Dialog.
. From the tool box, place a script task in the container.
. Right Click the script task and select edit from the context menu.
. Goto script tab in the left panel.
. Click Design Script button located in the right bottom of the dialog. this opens a vb code page with new studio environment with some code writen already.
. Place the code MsgBox("hai"), in the main subroutine, before the assignment of task result.
. save a close the environment.
. Click ok and close the script edit dialog.
. Right Click the container, select execute container from the context menu.
. this runs the For Loop Container, and the tasks inside it. it displays the Hai message box 10 times and stops the execution.

This example explains the feature of the For Loop Container in a simple, how ever we can use this as per our need in the package.

SSIS - Expession Builder

It is an Editor, which creates expression using the variables and In-Built functions. Normally this editor can be reached for all elements in the package. the button with "..." [3 dots] is clicked to open this editor.
The property of the elements are set directly by assiging values to them, or by setting a expression and the expression is evaluvated in the runtime to set the values.
Almost every control's editor will have a Expressions tab, which can be selected, the tab contains a Field group named Expressions, by selecting the triple dot button again, will opens a Property Expression Editor. this contains two columns. first column contains drop down list, from which a field name can be selected and the second column contains a triple dot button, which actually opens a Expression Builder.
Expression Builder Editor contains 5 blocks.
1. Variable - list of variables available within the scope is listed.
2. Built in functions - list of functions are organized in groups and listed.
3. Description - displays a description of built in function, which is currently selected[Read only]
4. Expression - variables and functions can be dragged and droped here or expression can be manually keyed in.
5. Evaluvated Value - the value of the expression will be displayed, when the evaluvate expression is clicked, which throws exception if the expression is not set correctly.

The Built In Functions block will have type casting symbols and also the supported operators for evaluvating the expression.
once the expression is created succesfully the dialog can be closed, and the expression is assigned to the field. we can assign expressions to any no of fields.

SSIS - Precedence Contraints

. It is link created between the Containers, executables, tasks or any control flow elements.
. For example, to view a Precedence Constarint Editor, create two script tasks.
. select a task, it displays a arrows pointing downwards, drag and drop the arrow to the second script task.
. a conector is created in between the tasks. it is called Precedence constaint.
. the default value set to the constraint is success.
. to view the editor, select the connector, right click and select edit from the context menu.
. a dialog box opens with fields, evaluvation operation, constaint and expression.
. the flow can set as per the condition defined here.
. the condition can be set to Constraint, with following values,
1. Success - allows to flow only if the previous task executed succesfully.
2. Failure - allows to flow only if the previous task fail to execute.
3. Completion - allows to flow, without considering the execute status of previous task.
. the condition can be set to Expression. the flow depends on the result of the expression.
. the condition can be set to Constaint or Expression, where both will be defined and flow will be decided by any one.
. the condition can be set to Constraint and Expression, where both will be defined and flow will proceed only if both are passed.
. Multiple Precedence Constraints may contain for a task, in that case the logical Or and And between the Precedence Constraints can also be set using the editor.

SSIS - Control Flow Items

Control Flow Items contains Containers and Tasks.
. Containers are Components which holds the tasks and the task will be executed as per the containers nature.
. Tasks are activity which performs a action.
. SSIS supports various type of tasks related to database, file system, process, web service, FTP, Xml, Sql jobs, message queues.
. Each task can be ordered, or aranged in sequence like one after the other or in parellel using Precedence Constraints.
. Container helps to execute repeative tasks, and also to make a group of task over package.

What is SSIS package?

1. SSIS package is a collection of connections, control flow elements, data flow elements, event handlers and variables.
2. SSIS package can be built using Visual Studio 2005 by design mode by dragging and droping controls or programatically by adding the referece of SSIS components.
3. SSIS package can be stored in File System, SQL Server, SSIS package store.
4. Package is a unit of work that is retrived, executed and saved, either manually or on scheduled intervals of time or on specific day and time.
5. It is vast and lots of features involved, reliable and configurable.
6. Other major objects used with package to support on runtime are configuration, Logging and Event Handlers.

Event Handlers : It is an work flow that runs in response to the events raised in package, task or container.
Configuration : It is a set of property values used by the package, it enables the package to be customized.
Logging : It is a collection if information about the package, that is collected when it runs.
Variables : Supports system variables & user defined variables. it can be used in expression, script and configuration. each variable have scope depends its creation.