SET DICT LIST TUPLE SLICING in Python

set
---------

a = set(('10','20','30','40'))
b = set(('30','60'))
u=a.union(b)
i=a.intersection(b)
d=a.difference(b)
sd=a.symmetric_difference(b)
print('u = ',u)
print('i = ',i)
print('d = ',d)
print('sd = ',sd)

dict
---------

d1 = dict()
print(d1)
d2 = dict(p='play',t='talk')
print(d2)
d2['v'] = 'vibe'
d2['d'] = 'docs'
print(d2)
del(d2['v'])
print(d2)

list
-------

emplist1 = list()
print(emplist1)
emplist1.append([9,10])
print(emplist1)
emplist2 = []
print (emplist2)
emplist2 = ['a', 'b', 'c']
print (emplist2)
e=emplist2.pop()
print (emplist2)
print(e)



Tuple
----------

tup1=tuple()
print(tup1)
tup2=tuple('Welcome')
print(tup2)
print('e' in tup2)
print(tup2.count('e'))
print(tup2.index('e'))
print(len(tup2))

Slicing
-------------

k = [3, 4, 5, 6, 7, 8, 9]
print(k[::2])
print(k[0:4])
print(k[1:10:2])

Generate Positive Infinity and negative Values using Python

We can generate the Infinity values using Python. We need import math library to do this.


import math
x=math.inf

y=-math.inf

you need to keep - before to generate negative infinity

print(x)

The above prints positive infinity

print(y)

Above one print negative infinity

print(math.isinf(x))

prints true

print(math.isinf(y))

print true

String functions in Python

x=6
y=12

print('x==y =',x==y)

This prints false

print('x!=y =',x!=y)

True

print('x>=y =',x>=y)

False

print('x>y =',x>y)

False

print('x<=y =',x<=y)

True
print('x
True


s1='Python\nis\namazing'

Print(s1)

This one prints


Python
is
Amazing

rs1=s1.encode('unicode_escape').decode()
print(rs1)

This one prints Python\nis\namazing


s2='Python\tis\tamazing'

This one prints

 Python    is    amazing

rs2=s2.encode('unicode_escape').decode()
print(rs2)

This one prints

Python\tis\tamazing

s='INfinity'
print(len(s))
print((s).isalpha())
print((s).isdigit())
print((s).upper())
print((s).lower())
print((s).count('i'))
print((s).index('t'))

print('Infinity'.startswith("I"))  -- return true
print('Infinity'.startswith("i")) --false
print('Infinity'.endswith('y'))
print('Infinity'.endswith('Y'))

print(min(30,20,10)) --min
print(max(30,20,10)) --max

print(('a,b,c').split(","))
print(','.join(['a','b','c']))


x=5
y=2
print(x+y)
print(x-y)
print(x*y)
print(x/y)
print(x**y)
print(x%y)

Print Count of vowels using While and For loop in Python

Using while loop:
--------------------

s='Python is a programming language'
count = 0
inc = len(s)
i = 0
while i < inc:
        if s[i] in ('aeiou'):
                count+=1
        else:
                count+=0
        i+=1
print(count)

Using for loop
-----------------

s='Python is a programming language'
count=0
for char in s:
        if char in ('aeiou'):
                count+=1
        else:
                count+=0
print(count)

Range function in Python

Usage of Range function Python
--------------------------------------

This will print sequence numbers from 0-4
0,1,2,3,4

for k1 in range(0,5):
        print(k1)

0 -- Start number
5 -- End number .But 5 will not be included. range print (n-1) as index start from zero.

Below one print number from 10 - 14

for k2 in range(10,15):
        print(k2)

Below one print number 10,12,14,16,18,20

for k3 in range(10,21,2):
        print(k3)
for k4 in range(100,0,-25):
        print(k4)

10 -- starting number
21 -- ending number
2 - step sequence


Below one print the values in list

k1 = list(range(0, 5))
k2 = list(range(10,15))
k3 = list(range(10,21,2))
k4 = list(range(100,0,-25))
print(k1)
print(k2)
print(k3)
print(k4)

list is function which will store the value in a list in sequence.

Install Nginx and PostgreSQL using Ansible Playbook

Below Script will Install the NGiNX and PostgreSQL in linux ubuntu.

1) Copy the below code in ansible home directory using vi test.yml

---
- name: Install nginx
  hosts: all

  become: true

  tasks:
  - shell: apt-get upgrade -y; apt-get update

  - name: Install nginx
    apt:
      name: nginx
      state: latest

  - name: Start NGiNX
    service:
      name: nginx
      state: started

  - name: Install PostgreSQL
    apt:
      name: postgresql
      state: latest

  - name: Start PostgreSQL
    service:
      name: postgresql
      state: started
...

2) Save using esc button + :wq!

3) Run the command 

ansible-playbook -i myhosts test.yml

The output will look like this

