SQL Server/SSIS/SSRS/ETL/Data warehouse/Power BI

Pankaj

*****.*******@*****.*** / 510-***-****

Professional Summary:

8+ years of strong IT experience in Microsoft SQL Server in administration, Coding, testing, development, Logical, physical design, Data Analysis, Performance tuning, production environment and Support and maintenance of business application as an MS SQL Server/ T-SQL /ETL/ SSIS/ SSRS Developer.

Strong experience in MS SQL Server 2017/2016/2014/2012/2008r2/2008 suite of products including SSIS, SSRS and SSAS.

Experience in Software Development Life Cycle, Agile Scrum, RDBMS, and Data Warehouse & Data Modeling concepts.

Strong experience in creating T-SQL objects, working with DDL/DML, performs most of the SQL Server Enterprise Manager and Management studio functionality using SQL Scripts and Batches.

Experience in Data Warehousing methodologies and concepts, including star schemas, snowflakes schemas,Physical/Logical Data Model, Data Migration, Kimball methodologies, ETL/SSIS processes, Data governance, and dimensional modeling.

Proficient in using T-SQL for developing complex Stored Procedures, Triggers, Tables, Views, User Functions, User profiles, Relational Database Models and Data Integrity, SQL joins, Derived table, Cursors, CTE and Query Writing for Data Analysis.

Creating Indexes, Indexed Views and backup/ restore in observing Business Rules and creating effective Functions and appropriate Triggers to assist efficient data manipulation and data consistency.

Highly experienced in Performance Tuning, Handling Indexes, and Query Optimization.

Involved in developing complex database object like Store Procedure, Functions, CTE and Triggers using PL/SQL.

Experienced in creating, configuring, and scheduling the Dynamic SSIS packages.

Expertise in Extraction, Transforming and Loading (ETL) data using SSIS creating mappings/workflows to extract data from SQL Server, DB2, Excel file, Teradata, Oracle other databases and Flat File sources and load into various Business Entities.

Extensive working experience of (ETL) in usage of SSIS Control flow elements, Data flow element and multiple transformations provided by SSIS such as Data Conversion, Conditional Split, Lookup, Merge Join, slowly changing Dimension, Derived Column, script component, ODBC, For Loop, For Each Loop, Execute SQL Task, Execute Package Task, Send Email Task, Script Task, Xml Task, File System Task, etc.

Experience in providing Error handling by using event handler, Logging, Checkpoint, Variable and expression, Package deployment in SSIS.

Working Knowledge of VB.net and visual C#.net.

Experience with file transfer protocols, including standard FTP Task (File Transfer Protocol) and SFTP.

Expert in designing complex reports like reports using Cascading parameters, drill down Reports, Drill-Through Reports, Parameterized Reports, Dashboard Report and Report Models and ad hoc reports using SQL Server Reporting Services (SSRS) based on client requirement.

Hands on experience in developing and modifying SSRS/ Power BI dashboard and reports.

Coordinating and tracking all projects for seamless releases using Project Management System JIRA.

Experience in creating Jobs, Alerts, SQL Mail Agent, Database Mail and Scheduled DTS and SSIS Packages.

Team oriented individual with a result driven attitude, strong interpersonal and Communication skills in effective information exchange and interaction with clients at all Levels of management.

Excellent communication and inter-personal skills, accustomed to work in a team Environment with tight schedules and capable of working efficiently under pressure.

Technical Skills:

Database: MS SQL Server 2017/2016/2014/2012/2008r2/2008, Oracle 11g Express, My SQL 6.0,

T-SQL, DB2, Teradata, SAP BO.

Programming My SQL, VB.Net, C#.Net, Java, C, PL/SQL, Oracle.

Language:

Database Tools: SQL Server Management Studio, Visual Studio, Toad 13.0.

Design Tools: MS Visio, Erwin 9.0/7.1, ER Studio, Kimball Methodologies.

BI Tools: SSRS, SSIS, SSAS, Report Builder, Crystal Report, Azure Data Factory (ADF), Azure DevOps, Power BI.

Platform &

Frameworks: Windows 10/7, Windows Server 2003/2008/2012R2, DOS, etc.

Other: Microsoft Project, Microsoft Office Suite, JIRA, TFS, GitHub, MS Access, Agile, Excel, Word.

Data Warehousing: OLAP, OLTP, ODS, Star/Snowflake Schema, Fact and Dimension Table.

Educational Qualification:

Master of Science in Computer Science, USA.

Bachelor of Computer Engineering, India.

Professional Summary:

Client: Swiss Re Group Feb 2020 – Present

Role: Sr. SQL Server/PL SQL/T-SQL/SSIS / SSRS/ ETL Developer

The Swiss Re Group is one of the world’s leading providers of reinsurance, insurance, and other forms of insurance – based risk transfer, working to make the world more resilient. Asset Management creates value for Swiss Re and its shareholders by managing the assets generated from the core insurance and reinsurance business. We drive sustainable performance by following a disciplined, long-term oriented investment approach and focusing on a high-quality investment portfolio. Where I was responsible for creating and maintaining SQL and PL/SQL objects for migrating SAP BO report to SSRS/PBIRS and, Responsible for creating and modifying SSIS packages.

Responsibilities:

Develop complex queries, PL/SQL, and T-SQL objects such as Stored Procedures with parameters, Views, Tables, Common Table Expression (CTE’s) and User Defined Function by using various Data Manipulation Language (DML) and Data Definition Language (DDL) to develop data model.

Extensively worked on SSRS and delivered complex reports from different data sources such as SQL and Oracle Database.

Develop Parameterized Report, Chart Report, Drill-Down, Drill-Through, Sub Report and Linked Report with thorough knowledge of report serving architecture. (Table, chart, and matrix report).

Create complex SSRS reports using Multiple Data Providers, Global Variables, Expressions, User Defined Objects and Charts and then perform quality checks on reports and exports to ensure exceptional quality.

Responsible for debugging and testing of reports in SQL Server Reporting Services (SSRS) and SAP Crystal reports based on requirement.

Extensive use of project tracking tool JIRA for reporting the Development/Modification Requests In order to Keep track of the projects.

Analyzing various existing SAP Business Objects reports and migrating them into SQL Server Reporting Services (SSRS) reports.

Analyzed SAP BO Report and data to create a SQL Server Reporting Services (SSRS) reports.

Modify existing crystal reports and to develop new reports by analyzing databases and data sources.

Used SQL objects extensively to create/modify crystal reports.

Deployed SSRS report to the server by using Azure DevOps server.

Worked on Git to maintain source code, packages, and reports to GitHub repositories.

Created pipeline in Azure DevOps for deployment.

Involved in using Continuous development, Continuous Integration (CI) and Continuous Deployment (CD) in runtime with GIT.

Create unit test result document of each reports.

Responsible for Extracting, Transferring, and loading (ETL) data from various source system like Flat/CSV file, Oracle, SQL server based on the requirement by using SSIS.

Designed packages in SQL Server Integrating Services (SSIS) with various complex transformations such as derived column, conditional split, lookup, execute SQL task, Send email task.

Implemented various SQL Server Integrating Services (SSIS) packages having different tasks and transformations and scheduled SSIS packages.

Created SQL Server Integrating Services (SSIS) package to load data from staging environment to data warehouse using Lookup, Fuzzy Lookup, Derived Columns, Condition Split, and Slowly Changing Dimension.

Modified the existing SSIS package to meet the changes in user's’ requirements.

Involved in writing C# code wherever scripting is necessary specially to manipulate the coming file.

Responsible for Deploying, Scheduling Jobs, and Maintaining SSIS packages.

Involved in creating and modifying Power BI dashboard.

