SSIS unzip files using 7Zip

Oftentimes after downloading a file through either FTP or SFTP, I will need to unzip them.  I have used 7Zip through the commandline to successfully unzip files. A screenshot is below:

MS SQL Partitioned Tables

One of our tables was quite large and was performing poorly and was difficult to manage.  If the users wanted the table imported into another database, SSIS & DTS would choke and we ran into locking and blocking issues if the table was being read while being written to.  We decided to try partitioning the table to see if we could get better results.  We were able to copy over partitions when needed and performance when writing and reading to and from the partition view improved.   

Here are sample scripts for partitioning:

CREATE TABLE table_2010
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not null CONSTRAINT PK_table_2003
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_2010_mailing_date
CHECK (mailing_date <= '2010-12-31'))

CREATE TABLE table_201101
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not null CONSTRAINT PK_table_201101
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201101_mailing_date
CHECK (mailing_date between '2011-01-01' and '2011-01-31'))

CREATE TABLE table_201102
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not nullCONSTRAINT PK_table_201102
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201102_mailing_date
CHECK (mailing_date between '2011-02-01' and '2011-02-28'))

CREATE TABLE table_201103
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not nullCONSTRAINT PK_table_201103
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201103_mailing_date
CHECK (mailing_date between '2011-03-01' and '2011-03-31'))

CREATE partitioned_vw as
Select Field1, Field2, mailing_date
from table_2010
union all select Field1, Field2, mailing_date
from table_201101
union all select Field1, Field2, mailing_date
from table_201102
union all select Field1, Field2, mailing_date
from table_201103

We never implemented swapping out partitions.  If I implement this in the future, I will add another blog entry to go over that.

How to deploy SSRS report to SharePoint

·         click on project in the menu and select project properties

·         set the TargetDataSourceFolder to the location of the data source

·         set the TargetReportFolder to the report folder the report should be deployed to

·         set the TargetServerURL to the server URL

·         set the TargetServerVersion to the reporting services version

·         Right click on the report to be deployed within the solution explorer to bring up the context menu

·         Click on Deploy

A couple SSRS tips

A co-worker of mine, Tina, gave me a couple of tips for SQL Server Reporting Services - so I thought I would publish them to help myself remember and to be of help to anyone else who is reading.

• When exporting to Excel, we found that Excel was showing 3 decimal places instead of 2 even though I had formatted the fields to C2.  By changing the format to $0.00 instead eliminated the extra decimal place in Excel exports.
• Also by changing the background color to White rather than transparent, then scrolling headers would work properly.  Otherwise, the data would run into each other as the user scrolled through the report.

MS SQL tablediff utility

SQL Server has a utility used by replication to compare data in two tables for non-convergence.  This utility can be called from the command prompt or in a batch file.  Look here for details: http://msdn.microsoft.com/en-us/library/ms162843.aspx. 

I used this utility to find differences between tables located on two different servers.  This gave me very fast output. 

An example bat file is as follows:

   cd "c:\program files\microsoft sql server\90\com"
   tablediff -sourceserver "[SourceServer]" -sourcedatabase     
   "[SourceDatabase]" -sourcetable "[SourceTable]" -destinationserver 
   "[DestinationServer]" -destinationdatabase "[DestinationDatabase]" -  
   destinationtable "[DestinationTable]"
   pause

SharePoint disable image right-click

I was asked to disable right-click on images rendered on SharePoint so users would not be able to download and draw mustaches on people's faces.  After discussing how images really can't be secured, it was still the wish that I disable right-click for images.  The first solution I implemented disabled right-click altogether based on this advice:  http://social.msdn.microsoft.com/Forums/en/sharepointcustomizationlegacy/thread/18414dc7-4319-476d-8257-9c498c5092b9.  Basically, I created a content editor web part and put the following code in:

<![CDATA[<html>
<body oncontextmenu= "return false;">
Right Click not allowed on this page
</body>
</html>]]>
Since that worked out, I decided to go a bit further and try to get some javascript to work.  That involved pasting in the following code I saw here http://www.dynamicdrive.com/dynamicindex9/noright2.htm in the content tag.


<![CDATA[<html>
<!-- <body oncontextmenu= "return false;"> -->
<script language="JavaScript1.2">
/*
Disable right click script II (on images)- By Dynamicdrive.com
For full source, Terms of service, and 100s DTHML scripts
Visit http://www.dynamicdrive.com
*/
var clickmessage="Right click disabled on images!"
function disableclick(e) {
if (document.all) {
if (event.button==2||event.button==3) {
if (event.srcElement.tagName=="IMG"){
alert(clickmessage);
return false;
}
}
}
else if (document.layers) {
if (e.which == 3) {
alert(clickmessage);
return false;
}
}
else if (document.getElementById){
if (e.which==3&&e.target.tagName=="IMG"){
alert(clickmessage)
return false
}
}
}
function associateimages(){
for(i=0;i<document.images.length;i++)
document.images[i].onmousedown=disableclick;
}
if (document.all)
document.onmousedown=disableclick
else if (document.getElementById)
document.onmouseup=disableclick
else if (document.layers)
associateimages()
</script>
</body>
</html>
]]>

DTS Notes

Ø  Simple DTS package

§  Source connection

§  Destination connection

§  Datapump task

§  Note: Different types of connection managers

·         ODBC for AS400

·         ODBC for Oracle

·         Native client tends to have better performance than OLEDB – however, in SSIS destination OLEDB allows the developer to define commit batches

Ø  Dynamic Properties task

§  Modifies properties of tasks at runtime with resources outside the task

§  Outside resources:

·         Global variable

·         Constant

·         INI File

·         Data file

·         Query that uses the first row returned

·         Environment variable from the System or User variables

§  INI example to manage server connection between development & production environments

·         Create INI file

·         Add a dynamics property task to the package

·         Add property definition

¨       Double-click dynamic properties task to edit

¨       Click on Add

¨       Navigate through tree & properties to find which property to define

¨       Click on Set

 

    

Ø  INI file is default source

Ø  Browse to the location of the file (needs to have the same relative location on both servers)

Ø  Select section defined in INI file

Ø  Select key from INI file

Ø  Verify value in Preview section

·         Make sure dynamics property task is added to the beginning of the workflow so properties are configured correctly for runtime

Ø  Using Global Variables

§  How to create global variables

·         Right click in empty area of package

·         Select package properties in the context menu

·         Select global variables tab

·         Make sure Explicit Global Variables is checked

·         Add/configure global variables

·         Note: we are using GV1, GV2 naming convention

§  How to use global variables/parameters in Execute SQL task

·         Input parameters

¨       Add execute SQL task

¨       Write SQL query and put in “?” in where clause

¨       Click on “Parameters”

¨       Make sure “Input Parameters” tab is selected

¨       Map parameters to global variables to make SQL statement dynamic

 

·         Output parameters

¨       Add execute SQL task

¨       Write SQL query

¨       Click on “Parameters”

¨       Select “Output parameters” tab

¨       Select output parameter type

¨       Map parameter to global variable

§  Active X script for additional functionality (get around parameter limitations)

·         Add Active X script

·         Declare Active X variables (package, connection, SQL string)

·         Build SQL string concatenating strings & variables

·         Assign SQL string to task SQL source (can assign to data pump tasks or Execute SQL tasks – browse in dynamic properties task to find out name of property)

Ø  Active X script to dynamically change email message content based upon output parameter

Ø  Active X script to manage changing text file destinations