Home Blog Page 7

Proxy Accounts to run SSIS Package from Sql Server Agent Jobs

By
roopesh.valluru
-
761

Friends,

Most of the times when you design a package, it works as expected in design environment but the same fails when you configure it in Job(Sql Server Agent). the reason for the above problem is you have connections that use Windows Authentication. At design time, the package uses your credentials, and when you schedule the package, it uses the SQL Server Agent service account by default. This Agent account may not have access to a file share or database server that is necessary to successfully run the package. The solution for that problem is using PROXY ACCOUNTS.

Let’s see the steps involved in creating Proxy Account.

  • Creating Credential
  • Assigning credential

Creating Credential :

  • To create a credential, Connect to SSMS and rightclick on Security folder and select  “Credential” as shown below.

 

  • Give some name and I have given “Test” in this case. The credential will allow users to temporarily gain administrator access. For the Identity property, type the name of an administrator account or an account with higher rights. In this case I have given my details as shown below.

 

  • Click on OK. A credential will be created with the name given.

Assigning credential : Now let’s see the steps involved in assigning a credential.

  • Go to Sql Server Agent Tree.
  • Right click on Proxies and select option “New Proxy” as shown below.

 

  • When the wizard opens,Type Test Proxy for the Proxy Name property, and Test(which we created in last step) as the Credential Name. Check SQL Server Integration Services Package for the subsystem type allowed to use this proxy.

 

  • Now click OK to save the proxy. Now you can use the proxy created to run SSIS packages by configuring to Sql Server Agent Jobs. You will be able to use the new proxy by selecting the Admin Access Proxy from the Run As drop-down box. Any connections that use Windows Authentication will then use the proxy account instead of the standard account.

That’s it .. Hope this solves your issue .. 🙂

Regards,
Roopesh Babu V

DTEXEC Utility’s Commands

By
roopesh.valluru
-
55

Friends,

As you all know there are many ways to execute a package  and one among those is using “dtexec”.  The syntax to use this command line utility is

dtexec /option [Value] /option [Value] …

A simple example is given below.

dtexec /File “c:\ExportFlatFile.dtsx”

In the above example you can see an option FILE used which means the Package is copied to the file path specified.

Dtexec utility can run SSIS packages from three sources.

  • File system using /File option
  • MS SQL database using /SQL option
  • SSIS Package store using /DTS option

PFB the different types of options available for DTEXEC utility.