Involved in developing custom calculated measures using DAX in Power BI to satisfy business needs.

Followed agile scrum methodology projects.

Participate in all agile daily stand-up meetings and sprint meeting and give the updates.

Working with offshore/onshore team members globally.

Environment: MS SQL Server 2016, Toad 13.0, T-SQL, Report Builder, SSRS, SSIS, SSAS, SAP Crystal Report, Power BI, Visual Studio 2017, JIRA, GitHub, C#, Excel, Windows 10.

Client: State of Iowa March 2019 – Jan 2020

Role: Sr. SQL Server/ T-SQL / SSIS/ SSRS / ETL Developer

The goal is employment for job candidates that meets the needs and interests of job candidates as well as the needs of business partners. IVRS is a part of the state-federal vocational rehabilitation system. Each state receives funding to operate a vocational rehabilitation program. I was responsible creating and maintaining SQL objects, SSRS reports and SSIS packages. Also, solely responsible for managing all activities from development to production.

Responsibilities:

Analyze the Business Requirement Document (BRD) to design a conceptual model to be implemented with T-SQL, SSRS and SSIS to develop a data model..

Involved in writing complex T-SQL programming to implement Stored Procedures, Common table expressions and Functions for different tasks.

Design, review, and create primary objects (views, indexes, tables etc.) based on logical design models, user requirements and physical constraints for data analysis.

Created and developed stored procedures to handle complex business rules, history data and audit analysis.

Work with business analyst, project manager and other functional units to identify business needs and discuss solution best suited for the problem statement.

Worked with integration services(SSIS) for transferring and reviewing data from heterogeneous sources like (Excel, SQL Server, flat file).

Created and maintained SSIS packages to validate, extract, transform and load data to a centralized SQL server using OLEDB providers from the existing diversified data sources.

Created SSIS package to load data from staging environment to data warehouse using Lookup, Fuzzy Lookup, Derived Columns, Condition Split, and Slowly Changing Dimension.

Involved in deploying SSIS packages into SSIS DB using Azure data factory (ADF).

Involved in creating pipelines to deploy SSIS packages using Azure data factory (ADF).

Involved in writing C# code in Script component and script task based on requirements.

Involve in developing, debugging, and testing of reports in SQL Server Reporting Services (SSRS).

Discover and correct critical bugs in the data migration process that led to in-correct data being reported to clients and government reporting using SSRS.

Develop Parameterized reports, Drill down, Drill through, Sub report, linked reports with thorough knowledge of report serving architecture. (Table, chart, and matrix report).

Create complex SSRS report using multiple data providers, global Variables, Expressions, user defined objects, charts, and synchronized queries.

Identify and work with parameters using complex expressions for parameterized reports in SSRS.

Develop complex Stored Procedures, Triggers, and SQL scripts for performing automation tasks.

Responsible for deploying T-SQL objects as well as reports to development, Staging and production environment.

Developed and optimized SQL database structures, Stored Procedures, Indexes, Views, and User Defined Functions (UDF), Common Table Expression (CTEs) to support SAP Crystal report, Power BI and SSRS report.

Created and maintained TFS projects branches to include sources code repositories.

Used TFS to maintain different versions of SSIS packages and SSRS reports.

Involved in building a Business Analysis Process Model using Visio.

Environment: MS SQL Server 2014, T-SQL, SSRS, SSIS, SSAS, Crystal Report, Power BI, Visual Studio 2015, Windows Server 2012, JIRA, ADF, TFS, C#, Visio, Excel, Windows 10.

Client: Harper College April 2018 – Feb 2019

Role: Sr. SQL Server/T-SQL/ SSIS/SSRS/ETL Developer

Harper college gain skills advance your career and begin your bachelor’s degree. Harper is a top college in Illinois and career training school. In Harper, I was responsible for creating and maintaining T-SQL objects and responsible for creating and maintaining SSRS reports, ETL process, and production support.

Responsibilities:

