Get dynamic file name using foreach loop container SSIS

When we are working with file in SSIS we need to set the file name dynamically. We can’t hard coded the file name. If in a folder we have multiple file and want to load all file to database in this case we need to get the file name. In this article we will learn how we get the dynamic file name.

Let’s see the example.

TestData folder I have some file. I want to get the file name dynamically.

Open SSDT.

For enumerator we need to take foreach loop container.

  

Double click on the foreach loop container.

Foreach loop editor will be open.

Go to collection tab.

Select foreach file enumerator.

Now we need to select the folder where stored

Write the file extension and select the retrieve file name. If you want to fetch the all file which is in sub folders the select Travers sub folder option.

Now we need to create a variable which store the file name.

Creating a variable name as filename which store the file name.

Now I am taking script task inside foreach loop container to display the file name.

Double click on the script task.

 

Select the variable name.

Click on the edit script button and write the C# script to display the file name.

Save and close it. Now package is ready to run. Now I am running the package.

Got the expected output.

Get the Size of all Tables in a database in sql server

Using below sql script we will get the size of the tables in a database.

CREATE TABLE #TableSize ( SEQ INT IDENTITY(1,1) , name NVARCHAR(200), rows BIGINT, reserved VARCHAR(100), data VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100) ) INSERT #TableSize EXEC sp_msForEachTable 'EXEC sp_spaceused "?"' Select * from #TableSize Drop table #TableSize

Running this sql script.

  

I have run it on Test database.

Now I am running on other database see the result.

 

Generate create scripts for Procedure, Views and Function in sql server

Using below sql script we will get the create script for procedure, view and function.

SELECT CASE WHEN o.type ='FN' Then 'Function'                  WHEN o.type ='P' Then 'Procedure'                  WHEN o.type ='V' Then 'View'                  END as 'create scripts ', m.definition FROM sys.sql_modules m INNER JOIN sys.Objects o ON m.Object_id = o.Object_id WHERE o.type IN('FN','P','V')

Executing this script

  

We can copy this script and run this query object will created

 

Get the list of Server Permissions and Role

With the help of below sql script we will get the list of sql server roles and permissions

Role Members

SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) +  QUOTENAME(usr2.name, '''') AS '--Role Memberships' FROM    sys.server_principals AS usr1         INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id         INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id ORDER BY rm.role_principal_id ASC;

See the list of roles

 
 Server Permissions

SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS         + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions' FROM    sys.server_permissions AS server_permissions WITH (NOLOCK)         INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id WHERE   server_principals.type IN ('S', 'U', 'G') ORDER BY server_principals.name ,         server_permissions.state_desc ,         server_permissions.permission_name;

 

Find Table in Every Database of SQL Server

Using below sql script we will get the table which contains the search string. Some time we are not known this table belongs from which database. With the help of below query we will get the list of database.

CREATE PROCEDURE SP_FindTableName @TableName VARCHAR(256) AS DECLARE @DBName VARCHAR(256) DECLARE @varSQL VARCHAR(512) DECLARE @getDBName CURSOR SET @getDBName = CURSOR FOR SELECT name FROM sys.databases CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256)) OPEN @getDBName FETCH NEXT FROM @getDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @varSQL = 'USE ' + @DBName + '; INSERT INTO #TmpTable SELECT '''+ @DBName + ''' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name LIKE ''%' + @TableName + '%''' EXEC (@varSQL) FETCH NEXT FROM @getDBName INTO @DBName END CLOSE @getDBName DEALLOCATE @getDBName SELECT * FROM #TmpTable DROP TABLE #TmpTable GO

Creating the SP.

Created successfully.

  

Now I am executing this SP.

It will return the all table names which contains “emp” .

Computed Column in SQL Server

A computed column is computed from an expression that can use other columns in the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators but the sub query can’t be used for computed column.

For example to calculate the total price we are using Quantity and price

Total price = Quantity * price

There are two type of Computed Column

Ø  Persisted

Ø  Non-Persisted

By default computed column is none persisted.

Creating a table

Create table tblOrder ( OrderID bigint, Qty int, Price decimal(18,2), Total_Price as Qty * price persisted )

Now I am going to insert some records.

INSERT INTO tblOrder(OrderID,Qty,Price) Values (1,10,12.5), (2,5,130.35), (3,100,50.5), (4,54,80)

See the above sql query, in this query I am inserting only OrderID,Qty,Price the value of total_Price will be calculated.

Persisted Computed Column

Persisted computed columns are run whenever data is inserted or is updated in a table. A persisted computed field occupies memory for the data. Comparing to a persisted computed column is faster than a non-persisted computed column.

Non-Persisted Computed Column 

Non-Persisted computed columns are run whenever data is selected from a table. A Non-Persisted computed field does not occupy memory for the data, because it is executed when the data is selected. Comparisons to non-persisted computed columns are slower than persisted computed columns. By default computed column is non persisted means if we don't write keyword persisted then computed column treat as a non-persisted. 

We can’t insert the value in computed column.

We can’t update the computed column value.

 By marking a computed column as PERSISTED, we can create an index on a computed column. Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

Limitations of Computed Column:

• A computed column cannot be the target of an INSERT or UPDATE statement.
  
  
• We can’t reference columns from other tables for a computed column expression directly.
  
  
• Null ability for a computed column value will be determined by the database engine itself. The result of most expressions is considered null able even if only non- nullable columns are present, because possible underflows or overflows will produce null results as well. To overcome this problem the COLUMNPROPERTY function with the Allow Null property.
  
  ‘Sub query can’t be used as an expression for creating a computed column.
  
  b. If we are using different data types in our expression then operator of lower precedence will try to convert into the higher precedence data type. If implicit conversion is not possible then error will be generated.

We can create a table using GUI

Right click on the table and create a new table

New window will be open.

Here we can declare the computed expression. 

Print Star in SQL SERVER

We can print the pyramid star in sql server also using sql server using below sql query.

;With CTE(star) AS ( SELECT CAST('*' as varchar(10)) as star UNION ALL SELECT CAST(star +'*' as varchar(10)) FROM CTE WHERE LEN(star)<10 ) SELECT * FROM CTE

See the output

Escape % Sign from Like clause in sql server

Read LIKE clause in SQL serverhere click here

http://bageshkumarbagi-msbi.blogspot.com/2016/05/like-clause-in-sql-server.html

In SQL Server like command is basically used for use wild card character in where clause. The most useful wild character is % . But what happen when in our column already contains % in column data. See the example if in table value have %. Like Bagesh%kumar , Rajesh%Kumar etc.

I am creating a simple table.

create table EmpTemp ( ID int identity(1,1),   Name varchar(50)  )

Now I am inserting some records.

Insert into EmpTemp ( name) Values ('Bagesh%Kumar'), ('Rajesh%kumar'), ('Mashesh%Kumar'), ('Manishkumar')

 I want to fetch all record which have h%k

SELECT * FROM EmpTemp WHERE name like '%h%k%'

See the output.

  

That is not a correct output.

See below sql query.

SELECT * FROM EmpTemp WHERE name like '%h[%]k%'

See the output

  

Get the expected output.

We need to write the % in [] (squire bracket) then it will work.