SurendraThotaMSBIProfessional

when we are trying to insert Null value into Not Null column then we will get this error .  Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.". An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'ID', table 'DbM2MReporting.M2M_Schema.XML_Quantity'; column does not allow nulls. INSERT fails.". Another Possibilty for this type of error is inserting Null value to Identity Column . when we Check the Keep Identity  property in OLEDB Destination Task then it will get the Same source Identity value and by mistake if you make null in column mapping when Keep identity property is ON then you will get this error . So kindly tak

Here i want to explain you how to create a Log file if it is not exists and Delete Logfile after redention Period . There are 2 major steps in this Template 1) Create Log file If not  with the Name <<SSIS Package name>>_YYYY-MM.log 2)Delete The Log file which is Older that Redention Period To achive this we need 3 Parameters 1.SSIS_ErrorLog_Folder :  To indicate the Log file folder path 2.Log fileName :  To store the Logfile name . Here we want to craate File name which inculdes Cureent Year month  . we will keep log file on monthly basis and we have redention period of 2 months  Expression to Createa logfile is @[User::SSIS_ErrorLog_Folder] + @[System::PackageName]+ "_" + (DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)+ ".log" The result will look like \\SurendraThota\Dev\Logs\Package_2014-03.log 3.DeleteLogFileName : This variable is get the Logfile name of  last 2 months back 

If we want to append current Year and month to the string  for example to the File name if we want append the current year nad month this the Expression we need to Use (DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) we are Padding month number with zero to make it 2 digits . See the Below example

SSIS Error : Column '%s' Cannot Convert between unicode and non-unicode string datatypes. We will get this error when the destination Table column datatype is Nvarchar and  OutPut Column Data Type  in SSIS Transformation  is String[DT_STR] then you will get this error . We can solve this error by giving Correct datatype in this case ie  Unicode String [DT_WSTR] you can set the value as shown in below fig .

Convert Current UTC date to Epoch SELECT DATEDIFF ( s , '1970-01-01 00:00:00' , GETUTCDATE ()) SELECT DATEDIFF ( s , '19700101' , GETUTCDATE ()) Convert Epoch to dateTime select dateadd ( ss , epoch , '19700101' ) SELECT DATEADD ( s , epoch , '1970-01-01 00:00:00' ) Example select dateadd ( ss , 1394796484 , '19700101' ) SELECT DATEADD ( s , 1393345800 , '1970-01-01 00:00:00' )

If we want to split a string which is like Key=value format then apply the below query to get the Key and Value strings seperately . declare @inputData varchar ( Max ) = 'Customer Name=Surendra Thota' select SUBSTRING (left( @inputData , Len ( @inputData )), 0 , charindex ( '=' , @inputData )) as [Key] , SUBSTRING (left( @inputData , Len ( @inputData )),( charindex ( '=' , @inputData )+ 1 ), 40 ) as Value

Here i want to give a small demo to select Table columns dynamically in SSRS 2012 . First i have Created Test table and inserted some values  as shown below . In Report  Create a Parameters called SelectColumns  . In SelectColumns Parameter add the columns availble in table as shown below Create a Dataset Main  with the select query of all fileds in a table . Once data set is created use Expression to get the Query from query parameter Create  expression   on aviable values = "select " + Join(Parameters!SelectColumns.Value, "," )+ " from ODS_Schema.TestDynamic "  Create a table and select all columns in that table . and Update the column visibilty property of each column as  below =iif(count(Fields!a.Value)=0, true , false ) and do the same for all columns and see the result .

If someboday wants the query like  I need a query to get weekwise data for last month starting Last Saturday of the Month and previous 4 weeks from then if I run the report this month. Example I ran the Report in Feb-2014 then I need to get the past  weeks of data like  below for example when I execute report I Feb, Data for Last month Jan should be displayed like this week 1 (19/01 to 25/01) week 2 (12/01 to 18/01) week 3 (05/01 to 11/01) week 4 (29/01 to 04/01) Here is the Query Declare @SelectedDate datetime set @SelectedDate = getdate () select dateadd ( d ,- 6 , dates ) as weekstartdate , dates as weekEnddate from ( select dateadd ( day , number - 1 , DATEADD ( year , Year ( @SelectedDate )- 1900 , 0 )) as dates from master .. spt_values where type = 'p' and number between 1 and DATEDIFF ( day , DATEADD ( year , Year ( @SelectedDate )- 1900 , 0 ), DATEADD ( year , Year ( @SelectedDate )- 1900 + 1 , 0 )) ) Dt where DATENAME ( weekday ,

In some Cases we need validate /correct the input value .In SSRS when we want to enter MSISDN number   which is generally     11 digits (32NNNNNNNNN)   so we cannot give interger datatype . Due to this we need to take as text and Use is able to enter in different formats /styles . we need to use regex to get only required format . Workaround . Step 1 . create a Input parameter   (example @MSISDN) of type Text which allows multiple values Step2 : Create another Hidden Parameter to   MSIDN_RegEX   Where in default value use =System.Text.RegularExpressions.Regex.Replace(join(Parameters!MSISDN.Value, "," ), "[^0-9,]" , "" ) This RegEX will remove all characters Except “,” and replace with “” Step 3 : Create another Parameter called Query of Text datatype . In that Select Column1 ,…Columnn from Table where MSIDN in( @MSISDN_ ) Step 4 : Create a Dataset Main NOTE for getting fields Metadata run the query Select Column1 ,…C