DEPLOYING SSIS IN TASK SCHEDULER WITH BAT FILE FOR ORACLE CONNECTIVITY

Hi,

Few of the below Points have to be Considered while deploying a SSIS Package as a File Sytem in a Windows Scheduler using Batch File Command.

Pre-Requiste : Windows 7 , SQL SERVER 2008 R2 Setup File, Tested DTSX Package File With Configuration  Within the DTSX Project Properties, select the Debugging page, and change the 64BITRUNTIME to FALSE, default value is TRUE.

Assume Oracle - 11/10g runs in/as a 32-bit Runtime.

Possible cause of Errors Listed out here ! since when i Choose Oracle is my Destination

I have to Use the OLEDB as my Destination Object ! Note: I have used Default Provider

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/10/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

  

I need the Command Tool DTEXEC Utility for my Deployement
So i Started running the SQL server 2008 R2 Setup

When i tried to Install the Integration Services alone as show in the Figure ,

After Completing the Installtion when i try to deploy it
I GOT THE BELOW ERROR , because i have tried executing dtexec util using the Below path in my batch File

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Microsoft (R) SQL Server Execute Package UtilityVersion 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 5:50:44 AM

Progress: 2013-04-15 05:50:45.15
Source: Data Flow Task
Validating: 0% complete
End Progress
Error: 2013-04-15 05:50:45.15
Code: 0xC0209303
Source: Package Connection manager "Oracle_Source"

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Started: 5:50:44 AM
Finished: 5:50:45 AM
Elapsed: 0.25 seconds


Now I understood the Error is Because of 32 bit installation   Again Now i Tried Installing the Mangement Tools - ALL using the R2 setup 

After the installation got over, i found the  C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ (32 Bit folder) has the DTEXEC utility copied on it

Now tried using the  below path in my batch file to execute it

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

It didnt stopped throughing the Erros, find the below LOG ERROR


This time  Error Log is different !!!


Microsoft (R) SQL Server Execute Package Utility


Version 10.50.1600.1 for 32-bit

Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 3:37:15 AM

Progress: 2013-04-12 03:37:16.18

Source: Data Flow Task

Validating: 0% complete

End Progress

Error: 2013-04-12 03:37:16.20

Code: 0xC0202009

Source: Package Connection manager "Oracle_Source"

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.".

End Error

Error: 2013-04-12 03:37:16.20

Code: 0xC020801C

Source: Data Flow Task OLE DB Source [668]

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle_Source" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

End Error

Error: 2013-04-12 03:37:16.20

Code: 0xC0047017

Source: Data Flow Task SSIS.Pipeline

Description: component "OLE DB Source" (668) failed validation and returned error code 0xC020801C.

End Error
 
Clearly Indicates from the Error that the OLEDB Provider is not instllaed for the Oracle Connectivity in the Server

DownLoad the 32-bit Oracle Data Access Components (ODAC) from Oracle.com Downloads
ODAC1120320Xcopy_32bit.zip

Follow the installation Instructions Thats it ..........

you are now ready to Deploy DTSX Package in the Live Server with the minimal Pre-requiste installed on it

Find below the Create Task Scheduler Steps
http://geekepisodes.com/sqlbi/2010/schedule-ssis-package-execution-with-windows-task-scheduler/
Batch file command for windows Scheduler follows

@echo off

FOR /F "tokens=2-4 delims=/ " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k

C:

CD C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

dtexec.exe /FILE C:\DashBoard\SSIS_Setup\Package.dtsx /DECRYPT password /ConfigFile C:\DashBoard\SSIS_Setup\DashBoardConfig.dtsConfig /CHECKPOINTING OFF > C:\DashBoard\SSIS_Setup\Logs\%DATE%_PSStageLog.txt

if ERRORLEVEL 0 SET ERRORLEV=0

if ERRORLEVEL 1 SET ERRORLEV=1

echo ERRORLEVEL = %ERRORLEV%

exit /B %ERRORLEV%

echo PS Stage Complete


 
Thanks for this blogger

http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/when-is-dtexec-installed

Using Entities in IList

BL Method which return Collective List objects
///

/// To Fetch the User Name & Code from DB
///