dtexec Command Options
Option Description
-? [option_name ] Displays a list of options or a description for the specified option
-CheckF[ile] filespec Sets the CheckpointFileName property on the package to a specified path and file name
-CheckP[ointing] {on\off } Determines whether the package will use checkpoints during execution
-Com[mandFile] filespec Specifies a text file that contains additional dtexec command options
-Conf[igFile] filespec Specifies an XML configuration file containing settings that will be used to execute the package
-Conn[ection] id_or_name;connection_string [[;id_or_name;connection_string ]…] Supplies connection strings for the specified connection managers
-Cons[oleLog] [[displayoptions ];[list_options;src_name_or_guid ]…] Configures which log entries are displayed to the console during package execution
-D[ts] package_path Loads the specified package from the SSIS package store
-De[crypt] password Sets the decryption password for a password encrypted package
-Dump error code Specifies the type events that trigger the creation of dump files
-DumpOnError Specifies that dump file be created when any error occurs during package execution
-F[ile] filespec Loads the specified package from the file system
-H[elp] [option_name ] Displays a list of options or a description for the specified option
-L[ogger] classid_orprogid;configstring Associates one or more log providers with the package during execution
-M[axConcurrent] concurrent_executables Specifies the number of executable files that the package can run concurrently
-P[assword] password Specifies a password and is used with the -User option to access a package secured with SQL Server authentication
-Rem comment Specifies a comment; Does not affect the execution of the package
-Rep[orting] level [;event_guid_or_name[;event_guid_or_name[…]] Specifies the reporting level used during package execution
-Res[tart] {deny | force | ifPossible} Specifies a new value for the package’s CheckpointUsage property
-Set propertyPath;value Configures a new value for a variable, property, container, log provider, For each enumerator, or connection. This overrides the package’s design-time configuration for these items
-Ser[ver] server Specifies the name of the server from which to retrieve the package. Used only with the -DTS or -SQL options
-SQ[L] package_path Loads the specified package from a SQL Server
-Su[m] Displays a count of the rows to be received by the next component
-U[ser] user_name Specifies a user name and is used with the -Password option to access a package secured with SQL Server authentication
-Va[lidate] Stops the execution of the package after the validation phase
-VerifyB[uild] major [;minor [;build ]] Compares the build number of a package with the build numbers that were specified during the verification phase
-VerifyP[ackageID] packageID Compares the GUID of the package to be executed with the specified package ID
-VerifyS[igned] Forces Integration Services to verify the package’s digital signature
-VerifyV[ersionID] versionID Compares the version GUID of a package with the specified version ID
-VLog [Filespec ] Writes all Integration Services package events to the log providers that were enabled at design time
-W[arnAsError] Configures the package to treat all warnings as errors during validation
-X86 Configures the SQL Server Agent to run the package in 32-bit mode on a 64-bit computer. This option is only valid when dtexec is executed by a SQL Server Agent job

Hope this helps you guys .. Enjoy !!

Regards,
Roopesh Babu V

MSBI Training Part 19

By
roopesh.valluru
-
7

Friends,

This is the 19th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Partitions
  • Performace tuning

 

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

MSBI Training Part 18

By
roopesh.valluru
-
8

Friends,

This is the 18th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Many to Many Relationship
  • Types of Storage Modes
  • Adv and Disadvantages of different storage modes

 

Hope you understood the concepts and start browsing the next one ..

Regards,

Roopesh Babu V

MSBI Training Part 17

By
roopesh.valluru
-
881

Friends,

This is the 17th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Regular Relationship
  • Fact Relationship
  • Referenced Relationship
  • No Relationship

 

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

Derived Tables

By
roopesh.valluru
-
34

Friends,

As we all know that there are TWO types of Table Expressions and the same is provided below. Table Expressions are nothing but subqueries that are used where a table is expected.

  • Derived Tables
  • CTEs

In this post we are gonna discuss about “Derived Tables”. A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known.

Lets take a simple example to demonstrate the same. Imagine I have a table with the following columns.

  • Date – Which stores the date of sales
  • Sales – Which stores the sales amount.

Now my requirement is to see the sales MONTH wise and for achieving the same we need to use Group By clause as shown below.

select Month(Date) as Month_Name,Sum(Sales) as Sales from Table1
Group By Month_Name

If I execute the above query, it throws the following error.

Message 207: Level 16, State 1, Line 2
The invalid column ‘Month_Name‘

The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name of the column Month_Name is not known at the time the grouping is processed.By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause and you can see the modified query given below.

SELECT Month_Name,Sales
FROM (SELECT MONTH(Date) as enter_month,Sum(Sales) as Sales
FROM works_on) A
Group By Month_Name

In the above query, the processing of column MONTH(Date) is executed before it triggers GROUP BY clause and this is why the data will be grouped by MONTH and return the results.

Also remember that DERIVED Table can be added in any part of SELECT statement like in Columns list,Where or FROM. You can use these as REPLACEMENT of views also.

Regards,
Roopesh Babu V

Error converting data type varchar to Float.

By
roopesh.valluru
-
51

Friends,

Sometimes you need to convert the data of a column in SQL server table from VARCHAR(String) to FLOAT. When I was working on the similar one today i got an error saying that

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to Float.

PFB the code I have written  –

SELECT col1, CAST(col2 AS FLOAT) as col2 FROM #DaTable

I was getting this error because there is some data like NULLS and non numeric values which cannot be converted to FLOAT datatype. To handle this kind of error we have to use the CASE Statement. PFB the converted code which converts VARCHAR column to FLOAT column.

SELECT col1,  
	CASE WHEN ISNUMERIC(col2) = 1
		THEN CAST(col2 AS FLOAT) 
		ELSE NULL 
	END as col2
FROM #tableName

In the above example at first point I am checking whether the data is NUMERIC or not using ISNUMERIC() function and if the data is non numeric then I am replacing the value with NULL as I cannot convert it to FLOAT type and then casting the data ONLY when it is NUMERIC to FLOAT.
Hope this helps !!
Regards,
Roopesh Babu V

Log Providers in SSIS

By
roopesh.valluru
-
550

Friends,

SSIS include many default providers to log the information of SSIS package. Let’s see about each one in this post. We have the following types of LOG providers in SSIS.

  • SSIS Log Provider for Text Files
  • SSIS Log Provider for SQL Profiler
  • SSIS Log Provider for SQL Server
  • SSIS Log Provider for Windows Event Log
  • SSIS Log Provider for XML Files
You can see all there under SSIS Menu–> Logging wizard as shown below.

PFB the details of each Provider.

  • SSIS Log Provider for Text Files: This provider is used to store log information to a CSV file on the file system. This provider requires you to configure a File Connection object that defines the location of the file. Storing log information in a text file is the easiest way to persist a package’s execution. Text files are portable, and the CSV format is a simple-to-use industry-wide standard.
  • SSIS Log Provider for SQL Profiler: This provider produces a SQL Profiler trace file. The file must be specified with a trc file extension so that you can open it using the SQL Profiler diagnostic tool. Using SQL profiler trace files is an easy way for DBAs to view log information. Using Profiler, you could view the execution of the package step-by-step, even replaying the steps in a test environment.
  • SSIS Log Provider for SQL Server: This provider sends package log events to a table in the specified SQL Server database. The database is defined using an OLE DB Connection. The first time this package is executed, a table called sysdtslog100 will be created automatically. Storing log information in a SQL Server database inherits the benefits of persisting information in a relational database system. You could easily retrieve log information for analysis across multiple package executions.
  • SSIS Log Provider for Windows Event Log: This provider sends log information to the Application event store. The entries created will be under the Source name SQLISPackage. No additional configuration is required for this provider. Logging package execution to the Windows Event Log is possibly the easiest way to store log events. The Windows Event Log is easy to view and can be viewed remotely if required.
  • SSIS Log Provider for XML Files: This provider stores log information in a specified XML file on the file system. The file is specified through a File Connection object. Make sure you save the file with an xml file extension. Logging events to XML inherits the advantages of the XML specification. XML files are very portable across systems and can be validated against a Schema definition.

You can choose the provider of your wish. You can also choose more than one. It all depends on the requirements by the clients.

Regards,
Roopesh Babu V

Logging in SSIS

By
roopesh.valluru
-
484

Friends,

We all know how important LOGGING is for any data process projects. In this post we will see how the logging can be done in SSIS packages. The main purpose of logging is that it provides the complete information about the events occurred during the execution of the process. Capturing such information will be helpful in the following things –

  • Helps in identifying the errors.
  • Helps in identifying the bottlenecks for performance issues.
  • Helps in doing RCAs.

SSIS contains built-in logging features that capture execution details about your packages. Logging enables you to record information about events you are interested in as the package runs. The logging information of a package can be stored in the following locations –

  • Text file
  • XML file
  • Sql Server table
  • Windows event log
  • Sql Profiler

Logging can be enabled for all or some tasks and containers and for all or any events. Tasks and containers can inherit the settings from parent containers. Multiple logs can be set up, and a task or event can log to any or all logs configured. You also have the ability to control which pieces of information are recorded for any event.

Let’s see the steps involved in implementing logging for a package in SSIS.

  • Create a package with any couple of control flow tasks.
  • Go to SSIS menu on top and select LOGGING option which opens a wizard to configure logging as shown below.

  • To enable logging, you must first check the box next to the package name in the left pane. Notice that the checkboxes for the child objects in the package are grayed out. This means that they will inherit the logging properties of the package. You can click into any checkbox to uncheck an object. Clicking again to check the box will allow you to set up logging properties specific for that task or container.

  • Next, in the provider drop down, select any provider type of your wish and in this example I will go with “SSIS log provider for TEXT Files” (an easiest one to explain .. :)) and click on ADD.
  • Here you can see a log is added and you can edit the name,description if you wish. Check the check box on the left and click on CONFIGURATION to set the new connection as shown below.

  • Click the drop-down under Configuration and choose <New Connection>. Once the File Connection Manager Editor opens, set the Usage Type property to Create File. Type c:\SSIS_Log.txt as the path for the TEXT file or click Browse to the TEXT file location as shown below.

  • If you wish to select an EXISTING FILE then you have to select option “Existing File” for USAGE TYPE property in the above pic.
  • Click the Details tab to view a list of events that you can log. By clicking Advanced, you will also see a list of possible fields.
  • Here you can select all the events for which you wish to log the information. In this example I enabled logging for ONLY 3 e vents as shown below.

  • Click on OK and run the package. Once the package execution has completed, open the log file to view the logs. You can see the logs in the below given format.

