SSIS package validation while deploying to TEST or UAT or Production Server

Let's say you have created a great package in development environment and you have configured it and deployed on Test Server or UAT server. Now before you want Tester to know that package is out, You want to make sure that package would run as it was intended to do.

So How do we validate a SSIS package on a Test Server before it is being executed.

Let's dive in:

Step 1. Log on to your Test Integration Server and go to the package you want to validate.

Step 2. Right Click on the package and click Run Package.

Step 3. Go to Execution Options and check "Validate package without executing" option.

Step 4. Now Go to Command Line and click "Edit the command line Manually". In the command right at the end after /REPORTING V  add " > results.txt". Add your 32-bit execution path of package at the beginning.

C:\PROGRA~2\MICROS~1\100\DTS\Binn\dtexec.exe

So it will look like something this

C:\PROGRA~2\MICROS~1\100\DTS\Binn\dtexec.exe  /DTS "\DEV\mypackagename" /SERVER SERVERNAME /CHECKPOINTING OFF  /REPORTING V > results.txt

Step 5. Go to your command prompt where your server is install and paste the above text. And hit enter

Step 6. Type following " notepad results.txt" and hit enter

As soon as the validation is done, a notepad will open with the validation result.

At the end you will value 0 or 1. If it is 0, it mean that you package will execute without any metadata failure.
Remember that this is just validation. It won't check data in your databuffer or any other thing. It will just validate the package.

If you need more help on this one. Just leave a comment.

SQL To remove first few character from query

Suppose you have to write a query to remove first few character in a column on a SQL Server table.

Col A             Col B
1                    CM NewData
2                   CM Old Data
3                   CM My Data

And we want to remove "CM " cm with space from our result set or use this query to update the column itself.


There are four ways to do this.

Method 1
Substring function: We need three information column name , starting position and ending position. In our example we know column name, starting positing (4th character here) and I can put any ending length would be column length

SELECT SUBSTRING(ColB, 4,1000)
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 2

Use of RIGHT FUNTION: Here we  need column name, total length of column minus what length we need to remove on the right side.


SELECT RIGHT(colB, LEN(colB) - 4)
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 3

Replace Method. In Replace function, we take column name, what we are looking for and what we need to replace with it. Make sure that the pattern you want to replace does not repeat in same string. Otherwise it would replace them too.

SELECT REPLACE(ColB, 'CM ','')
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 4

STUFF Method. Stuff method is another way to replace character in a column.

  SELECT Stuff(ColB, 1, 4, '')
    FROM [dbo].[MyTable]

  WHERE ColB LIKE 'cm %'

All of the above method has same execution result and cost factor. Depending on your need, you can use which ever fits your bill.

:)

Moving Large Table to Different File Group

--Moving Large Table to Different File Group

--Step 1. Find the space occupied by tabe which needs to be moved to file group.

sp_spaceused 'ACCident' -- This will list table name with rows, reserved data, index size and unused space

--Step 2.  Create a new filegroup by altering database.

ALTER Database Databasename add filegroup [SecondaryData] -- This will add a new filegroup

--Step 3. Create a file and point to this new filegroup.

ALTER DATABASE Databasename
ADD FILE
( NAME = XFILENAME,
FILENAME = 'new path\SALES.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP [SECONDERYDATA]
GO

--Step 4. Alter the table to change index on the table

ALTER TABLE [BIGTABLE]
DROP CONSTRAINT [BIGTABLE_PK] WITH (MOVE TO SECONDERYDATA)

--Step 5. Recreate the index

ALTER TABLE [BIGTABLE]
ADD CONSTRAINT [BIGTABLEE_PK] PRIMARY KEY CLUSTERED
( [column name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [SECONDERYDATA]