Display all the tables and indexes present in a SQL Server database


SELECT
st.name AS [Table Name]
,si.name AS [Index Name]
,si.id AS [Index ID]
,IS_CLUSTERED = INDEXPROPERTY(si.id, si.name, 'IsClustered')
,IS_UNIQUE = INDEXPROPERTY(si.id, si.name, 'IsUnique')
FROM sys.tables st
LEFT OUTER JOIN sys.sysindexes si
ON st.name = OBJECT_NAME(si.id)
WHERE st.type = 'u'
AND OBJECTPROPERTY(si.id, 'IsMsShipped') = 0 /*leave out system tables*/
AND (si.indid BETWEEN 1 AND 254)
AND (si.Status & 64)=0 /*leave out AUTO_STATISTIC*/
ORDER BY st.name, si.name


How to change the UAC

http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx

Upgrade from SQL 2000 to SQL 2008

Check the following article for more details.

Upgrade to SQL 2008

Delete files and folders recurcively


public void DeleteAllFilesAndFolders(string folderPath)
{
try
{
if (String.IsNullOrEmpty(folderPath))
{
return;
}
foreach (string subDirectory in Directory.GetDirectories(folderPath))
{
foreach (string fileName in Directory.GetFiles(subDirectory))
{
File.Delete(fileName);
}
DeleteAllFilesAndFolders(subDirectory);
}
if (Directory.Exists(folderPath))
{
Directory.Delete(folderPath, true);
}
}
catch
{

}
}



Checking for invalid file name and filepath

Following method verifies the given file name for invalid characters and returns the valid filename.


///

/// Verifies if invalid character is present in the filename
/// If invalid character is present it replaces with replace character.
///

/// fileName to validated
/// replace character for invalid character
/// valid filename
public static string ModifyInvalidFileName(string fileName, string replaceChar)
{
if (string.IsNullOrEmpty(fileName))
{
return fileName;
}
if (string.IsNullOrEmpty(replaceChar))
{
replaceChar = "";
}
string strFileName = fileName;
char[] invalidFileNameChars = System.IO.Path.GetInvalidFileNameChars();
int invalidPresent = strFileName.IndexOfAny(invalidFileNameChars);
if (invalidPresent > -1)
{
foreach (char invalidChar in invalidFileNameChars)
{
strFileName = strFileName.Replace(invalidChar.ToString(), replaceChar);
}
}
return strFileName;
}



We can use GetInvalidFileNameChars from System.IO namespace to identify all the invalid characters in the File Name. Check the following link for more details. If we use the built-in C#.net methods it will check for all the invalid characters and no need to identify each charcter by us.

getinvalidfilenamechars

To verify the invalid Path we can use the GetInvalidPathChars() method.

GetInvalidPathChars

SSIS

Bulk Insert Task Limitation:
The Bulk insert task does not log error causing rows. If you want your bad records to written to an error file or table, it is better to use Data Flow Task.

Bulk Insert Task will not fire triggers by default.

Microsoft Connectors for SAP BI, Teradata, Oracle to connect SSIS / SQL Server

You can import data from Oracle or Teradata to SQL Server using SSIS Connectors. To perform these operations you need to install the Oracle and Teradata connectors from microsoft at the following link

Connector Download Link

After installing the connectors, open the BIDS(VisualStudio)
open the ssis project and open the ssis package. Go to data flow items. Rigth click on Data Flow items on the context menu "Choose Items" In the Choose ToolBox Items window select "SSIS Data Flow Items" and select the check box for "ORACLE Source" and "Oracle Destination". For Teradata select the check box for "Teradata Source" and "Teradata Destination" and press ok.

Check the user forums for issues and updates

User Fourms

To connect to SAP BW from SSIS, install the Microsoft Connector 1.0 for SAP BI from the following location.
Microsoft Connector for SAP BI Download page

Download it from "Microsoft Connector 1.0 for SAP BI" in that page.

SQL Server usefull system procedures

Following are the System level stored Procedures which are helpfull in day to day life in Microsoft SQL Server

SP_HELP <<Object_Name >> -- Displays object structure

SP_HELPSORT -- Displays database sort order based on the installation

SP_SPACEUSED -- Displays space used by the database. If passed the table name, displays space used by that table

SSIS 2008 Development issues and Solutions

Q1.
Error: 0xC00F9304 at Package2, Connection manager "DestinationConnectionExcel": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
A. Go to SSIS Project --> Properties --> Debugging --> Run64BITRuntime = FALSE

For more details check the following
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9
http://msdn.microsoft.com/en-us/library/ms141766.aspx

Q2. How to download files from SFTP sites in SSIS.
A. SSIS only supports download of files from FTP sites. To download files from SFTP sites in SSIS, you need to use the third party tools. One workaround is described in the following article. You can install WinSCP tool to achieve this. Read the following article for more information.
Workaround article on CodeProject
Complete Wroking Code


Q3. How to configure Send Mail Task from database records?
A. Check the folloing article for more details. You need to use Send mail task inside ForEach loop. In the send mail task, configure the expression for the mail properties.

http://www.sqlis.com/post/Shredding-a-Recordset.aspx

