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.