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
Problem: I need the UNC path for a mapped drive
Solution: open up a command prompt, issue “net use” command
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
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.
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
SQL Notes 