Q4. How to debug SSIS package or How to view variable values?
A. Check the following article for more details
Select the task in SSIS package, right click --> Edit Breakpoints
Check the checkbox for onPreExecute to verify the values before executing the package.
Check the checkbox for onPostExecute to verify the values after executing the package.

Debug SSIS package

Q5.How to write entries into event logs from SSIS Package

You can use script task and use System.Diagnostics namespace.
How to write entries into event logs from SSIS

SSIS Development articles
http://www.sqlis.com/archive.aspx

some solutions to SSIS development issues
http://blogs.msdn.com/dataaccesstechnologies/archive/2009/11/09/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

Q5. SSIS Script Task runs fine in BIDS and MSDB, but can't get to run via SQL Agent Job in Windows 2008 Server
Please make this folder.
C:\Windows\SysWOW64\config\systemprofile\Desktop
・Windows 2008 Server x86
Please make this folder.
C:\Windows\System32\config\systemprofile\Desktop
...instead of dcomcnfg.exe.
This operation took away office automation problems in my system.
A Desktop folder seems to be necessary in the systemprofile folder to open file by Excel.
It disappears from Windows2008, Windows2003 had the folder

Here’s the repro steps:
1. Script task code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel


_
_
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum


Public Sub Main()

Dim excel As New Microsoft.Office.Interop.Excel.Application()
Dim wBook As Microsoft.Office.Interop.Excel.Workbook

'Open the excel file and worksheet
wBook = excel.Workbooks.Open("\\filepath\filename.xlsx")
wBook.RefreshAll()

'Save workbook in shared mode with no overwrite prompt
'excel.DisplayAlerts = False
wBook.Save()
wBook.Close()
excel.Quit()
Dts.TaskResult = ScriptResults.Success
End Sub

End Class
2. Import file to MSDB
3. Successfully run script to refresh workbook via BIDS and MSDB
4. Setup as SQL Agent Job and get the following error
5. Error details
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
07/14/2009 12:03:53,MonthlyForecast2010_Mktg,Error,0,BPSSQL03,MonthlyForecast2010_Mktg,(Job outcome),,The job failed. The Job was invoked by User REDMOND\hedsvc1. The last step to run was step 3 (Refresh Metrics). The job was requested to start at step 3 (Refresh Metrics).,00:00:01,0,0,,,,0
07/14/2009 12:03:53,MonthlyForecast2010_Mktg,Error,3,BPSSQL03,MonthlyForecast2010_Mktg,Refresh Metrics,,Executed as user: REDMOND\brentkr. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1806.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:03:53 PM Error: 2009-07-14 12:03:53.95 Code: 0x00000001 Source: Script Task 1 Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'http://team/sites/mshproducts/pkg/Budget Files/FY10 Marketing Budget/MonthlyForecast_Metrics.xlsm'. There are several possible reasons: ? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename Object UpdateLinks Object ReadOnly Object Format Object Password Object WriteResPassword Object IgnoreReadOnlyRecommended Object Origin Object Delimiter Object Editable Object Notify Object Converter Object AddToMru Object Local Object CorruptLoad) at ST_918bc12a04614420a1319ead3ce0c2eb.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target Object[] arguments SignatureStruct& sig MethodAttributes methodAttributes RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target Object[] arguments Signature sig MethodAttributes methodAttributes RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj BindingFlags invokeAttr Binder binder Object[] parameters CultureInfo culture Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj BindingFlags invokeAttr Binder binder Object[] parameters CultureInfo culture) at System.RuntimeType.InvokeMember(String name BindingFlags bindingFlags Binder binder Object target Object[] providedArgs ParameterModifier[] modifiers CultureInfo culture String[] namedParams) at System.Type.InvokeMember(String name BindingFlags invokeAttr Binder binder Object target Object[] args CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:03:53 PM Finished: 12:03:53 PM Elapsed: 0.639 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

Q6.Unable to prepare the SSIS bulk insert for data insertion" error on UAC enabled systems
Check the following article Article
Use one of the following methods to workaround the problem.:

If you are running the package from either SQL Server Management Studio (SSMS) or Business Intelligence Development Studio (BIDS) or DTExecUI.exe, launch those tools under the elevated Administrator account. To do this, click Start , point to All Programs , point to SQL Server 2005 or SQL Server 2008 , right-click the tool you are using and then click Run as administrator. This launches the application with elevated privileges of the Built In Administrator account and the package executes successfully.
Similarly if you are running the package using DTExec.exe launch it from an elevated command prompt. You can start the elevated command prompt by clicking Start, click All Programs , click Accessories , right-click Command Prompt , and then click Run as administrator.

Note: If you do not log on to the computer as an administrator, you are prompted to provide the administrator account. When you are prompted to provide the administrator account, type the administrator user name and password in the User Account Control dialog box. Then, click OK .

Replace the SQL Server Destination components in the Dataflow Tasks which are failing with OLE DB Destination components that point to the same SQL Server connection manager.

Use an account that is not a member of the local Administrators group after assigning "Create Global Objects" user right to that account.
To do this, follow these steps:
Click Start, point to Administrative Tools, and then click Local Security Policy.
Expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Create global objects.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK.
Click OK.

Note: When using an account that is not a member of local Administrators group, UAC does not come into play.