///
public List> GetLOBStationAccountUser(LOBStationAccountMap objLOBStationAccountMap)
{
List> lstCusomterALL = null; List lstSelectedAccount = null; List lstAvailablAccount = null; List lstCustomerAccount = null; SqlParameter[] ObjSqlParam = new SqlParameter[3]; string connString = SqlHelper.GetConnectionString(); //SqlConnection con = null; SqlDataReader dr = null; try { ObjSqlParam[0] = new SqlParameter("@ProductLobID", objLOBStationAccountMap.ProductLobID); ObjSqlParam[1] = new SqlParameter("@StationID", objLOBStationAccountMap.StationID == 0 ? System.Data.SqlTypes.SqlInt32.Null : objLOBStationAccountMap.StationID); ObjSqlParam[2] = new SqlParameter("@CustAccntID", objLOBStationAccountMap.CustAcntIdArray.Trim().Equals(string.Empty)? System.Data.SqlTypes.SqlString.Null: objLOBStationAccountMap.CustAcntIdArray ); //con = new SqlConnection(connString); dr = SqlHelper.ExecuteReader(connString, CommandType.StoredProcedure, "[DBSP_IMP_Get_LOBStatnAccntUsrMap]", ObjSqlParam); lstAvailablAccount = new List(); if (dr.HasRows) { while (dr.Read()) { UserMaster objCustomerAccountMaster = new UserMaster(); objCustomerAccountMaster.UserID = Convert.ToInt32(dr["UserID"]); objCustomerAccountMaster.UserCodeName = Convert.ToString(dr["UserCodeName"]); objCustomerAccountMaster.UserName = Convert.ToString(dr["UserName"]); lstAvailablAccount.Add(objCustomerAccountMaster); } } lstSelectedAccount = new List(); if (dr.NextResult()) { while (dr.Read()) { UserMaster objCustomerAccountMaster = new UserMaster(); objCustomerAccountMaster.UserID = Convert.ToInt32(dr["UserID"]); objCustomerAccountMaster.UserCodeName = Convert.ToString(dr["UserCodeName"]); objCustomerAccountMaster.UserName = Convert.ToString(dr["UserName"]); lstSelectedAccount.Add(objCustomerAccountMaster); } } lstCustomerAccount = new List(); if (dr.NextResult()) { while (dr.Read()) { UserMaster objCustomerAccountMaster = new UserMaster(); objCustomerAccountMaster.UserID = Convert.ToInt32(dr["UserID"]); objCustomerAccountMaster.UsrCustomerAccountID = Convert.ToInt32(dr["CustomerAccountID"]); objCustomerAccountMaster.UsrLobStationAccountID = Convert.ToInt32(dr["LOBStationAccountID"]); lstCustomerAccount.Add(objCustomerAccountMaster); } } lstCusomterALL = new List>(); lstCusomterALL.Add(lstAvailablAccount); lstCusomterALL.Add(lstSelectedAccount); lstCusomterALL.Add(lstCustomerAccount); } catch (Exception ex) { throw ex; } finally { dr.Close(); dr.Dispose(); //con.Close(); }
return lstCusomterALL;
}