Modified Store Procedure and formulated basic coding and scripting to be utilized for reports.

Used Joins and Sub-queries for complex queries that involved multiple tables from different databases and identified relationship among them.

Analyzed data models, data relationships, and dependencies for greatest optimization.

Provided tech team support for data warehouse.

Identified issue and developed a procedure for correcting the problem which resulted in the improved quality of critical tables by eliminating the possibility of entering duplicate data in a Data Warehouse.

Responsible for data cleansing operation for data warehouse in staging environment and identified issue and fix them before entering data in a Data Warehouse.

Modified the existing SSIS package to meet the changes in user's’ requirements.

Used SSIS to create ETL packages to Validate, Extract, Transform and Load data to Data Warehouse and Data Mart Databases.

Created various SSIS packages and ETL processes based on the business requirement.

Scheduled Jobs for executing the stored SSIS packages which were developed to update the Database on Daily basis using SQL Server Agent.

Created and maintained various SSRS report for the business users.

Developed and delivered dynamic reporting solutions using MS SQL Server 2015 Reporting Services (SSRS).

Created various kind of SSRS reports such as parameterized report, sub report, Drill Down report, and Drill Through report for the business users.

Designed and developed stored procedures that accept parameters, queries, views, User-defined function, and CTE’s necessary to support SSRS and SAP Crystal report reports.

Created subscription in SSRS to deliver reports to a PDF or excel format.

Created and modified complex reports using SAP Crystal reports.

Created crystal reports with multiple drill down and sub reports.

Created new and modified old views, stored procedures and functions for financial and student tracking reports using MS SQL Server 2008 R2 database.

Involved in providing support to various MS Access applications.

Create team specific agile process flow in JIRA to move tasks from one activity to another.

Environment: MS SQL Server 2014/17, T-SQL, SSRS, SSIS, SSAS, SAP Crystal Report, Windows server 2012R2, Visual Studio 2015, JIRA, C#, Excel, TFS, Windows 10.

Client: Penn National Insurance Jan 2017- March 2018

Role: MS SQL Server/T-SQL/ETL/ SSIS/SSRS Developer

Penn National Insurance is an insurance company which sells property-casualty insurance such as business insurance, personal auto, homeowners, etc. In Penn National Insurance I was responsible for architect ETL process to provide feeds to downstream system, design and maintain ETL Process, Reports, Production support and Performance Tuning.

Responsibilities:

Worked with Software Development Life Cycle, RDBMS, and Data Warehouse & Data Modeling concepts.

Created Indexed Views, complex Stored Procedures, effective functions, and appropriate Triggers to facilitate efficient data manipulation and data consistency.

Worked with various tools like Index Tuning Wizard, database engine tuning advisor, SQL Profiler, and Windows Performance Monitor for Monitoring and Tuning Server Performance.

Created various SQL objects such as schema, tables, field definitions and mappings/Workflows, stored procedures, CTE, Triggers, views, and indexes/keys for data analysis.

Created reports which retrieve data using Stored Procedures that accept parameters.

Developed and optimized database structures, Stored Procedures, Indexes, Views, Triggers (DDL & DML), Cursors and User Defined Functions (UDF).

Created various SSRS reports like drill down, sub reports, parameterized, multi-valued and various adhoc reports through Report model creation.

Created complex SSRS reports using multiple data providers, Global Variables, Expressions, user defined Objects, aggregate aware objects, charts, and synchronized queries.

Developed ETL programs using to implement the business requirements.

Created SSIS Packages to extract data from Flat files, Excel, Teradata, OLTP system and PDF Systems.

Worked on data migration from Teradata to SQL.

Involved in performing incremental loads while transferring data from OLTP to enterprise data warehouse (EDW) using different data flow and control flow tasks in SSIS.

Responsible for implementation of data viewers, SSIS Logging, error configurations for error handling the packages.

Built the email QC system to analyze the accuracy of the data warehouse ETL process, data governance and generate the email to all the team members.