This is it .. In this way you can log the information of the events occurring in ssis package. As discussed, we can log the information to many destinations. Try it !!

Regards,
Roopesh Babu V

MSBI Training Part 16

By
roopesh.valluru
-
4

Friends,

This is the 16th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Creating Empty Cube
  • Adding cube Dimensions
  • Adding Measure Groups
  • Relationships tab
  • Role Playing dimensions
  • Creating Cube using Existing tables option

 

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

MSBI Training Part 15

By
roopesh.valluru
-
2

Friends,

This is the 15th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Creating Dimensions
  • Adding Attributes
  • Deleting Attributes
  • Translations in Dimensions
  • Build
  • Deploy
  • Process

:)

Hope you understood the concepts and start browsing the next one .. ;)

Regards,

Roopesh Babu V

Computed Columns in Sql Server

By
roopesh.valluru
-
406

Friends,

In this post we are gonna look at Computed Columns in Sql Server. Sql server not only allows you to store data in columns of table but also allows you to construct a calculation and include it as a column in the table. The table will store ONLY the formulae you have given for computation. When ever you request for the column then sql server performs the required computation and returns the value. In short the calculations/computed columns are NOT PRECALCULATED. Let’s see how to create a computed column using sql scripts.

Take a new query window and execute the below given query.

