Archive for January, 2013

overwrite excel file when exporting from SSIS rather than appending

January 31, 2013

Problem: Want to overwrite excel file when exporting from SSIS rather than appending.

Solution:

Set up 2 SQL Tasks.

SQL Task 1: Connection Type Excel, set your connection and SQL Statement = drop table Excel_Destination
SQL Task 2: Connection Type Excel, set your connection and SQL Statement = CREATE TABLE `Excel Destination` (`id` Long) – copy the create table script from data flow task, excel destination, new excel sheet – the code will be created for you

Advertisements

UNC path

January 22, 2013

Problem: I need the UNC path for a mapped drive

Solution: open up a command prompt, issue “net use” command

Create an Oracle Linked Server From SQL Server

January 17, 2013

Problem: Need to set up a linked server from SQL Server to Oracle

Solution:

Download driver – if you are installing a linked server on a 32 bit server you need the 32 bit version, if you are installing on a 64 bit server you need the 64 bit version.
Google: ole oracle 64
Google: ole oracle 64

Hard link as of 17 Jan 2013

64: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
32: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

I used the smallest download which had OLE DB for Oracle included which in the 64 bit case was the xcopy version which is approx 55 mb.

Install driver

Unzip downloaded file
Open up a command prompt at the unzipped directory

i used the following install command

install.bat oledb c:\oracle myhome

oledb is the component we are installing
c:\oracle is the installation path
myhome is the home name used in the registry keys

Modify Linked Server Provider

Find OraOLEDB.Oracle in the linked server provider folder
Ensure Allow inprocess is ticked

Modify Path Environment Variable

Control Panel
System
Advanced System Settings
Environment Variables
edit system variable path add the following 2 entries c:\oracle\;c:oracle\bin\

*note* the max length of an environment variable is around 2000 charecters, any longer than this and you will start to receive error messages, so you will need to trim it

restart machine

Create Linked Server

Datasource = connection string eg ip:port/service_name

Exception has been thrown by the target of an invocation

January 17, 2013

Problem: When opening Management Studio you receive the error message “Exception has been thrown by the target of an invocation” (for me this happened after installing SQL 2012)

Solution: The maximum length of the PATH environment varible is 2048 charecters, ensure that the length of the variable is less than this value, if not trim it down a bit and it will load.

UNPIVOT function

January 9, 2013

set nocount on

–drop table #t

create table #t

(date datetime,
price1 int,
price2 int)
insert into #t VALUES (’01-dec-2012′,1,2)
insert into #t VALUES (’02-dec-2012′,3,4)
insert into #t VALUES (’03-dec-2012′,5,6)

–original table
select * from #t

–unpivoted table
select date,price_desc,price_val
from
(
select * from #t
) p
UNPIVOT
(
price_val for price_desc in (price1,price2)
) as u