Designed and developed Data Marts by following Star Schema and Snowflake Schema Methodology.

Built the automated ETL system for the Group Practice data mapping/Workflows by utilizing the Lookup, slow changing dimension, and developed C#/ VB.NET in script task and script component in SSIS.

Performed transformations like Conditional Split, Sorting and included data about the environment using the Audit Transformation.

Provided assistance for optimization and performance tuning of SQL and stored procedures.

Environment: MS SQL Server 2014/16, T-SQL, SSRS, SSIS, SSAS, Windows server 2008, Visual Studio 2013, C#/, Teradata, JIRA, Windows 10.

Client: Horizon Blue Cross Blue Shield of New Jersey Feb 2016 – Dec 2016

Role: MS SQL Server/T-SQL/SSIS/SSRS Developer

Horizon Blue Cross Blue Shield of New Jersey offers affordable New Jersey healthcare and health insurance for individual’s families and employers. In Horizon NJ health I was responsible for creating and automating provider reports to show the network. I also created dashboards to show provider terminations and additions to the network.

Responsibilities:

Developed User Defined Functions, store procedure. Responsible for error handling in stored procedure, indexes to generate the distinct data based on the type of the customer base, wrote T-SQL queries.

Performed review of health claims’ payment accuracy for healthcare payer in the claims processing systems.

Designed and developed Logical Data Flow by applying complex transforms as per business rules for loading the extracted data into Enterprise Data warehouse (EDW).

Worked with the QA and Production Team for setting the Configurations for the SSIS packages for Test and Production Deployment.

Designed SSIS Packages in SQL Server 2012 to handle data migration (ETL Processes) from transaction System to Dimensional Model.

Responsible for implementation of data viewers, SSIS Logging, error configurations for error handling the packages.

Developed various Error Handling Strategies for the successful run of the build packages.

In-depth Knowledge in Relational and Dimensional Data modeling for creating Logical and Physical Design of Database and ER Diagrams using multiple data modeling tools like ERWIN 9.0.

Created dynamic reports using SSRS, generated report models using report builder for the end users.

Created various SSRS report like Dashboard report, Parameterized report, linked report, Sub report, Drill through report for business user.

Created subscription in SSRS to deliver reports to a shared file.

Deployed reports created report schedules. Managing and securing reports using SSRS.

Environment: MS SQL Server 2012, SSIS, SSRS, T-SQL, Microsoft Visual studio 2012, Windows server 2008, Windows7, SharePoint, MS Office 2010, ASP.NET 3.5, C#.

Client: First Republic Bank April 2015 – Jan 2016

Role: MS SQL Server /SSIS/SSRS Developer

First Republic and its subsidiaries offer private banking, business banking and private wealth management, including investment, trust, and brokerage services. At First Republic Bank I used to work on Mortgage data to creating a data mart for reporting purpose and doing ETL to load data into the data mart. I was responsible for creating ETL packages to pull data from different data sources and loading it into data mart.

Responsibilities

Created databases and schema objects including tables, indexes, and applied constraints, connected various applications to the database and written functions, stored procedures.

Wrote data retention process for reporting database to keep 60 days report history.

Used Joins, correlated and non-correlated sub-queries for complex business queries involving multiple tables & calculations from different databases.

Designed ETL packages to load data from multiple sources.

Created SSIS Packages for feed process to generate underlying data for the reports and to export cleaned data from Excel Spreadsheets, Text file and CSV files to data warehouse.

Created complex queries to automate data profiling process needed to define the structure of the prestaging and staging area.

Used SSRS to generate reports by passing parameters through URL Developed reports.

Created interactive reports with SSRS, using the report parameters, query parameters, Filters and Expressions.

Created SSIS Packages to perform filtering operations and to import the data on daily basis from the OLTP system to SQL server.

Created staging, processing, and QA environment on the server, and managed the daily job for the data processing across these environments.

Work closely with Business Management to prioritize business and information needs.

Optimized the entire reporting system performance by tuning the procedure queries and build indexes in the Reporting system.

Environment: MS SQL Server 2008R2, SSIS, SSRS, SSAS, T-SQL, Visual Studio 2008, MS Office 2007,C#/VB.NET, Windows 7.

Client: TD Bank Sep 2013 – Nov 2014

Role: MS SQL Server /SSIS/SSRS Developer

TD bank America’s most convenient bank provides full banking, lending & investment services to people & businesses. TD Bank and its subsidiaries offer customized private banking and wealth management services I was responsible for improving performance of T-SQL queries, extract huge volume of data from different sources and load into staging database after that I have created SSIS package, create daily summary report using SSRS.

Responsibilities:

Successfully Installed and configured MS SQL Server 2008r2 on the production server.

Optimized the performance of queries with modification in T-SQL queries, removed unnecessary columns, eliminated redundant and inconsistent data, normalized tables, established joins and created indexes wherever necessary.

Designed system including tables, Stored Procedures, Indexes, UDF and triggers.

Created Complex ETL packages using SSIS to extract data from multiple sources.

Implemented SSIS (ETL) to extract and transform data from Xml, Flat files/CSV, SQL Server 2008 instances and to load into staging and then to DW for further Data Analysis and Reporting by using multiple transformations provided by SSIS such as Data Conversion, Conditional Split, Bulk Insert, xml task, merge, and union all, lookup and merge join and derived column.

Involved in maintaining PL/SQL scripts and stored procedures.

Improved performance and stability of database systems.

Generated Complex reports like reports using Cascading parameters, Snapshot reports Drill- down Reports, Drill-Through Reports, Parameterized Reports and Report Models and ad hoc reports using SQL Server Reporting Services (SSRS) also based on Business Requirement Document Designed and created Report templates, bar graphs and pie charts based on the financial data.

Developed and rendered monthly, weekly, and Daily reports as per the requirement and gave roles, user access with respect to security in report manager.

Working experience in database backup, restore and point in time recovery.

Evaluated database performance and performed maintenance duties such as Tuning, Backup/Restore, panning and replication.

Environment: Microsoft SQL Server 2008r2, SSIS, SSRS, Microsoft Visual studio 2008, Windows server 2008, PL/SQL Windows 7.

Client: Yippee Technology Pvt Ltd Dec 2012 – Aug 2013

Role: MS SQL Developer

Yippee Technology is an IT company providing business solutions to their customers. Yippee Technology is also providing technical support to their clients to troubleshoot technical problems. I was responsible for maintaining customer data in SQL database and loading customer files into database tables using SSIS and creating SSRS reports on that data to show company’s performance on monthly and yearly basis.

Responsibilities:

Designed and analyzed the SQL Server database and involved in gathering the user requirements.

Logical and Physical design of database for project on basis of user requirements

Created new tables, stored procedures for Application Developers and some user defined functions.

Performed data conversions from flat files into a normalized database structure.

Created Triggers to enforce data and referential integrity.

Defined Check constraints, Business Rules, Indexes, and Views.

Extracted the data from Flat Files and Excel transformed (Implemented required Business Logic) and Loaded into the target Data warehouse.

Scheduled SQL Server Agent Jobs to run every day to update changes in the base tables.

Used complex query statements like sub queries, correlated queries, derived tables, CASE functions to insert the data depending on the criteria into the tables of hub.

Modified the existing database by adding the tables and maintained relationships between the tables.

Designed and created distributed reports in multiple formats such as Excel, PDF, XML, HTML, .doc and CSV using SQL Server Reporting Services.

Created report subscription using data driven subscriptions and setting up automatic delivery of user.

Environment: MS SQL Server 2008, Microsoft Visual studio 2008, Windows 2007, Erwin v7.0, Crystal Report.

Contact this candidate