CREATE TABLE Sales
(SalesID        INT         IDENTITY(1,1),
OrderDate       DATE        NOT NULL,
SubTotal        MONEY       NOT NULL,
TaxAmount       MONEY       NOT NULL,
ShippingAmount  MONEY       NOT NULL,
GrandTotal      AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate   DATE        NULL)
GO

Once the table is created then insert some rows into the table using insert command. REMEMBER that the calculated column will not take and value as input and if you pass value to calculated column also then it will error out.

Insert into sales values(…,..,..,.,..,…)

Once the data is inserted then select the data using the select command and see the data retreiving for the calculated column.

select * from Sales

This is how you can add a calculated column to the table while creating a table. now let’s us see how to add a Calculated column to an already existing columns. This can be acheived using ALTER command and PFB the example query of the same.

ALTER TABLE Sales
    ADD ProductMargin AS (SubTotal - TaxAmount)

That’s it .. Hope you understood the calculated column concept .. Enjoy coding !!

Regards,
Roopesh Babu V

MSBI Training Part 14

By
roopesh.valluru
-
9

Friends,

This is the 14th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Master and Transaction tables
  • Dimension and Fact tables
  • Schemas
  • Star Schema
  • Snow Flake Schema

:)

Hope you understood the concepts and start browsing the next one .. ;)

Regards,

Roopesh Babu V

How to enable Telnet Server and Telnet Client in Windows?

By
roopesh.valluru
-
149

Friends,

How to enable Telnet Client and Server ? Before going to that let’s talk a bit about what is TELNET and why we need it. If you are configuring DATABASE MAIL then you need to test the connectivity to the MAIL SERVER you use in the Database mail settings. To TEST the CONNECTIVITY to any mail server we use command prompt keyword TELNET. This  service needs to be enabled to make use of the same. If the TELNET is not enabled the you will get the following error.