Linked Report (Drill through ) Reports in SSRS

A Linked report or Drill through report is a report which will open the another report to get the detail report. In order to show this I have created two reports.

1) StudentExamStatus
2)StudentExamStatusDetail

The report StudentExamStatus show the report of students whether they have passed or failed. The second report StudentExamStatusDetail shows the particular student information with the score they have achieved in each exam.

First Report Query text

create table #studentmarks (studentid int identity(1,1),studentname varchar(50),subjectname varchar(250),marks smallint,Ispassed Char(1))

insert into #studentmarks values ('A','Social',80,'P'),

                                                       ('A','Science',50,'P'),

                                                       ('A','English',90,'P'),

                                                       ('B','Social',30,'F'),

                                                       ('B','Science',50,'P'),

                                                       ('B','English',90,'P'),

                                                       ('C','Social',80,'P'),

                                                       ('C','Science',70,'P'),

                                                       ('C','English',90,'P')

select studentname,

(select top 1 (case  IsPassed when 'P' Then 'Passed'

                           WHEN 'F' then 'Failed' else null end) from #studentmarks s1 where s1.studentname = s.studentname order by Ispassed asc) AS ExamStatus

from #studentmarks s

group by studentname

drop table #studentmarks

Create a Data Source.

Use the Data Source as Shared Data Source so that all the data sets can use the same connection string.

Create a Data Set for the first report as shown below.

StudentExamStatus Report Design

The SSRS 

The Second Report StudentExamStatusDetails DataSet

Working with Indicators in SSRS

How to use Indicators in SSRS :

Indicators in SSRS are useful to show the report in symbolic representation of the Status/Progress of the Fields in the report. By using the Indicators its easy to understand the report. It gives rich interface to the report.

The below example shows how to use indicators in SSRS reports.

Go To Report in Data Tools

Add New Item. Select Report and Click Ok.

Go to Tool Box and Drag and Drop the table.Add 3 columns. Name,marks, Scrore/Grade. 

Create Data Source and Create Data Set using the Report data .

create table #studentmarks (id int identity ,name varchar(50), marks int );

insert into #studentmarks values ('malli',80),('naga',60),('eswar',30),('reddy',50)

select name , marks from #studentmarks

drop table #studentmarks

The above sample query gives output of studentmarks.

Place the Indicator in the Pass/ Grade  text box as shown below. There are 4 different Indicator types as shown below. 

• Directional : Showing Directions
• Symbols : Showing the Pass/Fail Status ,Correct/Incorrect
• Shapes : Showing different shapes triangle/circle etc
• Ratings : Showing rating using different icons stars/bar/charts

Select rating tab. Click Ok.

Right Click the Score Grade text box (Star symbol area) as shown below.

Click on Indicator properties as show below. Go to Value and States tab as shown below.

By default there are two measurement units available for indicators

1) Numberic
2)Percentage 

Here in the below example I have used the numeric to show the indicators

Value drop down can be use the sum and count of integer column fields. To change the expression of the value column click of fx. Select Fields (dataset) Category and select marks field.

Click ok. Then save the indicator properties and run the reports. The Out put of Report UI can been seen below.

Working with For Loop Container in SSIS

Its is easy to use For Loop Container in SSIS. For Loop Container mainly used for execution of certain task for specified number of times that match the condition.We can use the for loop for updating big table and split updated in batches.

Here I have Shown a simple For Loop Task . Its prints from 0-9.

We declared three variables  as shown below. 

Start for Initial position .

Inc for Increment

End for condition satisfying until.

Double Click on ForLoop Container to Configure the For Loop Properties.

Set the properties as shown below.

In order to check whether the For Loop is Working Fine or Not I have used the Script Task to show the message box pop up. Script task is very useful to implement the custom functionality as well as useful for debugging.

Set the ReadOnlyVariables for the variable Start  to show that variables getting incremented by the for each loop logic.

 Click on Edit Script. 

Write the below code in main. 

MessageBox.Show(Dts.Variables[0].Value.ToString()

Save the script and Close the Editor. Build and Run the SSIS Package. Message Box prints from 0 to 9.

Create Subscriptions in SSRS (SQL Server Reporting Services)

How to Create Subscription in SSRS 

The User Interface has been changed Slightly in SQL Server for SSRS report manager.

The old Interface in Report manager to create reports. When Click on report it will show the settings in tabular mode. By default it will show report view mode.

In SQL Server 2012 Microsoft has changed the user interface of report manager for creating subscriptions. This is

Click on Subscribe.  It will Report Delivery options.  

The Different options under Delivery types are 

By default E-mail will be selected. Select E-mail , T0 , CC,Subject. Select the Check Box include report so that report will be send as attachment . There are different rendering formats for sending the report as attachment.

These settings can be done in RSReporServer config file. The RSreport server generally stored in the similar path given below.

C:\Program Files\Microsoft SQL Server\ReportServerName\Reporting Services\ReportServer\RsReportServer.config

Click on schedule to schedule the report to run a specific time.

Select Schedule of report (daily,weekly etc). Select the start date of the report that need to schedule.

The prerequisites for creating SSRS Subscriptions are

1) Create SQL Login and  map the login ReportServer Database. ReportServer Database will be created when SSRS services installed.  