IList objects accessed in CS Page
List> lstUserAvail_Selected;
lstUserAvail_Selected = objImportsConfigurationBL.GetLOBStationAccountUser(objLOBStationAccountMap);
if (lstUserAvail_Selected != null) { if (lstUserAvail_Selected.Count > 0) { this.SetAvailExistUsers(lstUserAvail_Selected[0], lstAvailFrame); if (lstUserAvail_Selected[1].Count > 0) this.SetAvailExistUsers(lstUserAvail_Selected[1], lstSelectedFrame); //if ( lstUserAvail_Selected.Count > 1 && lstUserAvail_Selected[2].Count > 1) // this.SetMappedCustomerAccounts(lstUserAvail_Selected[2]); } }

Javascript validation to Find Which Row of GridView

CS PAGE Coding
protected void grdBatchCreation_OnRowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { TextBox txtPageFrom = e.Row.FindControl("txtFrom") as TextBox; TextBox txtPageTo = e.Row.FindControl("txtTo") as TextBox; TextBox txtTotalPageNo = e.Row.FindControl("lblNoofCase") as TextBox; HiddenField txtRefVal = e.Row.FindControl("txtRefDOC") as HiddenField; CheckBox chkbx = e.Row.FindControl("chkgrd") as CheckBox; txtPageTo.Attributes.Add("onblur", "if (CheckValidPageNo('" + txtPageFrom.ClientID + "','" + txtPageTo.ClientID + "','" + txtRefVal.ClientID + "')) {return CalTotalPage('" + txtPageFrom.ClientID + "','" + txtPageTo.ClientID + "','" + txtTotalPageNo.ClientID + "'); }"); } }
JS To Find the Row
function whichRow(obj) { var par = obj.parentNode; while (par.nodeName.toLowerCase() != 'tr') { par = par.parentNode; } return (par.rowIndex); }
JS for GridView validation
function CheckValidPageNo(txtFrom, txtTo, txtRefVal) { //debugger; ///alert(txtFrom); var grdElement = document.getElementById('<%=grdBatchCreation.ClientID%>'); var objFrom = document.getElementById(txtFrom); var objTo = document.getElementById(txtTo); var CurrentRefObj = document.getElementById(txtRefVal); if (objFrom != null && CurrentRefObj != null && objTo != null) { if (trim(objFrom.value) != "" && trim(CurrentRefObj.value) != "" && trim(objTo.value) != "") { var CurrentFrmValue = (objFrom.value * 1); var CurrentToValue = (objTo.value * 1); var FromValue; var ToValue; var IsValid = true; var IsFromValid = true; var IsToValid = true; var CurrentRefValue; var RefValue; var idx = whichRow(objFrom); var rowsLength = grdElement.rows.length; for (var i = 1; i <= rowsLength - 1; i++) { var row = grdElement.rows[i]; //alert(row.cells[i].innerText); //alert(row.cells[i].innerHTML); if (row.cells[1].childNodes[0].checked && i != idx) { CurrentRefValue = trim(CurrentRefObj.value); RefValue = trim(row.cells[6].childNodes[0].value); if (RefValue.toLowerCase() == CurrentRefValue.toLowerCase()) { if (row.cells[4].childNodes[0].value != '') FromValue = (row.cells[4].childNodes[0].value * 1); if (row.cells[5].childNodes[0].value != '') ToValue = (row.cells[5].childNodes[0].value * 1); // 4 5 4 10 if (CurrentFrmValue >= FromValue && CurrentFrmValue <= ToValue) { //alert('From, To page values should be unique for same doc'); IsFromValid = false; } // 9 5 9 10 if (CurrentToValue >= FromValue && CurrentToValue <= ToValue) { //alert('From, To page values should be unique for same doc'); IsToValid = false; } if (CurrentFrmValue <= FromValue && CurrentToValue >= ToValue) { //alert('From, To page values should be unique for same doc'); IsToValid = false; } if (IsFromValid && IsToValid) { } else { showToolTip(2, 'From, To page values should be unique for same batch'); IsValid = false; //CurrentRefObj.value = ""; objTo.value = ""; objFrom.focus(); break; } } } // else if (i != 1) { // showToolTip(2, 'Please select before input From, To page values'); // row.cells[4].childNodes[0].value = ""; // row.cells[5].childNodes[0].value = ""; // row.cells[1].childNodes[0].focus(); // break; // }//SELECT } //for return IsValid; } } }

Convert String to DateTime Value in SQL 2005

Try this for string to DateTime
BEGIN
Declare @DateTimeValue varchar(20)
SET @DateTimeValue = 'Sam20101201121212'

Declare @Name varchar(3), @DateValue char(8) , @TimeValue char(6)
SELECT @Name = substring(@DateTimeValue,1,3)


SELECT @DateValue = substring(@DateTimeValue,4,8)
SELECT @DateValue

SELECT @TimeValue = substring(@DateTimeValue,12, len(@DateTimeValue) - 1)
SELECT @TimeValue

SELECT @DateTimeValue = convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, len(@TimeValue) - 1)

SELECT @DateTimeValue


END

Renaming Master Page Inherited Page Control Names Using VB on Render Event

Imports System.IO

Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
Dim _html As New StringWriter()
Dim _render As New HtmlTextWriter(_html)
MyBase.Render(_render)
Dim outPut, result As String
If (_html.ToString().Contains("input type=""hidden""")) Then ' Add < 'before ' input word
outPut = _html.ToString().Replace("name=""ctl00$PageMainContentHldr$", "name=""").Replace("id=""ctl00_PageMainContentHldr_", "id=""")
result = outPut.Replace("name=""btnSubmit", "name=""ctl00$PageMainContentHldr$btnSubmit""").Replace("id=""btnSubmit", "id=""ctl00_PageMainContentHldr_btnSubmit""")
writer.Write(result)
End If
'btnSubmit.RenderControl(writer)
End Sub

MCTS, MCPD Dumbs

Hi Dudes,
Here am posting URL where u can downlaod all the Latest Dumps for Microsoft MCTS, MCPD Exams

http://www.4shared.com/network/search.jsp?sortType=2&sortOrder=1&sortmode=2&searchName=70-547&searchmode=2&searchName=70-547&searchDescription=&searchExtention=&sizeCriteria=atleast&sizevalue=10&start=0



http://www.4shared.com/network/search.jsp?sortType=4&sortOrder=-1&sortmode=3&searchName=Framework&searchmode=3&searchName=Framework&searchDescription=&searchExtention=&specifySize=true&sizeCriteria=atleast&sizevalue=2&start=40


http://decrypters.org/index.php?name=Downloads&req=viewdownload&cid=5&orderby=dateA


"ALL THE BEST"



ReGards,
Suresh

Error Code - 12031

HI Dudes,
Got Screwed UP eah ? Here is the solution!

Here am Posting the URL for the Error Code - 12031 (have eaten my brain)
and its description as Follows

Sys.WebForms.PageRequestManagerServerErrorException:
An unknown error occurred while processing the request on the server.
The status code returned from the server was: 12031

I almost struggled more than 7hrs of search in Web, atlast i found the Solution here
http://siderite.blogspot.com/2007/08/aspnet-connection-to-server-has-been.html

**********************
Yesterday I was trying desperately to understand why my web site was crashing without any error, the only information I could get being that the connection to the server has been reset. I've spent hours trying to determine what was wrong. Apparently I needed a break, because today it took me a few minutes to realize what it was.

First of all, duh! If there are issues with the connection server, look into the Windows Application Event Log. But we'll get there.

The "error" appeared at any postback after I loaded a certain page, but only if that page displayed a minimum of data. Above that threshold I would get the server reset thing that you can see both in IE7 and FireFox2 in the animated GIF. Basically the error messages were:
FireFox
The connection was reset
The connection to the server was reset while the page was loading.
Internet Explorer
Internet Explorer cannot display the webpage
Internet connectivity has been lost.
The website is temporarily unavailable.
The Domain Name Server (DNS) is not reachable.
Ajax UpdatePanel
Server returned error 12031

So, today I realised I should look in the Application Event Log and this Web Event Warning was displayed (shortened it a bit):
Event code: 3004
Event message: Post size exceeded allowed limits.

Process information:
Process name: aspnet_wp.exe

Exception information:
Exception type: HttpException
Exception message: Maximum request length exceeded.

Stack trace: at System.Web.HttpRequest.GetEntireRawContent()
at System.Web.HttpRequest.FillInFormCollection()
at System.Web.HttpRequest.get_Form()
at System.Web.HttpRequest.get_HasForm()
at System.Web.UI.Page.GetCollectionBasedOnMethod(Boolean dontReturnNull)
at System.Web.UI.Page.DeterminePostBackMode()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

It turns out I was putting a lot of data into the ViewState, which, as you know, is saved as a HiddenField (a.k.a. hidden html input) and the size of it exceeded the set up maximum POST size.

Solutions:
A. Add this code to your page: (NET 2.0)

protected override PageStatePersister PageStatePersister
{
get
{
//return base.PageStatePersister;
return new SessionPageStatePersister(this);
}
}



This should put your ViewState into the Session, rather than in the page. This solves some other issues as well, obviously.

B. Increase the maximum Request limit (default is 4Mb)
- In the Machine.config file, change the maxRequestLength attribute of the configuration section to a larger value. This change affects the whole computer.
- In the Web.config file, override the value of maxRequestLength for the application. For example, the following entry in Web.config allows files that are less than or equal to 8 megabytes (MB) to be uploaded:


This is an exact quote from the Microsoft support page.

That's it, folks!

Update:

The maxRequestLength maximum value is 2097151, that is less than 2.1Gb. No file that exceeds this size can be uploaded through the default upload mechanism.
**********************

Kudos to Siderite