“TELNET is not recognized as an internal or external command”

Now lets see the steps involved to enable the TELNET Client and Server.

  • Go to control panel
  • Select Programs (in windows 7) and Programs and features in earlier versions as shown below.

  • Select TURN WINDOWS FEATURES ON OR OFF option as shown below.

  • A popup will open and check the options Telnet Client and Telnet Server and click OK. This will take few minutes to enable the same.

  • Now go back and execute the same command to test the MAIL SERVER in command prompt. You can see the TELNET is recognized. Now you can provide correct mail server to test the connectivity.

Hope it helps you .. 🙂

Regards,
Roopesh Babu V

How to Configure Database Mail?

By
roopesh.valluru
-
29

Friends,

Lets discuss about the steps involved in configuring the Database  Mail in your database. PFB the steps involved.

  • First connect to SSMS (Sql Server Management Studio) and select Management folder in Object explorer.

  • Right click on “Database Mail” option and select “Configure Database Mail” option.

  • When the Welcome Wizard opens click NEXT.
  • Select the option “Select database mail by performing the following tasks” and click NEXT.

  • Provide Profile Name and Select ADD to add a new profile.

  • Here you need to provide all the SMTP settings as shown below.

  • Once the details are provided correctly then select OK and then you can see a new profile created. Check the same to set as default and click NEXT .

  • Continue clicking NEXT until you reach the last page and Click FINISH. You can see configuration status as shown below.

  • Now the configuration part is set. Now to configure EMail in DB, go to Query analyzer and execute the following code.

sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO

  • Once setup is done, we are ready to test our mails. PFB the code to be used to check the same.

USE msdb
GO
EXEC sp_send_dbmail @profile_name=’MailServer’,
@recipients=’something@gmail.com’,
@subject=’Test’,
@body=”This is to test Database Mail.

This is it. You can use the following queries to check the status of the mail sent.

SELECT * FROM sysmail_mailitems
SELECT * FROM sysmail_sentitems
SELECT * FROM sysmail_log

Hope this helps you .. Enjoy coding guys !!

Regards,
Roopesh Babu V

MSBI Training Part 13

By
roopesh.valluru
-
55

Friends,

This is the 13th video from the series of videos. In this video we are gonna discuss about the following topics –

  • DSV
  • New Named Query
  • New Named Calculation
  • REFRESH in DSV
  • Relationships

 

Hope you understood the concepts and start browsing the next one .. 😉

Regards,
Roopesh Babu V

MSBI Training Part 12

By
roopesh.valluru
-
1

Friends,

This is the 12th video from the series of videos. In this video we are gonna discuss about the following topics –

  • Services
  • BIDS
  • Data Sources
  • DSV (Data Source Views)

 

Hope you understood the concepts and start browsing the next one .. 😉

Regards,
Roopesh Babu V

MSBI Training Part 11

By
roopesh.valluru
-
61

Friends,

This is the 11th video from the series of videos. In this video we are gonna discuss about the following topics –

  • What is MSBI
  • How Teams works in MSBI projects
  • OLTP
  • OLAP
  • Database and Datawarehouse differences
  • SSAS

 

Hope you understood the concepts and start browsing the next one .. 😉

Regards,
Roopesh Babu V

MSBI Training Part 10

By
roopesh.valluru
-
10

Friends,

This is the tenth video from the series of videos. In this video we are gonna discuss about the following topics –

  • Numeric Functions
  • String Functions

 

Hope you understood the concepts and start browsing the next one .. 😉

Regards,
Roopesh Babu V

MSBI Training Part 9

By
roopesh.valluru
-
118

Friends,

This is the ninth video from the series of videos. In this video we are gonna discuss about the following topics –

  • Aggregatable Functions
  • Scalar Functions

 

Hope you understood the concepts and start browsing the next one .. 😉

Regards,
Roopesh Babu V

1...678...11Page 7 of 11
© MSBIGuide.com