2) In Report Manager Go to Data source. Click on Data Source as shown below.

3) Select the option Credential stored securely in the report server and provide SQL username and password. Click on Test connection to test the connectivity and Click on Apply.

Loop through Excel Files using SSIS ForEachLoop Task

Load the Multiple Excel files data in a folder to SQL Server using SSIS ForEachLoop Task:

Using SSIS ForEach Loop Task it is easy to Copy multiple excel in SQL Server.

Suppose you need to load multiple excel files from a folder with same structure in all excel files into SQL Server. Let Say excel contain daily data.

To do this we require Two Tasks. One Data flow task and One For EachLoop Container.

The we have to create an Excel Connection and SQL Server Database Connection for source and destination.

We need to create the below variables for storing the File Path and File name. Actually we require only 2 variables. One is for storing the File Directory and another is for storing the 

File Name that will pick up by the ForEach Loop Container.

In Excel Connection Manager Properties.  Excel Connection creation can be implemented by using the 

Go  to Expression Field.

Double Click the Expression . Select ExcelFilePath Property and Add the expression
as mentioned below.

Set  ServerName Propery to Some hard coded excel file which exist so that it will not fail while running.

Other wise it will throw the below error.

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "Excel Connection Manager 1"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Failure creating file.".

Also use the both servername and ExcelFilePath properties instead of ConnectionString property .Other wise you will get the below error. It always better to use ExcelFilePath property for the excel connection.

[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error: The result of the expression "@[User::FilePath]+  "\\"  + @[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

 Once the Excel Connection Manager Configured then configure Foreach Loop.

 In the ForEach Loop Editor . Select Collection tab.

Go to Expressions. Set the Directory Property to FilePath and FileNameRetrieval to FileName.

The Check box Traverse Sub folders can be checked if need to check sub folders in directory.
Then Go to Variable Mappings . Map the variables to index.

The Index of FilePath should be zero and FileNamr should be one. It should follow the same order as mentioned in the Expression.

Other wise you may get the below error while executing the package.

Logging in SSIS (SQL Server Integration Services)

How to Log Errors/Warning in SSIS

Its easy to log execution details, errors,warning in SSIS. In order to log execution events.

Go to SSIS Menu

Click on Logging as shown in the above.

There are multiple options to log the events in SSIS. Different Options are

1) SSIS Log Provider for SQL Server Profiler : Log Events will be saved in trace files. The Log can be opened using SQL Server profiler. Later it can be saved  into SQL Server database using the method fn_trace_gettable

2) SSIS Log Provider for XML files : Log Events will be saved in XML Files. This XML file we can save into SQL Server later using the Rowset functions (OPENXML).

3) SSIS Log provider for SQL Server : Log Events will be saved in SQL Server. SQL Server creates an internal table.

The log details will be stored in sysssislog table. The message column stores the error message.

4) Sqlserver log provider for Windows Event Log : Log Events will be saved in Windows Event Log.

5)Sqlserver log provider for Text files : Log events will be saved in text files.

There are two tabs to configure the logs in ssis.

1) Providers and Logs :

In this we can select the log providers to save the log events include errors,warnings.

2) Details : In this tab we can select list of events that required for logging.

Export CSV File Data into SQL Server using SSIS

SSIS Support importing CSV(Comma Separated Values) files into SQL Server. Using the Data flow task we can achieve this. 



Drag the Data Flow in Control Panel. Double Click on Data Flow Task. It will go to Data Flow task tab.

Now in Data Flow Task.  Right Click ----Select  SSIS ToolBox---Select Source Assistant

 Click New. Select Flat File as Source Type and Click New.

 Select Format  as delimited. Select the check box Column names in the first data row if the csv file contains the columns names in the first row. For changing Column names ,Data Type and OutputColumnWidth select the Columns tab as shown below.

 Run the Package as administrator by pressing F5 or Green button.

Some time you may get error as below.

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Folder Path" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Flat File Source [2]] Error: The "Flat File Source.Outputs[Flat File Source Output].Columns[Folder Path]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[Folder Path]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

The above error can be solved by changing the outputcolumnwidth in Flat File Connection Manager Editor or using the below steps. 

Right Click on the Flat File Source ---- Click on Show Advanced Editor

Select Input and output Properties tab.

Expand the Flat File Source Output . Expand the Output Columns .

Select respective column and Go to Data Type Properties section then change the Length.

Build and run the project. The data export from CSV to SQL Server Destination.