Monthly Archives: July 2016

SQL Server – Integration Service – How To Extract Images From a SQL Server Table To a Folder Using SSIS

In this article we will go through how we can extract images from a table in SQL Server and copy it on a folder. To achieve this we need to use SQL Server Integration Service. Let’s go over the process step by step.

Step 1: Create a new SSIS project in BIDS
IE1

Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface

IE2

Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.

ie3

Step 4: Right click on the connection manager area and create a new OLEDB connection. We will create a connection for the AdventureWorks2012 database and the Production.Photo table.

 

IE4 IE5

 

Step 5. Use the following command to select the data from the table

declare @Ipath varchar(100)= 'C:\document\'
SELECT [ThumbNailPhoto],
@Ipath+[ThumbnailPhotoFileName] AS Imagepath
FROM [AdventureWorks2012].[Production].[ProductPhoto]

 

IE6

 

Step 6. Double click on the export column transformation editor and select the following values

IE7

 

Step 7. Build the package and execute. You should find all the images in the selected folder

IE9

 

SQL Server 2012 – How to change the collation of SQL Server.

In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.

Step 1: Take a backup of all your system databases and user databases.

Step 2: Script out all Logins.

In this example we will change the collation fromSQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
collate1

 

Step 3: Stop the SQL Server Service.

collate3

 

Step 4: Via Command prompt locate to the Binn directory of the SQL Server

collate2

Step 5: Run the below command

sqlservr -m -T4022 -T3659  -s”INST2012_1″  -q”SQL_Latin1_General_CP1_CI_AS”

Parameters:
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation

collate5

Step 6: Restart SQL Server and check the collation

collate6

%d bloggers like this: