August 10, 2017 at 5:03 am
HI Everyone ,
SSIS package is about, SSRS report will generate and will be place in one folder using SQL job . Then from another job this excel will get update with filter and some column will hide.
While executing package using SSDT tool excel is getting updated with filter and unwanted column is also hide .
But while doing it using job it not even updating and not giving any error.
Thanks in Advance.
August 10, 2017 at 5:26 am
Unfortunately there seems to be language barrier here, but I don't understand what your question is.
You firstly seem to be saying you have a SQL Agent job that runs an SSIS package, that create a SSRS report, correct?
I think you're then saying that when you run the package in SSDT the package does what you want, but when run in the agent it doesn't? You say you're adding a Filter and hiding a column, how are you achieving that in SSDT? I would hazard a guess that you're doing it in SSIS with a Script Task which opens the file (in Excel) and doing the task with some kind of VB code? That won't work on the server, as you can't open an application window in an Agent job, so the task would likely fail. Even doing things like setting a Message box to be presented in Script Task can't be done on the server side. yes, it'll work in SSDT (as you're in a GUI), but the package isn't run in a graphical environment, so a message box command would fail.
If I'm way off the mark here, could you try to explain again? Be as concise as possible, describing the steps you do. What you've posted as is is quite vague, so we'd need more information anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 10:09 am
HI Thom,
You firstly seem to be saying you have a SQL Agent job that runs an SSIS package, that create a SSRS report, correct? -- Yes
I think you're then saying that when you run the package in SSDT the package does what you want, but when run in the agent it doesn't? -- correct
You say you're adding a Filter and hiding a column, how are you achieving that in SSDT?
-- with the help of VB script
I would hazard a guess that you're doing it in SSIS with a Script Task which opens the file (in Excel) and doing the task with some kind of VB code? That won't work on the server, as you can't open an application window in an Agent job, so the task would likely fail. Even doing things like setting a Message box to be presented in Script Task can't be done on the server side. yes, it'll work in SSDT (as you're in a GUI), but the package isn't run in a graphical environment, so a message box command would fail.
If I'm way off the mark here, could you try to explain again? Be as concise as possible, describing the steps you do. What you've posted as is is quite vague, so we'd need more information anyway.
August 16, 2017 at 10:15 am
That doesn't really help clarify a lot here. Could you post the VB Script? Please ensure your encapsulate it in IF Markup so that it is easily readable. How are you executing the VB Script?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 10:16 am
Any time that an SSIS job works in SSDT, but not in SQL Agent, your first suspicion should be that there is a permissions problem.
Does the SQL Agent service/proxy user have rights to modify the Excel file?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 16, 2017 at 11:21 am
Phil Parkin - Wednesday, August 16, 2017 10:16 AMAny time that an SSIS job works in SSDT, but not in SQL Agent, your first suspicion should be that there is a permissions problem.
Does the SQL Agent service/proxy user have rights to modify the Excel file?
One would expect some kind of error then though. Although, I would with the opening of a file in a Graphical way too.
Are you using SSISDB or MSDB to store the package? What do the logs say when it runs?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 12:32 pm
Thom A - Wednesday, August 16, 2017 11:20 AMOne would expect some kind of error then though. Although, I would with the opening of a file in a Graphical way too.Are you using SSISDB or MSDB to store the package? What do the logs say when it runs?
Indeed one would. But I believe I have seen cases in the past where there was none, and some Sherlock Holmes work was required in lieu of any error message to track down the problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 18, 2017 at 4:52 am
Phil Parkin - Wednesday, August 16, 2017 10:16 AMAny time that an SSIS job works in SSDT, but not in SQL Agent, your first suspicion should be that there is a permissions problem.
Does the SQL Agent service/proxy user have rights to modify the Excel file?
As per developer this project was running proper before. From last month they are facing some issue . So seems like there is no access issues.
I have checked SQL agent service is running using service account and it having sufficient access becasue other job using SSIS package are running proper.
August 18, 2017 at 4:59 am
Phil Parkin - Wednesday, August 16, 2017 12:32 PMThom A - Wednesday, August 16, 2017 11:20 AMOne would expect some kind of error then though. Although, I would with the opening of a file in a Graphical way too.Are you using SSISDB or MSDB to store the package? What do the logs say when it runs?
Indeed one would. But I believe I have seen cases in the past where there was none, and some Sherlock Holmes work was required in lieu of any error message to track down the problem.
package are stored in SSISDB database we are using SQL server 2014 version.
Also job are executing using MSDB.
when I check the log of SQL agent it shows job executed successfully without any error . But actually, we are not achieving of adding filer and hiding some column in excel
August 18, 2017 at 5:06 am
megha.chandak88 - Friday, August 18, 2017 4:59 AMpackage are stored in SSISDB database we are using SQL server 2014 version.
Also job are executing using MSDB.
when I check the log of SQL agent it shows job executed successfully without any error . But actually, we are not achieving of adding filer and hiding some column in excel
Does aren't the logs. What do the SSIS logs say? If you're using SSISDB, you'll need to look at the package's logs in the Intergration Services Catalogs.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 18, 2017 at 5:33 am
megha.chandak88 - Friday, August 18, 2017 4:52 AMPhil Parkin - Wednesday, August 16, 2017 10:16 AMAny time that an SSIS job works in SSDT, but not in SQL Agent, your first suspicion should be that there is a permissions problem.
Does the SQL Agent service/proxy user have rights to modify the Excel file?As per developer this project was running proper before. From last month they are facing some issue . So seems like there is no access issues.
I have checked SQL agent service is running using service account and it having sufficient access becasue other job using SSIS package are running proper.
You can have 5,000 packages that work without security issues, only to have package 5,001 fail because of security issues. It all depends on what the problematic package is doing. However, the fact that the package was working OK in the past lessens the probability, I agree.
But what you seem to be saying is that nothing has changed, but something which used to work has now stopped working.
I'm sorry, but I don't buy that: something has changed. The question is, what? The All Executions report, from SSISDB, should help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 18, 2017 at 7:36 am
Thom A - Wednesday, August 16, 2017 10:15 AMThat doesn't really help clarify a lot here. Could you post the VB Script? Please ensure your encapsulate it in IF Markup so that it is easily readable. How are you executing the VB Script?
VB Script for reference.
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Drawing;
using Microsoft.Office.Interop.Excel;
//using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Runtime.InteropServices;
//using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_b56651c6f8674e2bbfebb225ffa822bc
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
// TODO: Add your code here
// Declare variables that hold references to Excel objects.
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
ApplicationClass excelApplication = null;
Workbook excelWorkBook = null;
Worksheet targetSheet = null;
object useDefault = Type.Missing;
string workBookName = Dts.Variables["User::var_working_path_report_local_folder"].Value.ToString();
string subscription_id = Dts.Variables["User::Var_SubscriptionId"].Value.ToString();
string HideColumn_status = Dts.Variables["User::Var_ColumnHideStatus"].Value.ToString();
string Autofilter_status = Dts.Variables["User::Var_AutoFilter"].Value.ToString();
string Freeze_Header_Status = Dts.Variables["User::Var_FreezeHeaderStatus"].Value.ToString();
string Coloredtab_Status = Dts.Variables["User::Var_IsColoredTabName"].Value.ToString();
//// this is a shortcut for your connection string
ConnectionManager cm = Dts.Connections["DBConn"];
System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
System.Data.SqlClient.SqlCommand sqlComm;
try
{
excelWorkBook =
excel.Workbooks.Open(workBookName,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
System.Threading.Thread.Sleep(10000);
System.Data.DataTable TempTable = new System.Data.DataTable();
sqlComm = new System.Data.SqlClient.SqlCommand("Select report_id,Sheet_Name,AutoFilter_Range,HiddenColumn_Range,ExtraColumnHide_Range,HeaderFreeze_RowNum,ColoredSheet_Ind from telematicsmanage.ReportTab_Requirement_Details where Is_active_ind='Y' and Report_id in (select Report_id from telematicsmanage.subscription_fact where Subscription_Id ='" + subscription_id + "'" + ')', sqlConn);
TempTable.Load(sqlComm.ExecuteReader());
if (TempTable.Rows.Count > 0)
{
if (Coloredtab_Status == "Y")
{
int i = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
worksheet.Activate();
for (i = 0; i < TempTable.Rows.Count; )
{
string ColoredTab = TempTable.Rows["ColoredSheet_Ind"].ToString().ToUpper();
string a = worksheet.Name.ToString().ToLower().Trim();
if (ColoredTab == "")
{
i++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim())
{
if (ColoredTab == "Y")
{
worksheet.Tab.Color = Color.Orange;
}
i++;
}
else
{
i++;
}
}
}
}
// Get the worksheet that contains the data to filter.
if (Autofilter_status == "Y")
{
int j = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter = null;
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
worksheet.Activate();
string a = worksheet.Name.ToString().ToLower().Trim();
for (j = 0; j < TempTable.Rows.Count; )
{
string range = TempTable.Rows[j]["AutoFilter_Range"].ToString().ToUpper();
if (range == "")
{
string h = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
j++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim())
{
string ab = worksheet.Name.ToString().ToLower().Trim();
string kl = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
rngFilter = worksheet.Application.get_Range("" + range + "", Type.Missing);
rngFilter.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
j++;
}
else
{
string h = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
j++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
if (rngFilter != null)
{
Marshal.ReleaseComObject(rngFilter);
}
}
}
if (HideColumn_status == "Y")
{
int i = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
Microsoft.Office.Interop.Excel.Range rngFilter2 = null;
worksheet.Activate();
for (i = 0; i < TempTable.Rows.Count; )
{
string a = worksheet.Name.ToString().ToLower().Trim();
//get range
string HiddencolumnRange = TempTable.Rows["HiddenColumn_Range"].ToString().ToUpper();
string ExtraColumnHide = TempTable.Rows["ExtraColumnHide_Range"].ToString().ToUpper();
string j = TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim();
if (HiddencolumnRange == "")
{
i++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim())
{
string ad = worksheet.Name.ToString().ToLower().Trim();
string jg = TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim();
rngFilter1 = worksheet.Application.get_Range("" + HiddencolumnRange + "", Type.Missing);
rngFilter1.EntireColumn.Hidden = true;
if (ExtraColumnHide != "")
{
rngFilter2 = worksheet.Application.get_Range("" + ExtraColumnHide + "", Type.Missing);
rngFilter2.EntireColumn.Hidden = true;
}
i++;
}
else
{
i++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
if (rngFilter2 != null)
{
Marshal.ReleaseComObject(rngFilter2);
}
}
}
if (Freeze_Header_Status == "Y")
{
int j = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
worksheet.Activate();
for (j = 0; j < TempTable.Rows.Count; )
{
string abc = worksheet.Name.ToString().ToLower().Trim();
int FreezeRowNum;
if (TempTable.Rows[j]["HeaderFreeze_RowNum"] is DBNull)
{
j++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim())
{
string ab = worksheet.Name.ToString().ToLower().Trim();
string b = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
FreezeRowNum = Convert.ToInt32(TempTable.Rows[j]["HeaderFreeze_RowNum"]);
worksheet.Activate();
worksheet.Select();
rngFilter1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
worksheet.Select();
worksheet.Application.Activewindow.SplitRow = FreezeRowNum;
worksheet.Application.Activewindow.FreezePanes = true;
j++;
}
else
{
j++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
}
}
}
}
catch (Exception E)
{
Dts.Events.FireError(0, "My File Task", E.Message, String.Empty, 0);
string error = E.Message.Replace("'", "").ToString();
sqlComm = new System.Data.SqlClient.SqlCommand("update telematicsmanage.subscription_log set ReportExcelConversion_Exception = ' " + error + "' where ReportExcelConverted_dt IS NULL and ReportGenerated_dt!='' and Subscription_Id ='" + Dts.Variables["User::Var_SubscriptionId"].Value + "'", sqlConn);
sqlComm.ExecuteReader();
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
// Release the references to the Excel objects.
excelWorkBook.Save();
System.Threading.Thread.Sleep(10000);
//excelWorkBook.Close();
////excel.Workbooks.Close();
//targetSheet = null;
cm.ReleaseConnection(sqlConn);
//// Close the Workbook object.
//if (excelWorkBook != null)
// excelWorkBook = null;
////// Close the ApplicationClass object.
//if (excelApplication != null)
//{
// excelApplication.Quit();
// excelApplication = null;
//}
GC.Collect();
GC.WaitForPendingFinalizers();
if (targetSheet != null)
{
Marshal.ReleaseComObject(targetSheet);
}
if (excelWorkBook != null)
{
//excelWorkBook.Close(false);
Marshal.ReleaseComObject(excelWorkBook);
}
if (excel != null)
{
//KillProcess kp = new KillProcess();
//kp.GetExcelProcess(srcxlApp);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
//if (targetSheet != null)
//{
// Marshal.ReleaseComObject(targetSheet);
//}
//if (excelWorkBook != null)
//{
// excelWorkBook.Close(false);
// Marshal.ReleaseComObject(excelWorkBook);
//}
//if (excel != null)
//{
// excel.Quit();
// Marshal.ReleaseComObject(excel);
//}
//if (excelApplication != null)
//{
// excelApplication.Quit();
// Marshal.ReleaseComObject(excelApplication);
//}
//Garbage collection.
//Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception E)
{
Dts.Events.FireError(0, "My File Task", E.Message, String.Empty, 0);
string error = E.Message.Replace("'", "").ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
August 18, 2017 at 7:43 am
And here it is again, this time with mark-up, as Thom requested.
Not sure what makes you think that this is 'VBScript'. It's pure C#.#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Drawing;
using Microsoft.Office.Interop.Excel;
//using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Runtime.InteropServices;
//using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_b56651c6f8674e2bbfebb225ffa822bc
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
// TODO: Add your code here
// Declare variables that hold references to Excel objects.
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
ApplicationClass excelApplication = null;
Workbook excelWorkBook = null;
Worksheet targetSheet = null;
object useDefault = Type.Missing;
string workBookName = Dts.Variables["User::var_working_path_report_local_folder"].Value.ToString();
string subscription_id = Dts.Variables["User::Var_SubscriptionId"].Value.ToString();
string HideColumn_status = Dts.Variables["User::Var_ColumnHideStatus"].Value.ToString();
string Autofilter_status = Dts.Variables["User::Var_AutoFilter"].Value.ToString();
string Freeze_Header_Status = Dts.Variables["User::Var_FreezeHeaderStatus"].Value.ToString();
string Coloredtab_Status = Dts.Variables["User::Var_IsColoredTabName"].Value.ToString();
//// this is a shortcut for your connection string
ConnectionManager cm = Dts.Connections["DBConn"];
System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
System.Data.SqlClient.SqlCommand sqlComm;
try
{
excelWorkBook =
excel.Workbooks.Open(workBookName,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
System.Threading.Thread.Sleep(10000);
System.Data.DataTable TempTable = new System.Data.DataTable();
sqlComm = new System.Data.SqlClient.SqlCommand("Select report_id,Sheet_Name,AutoFilter_Range,HiddenColumn_Range,ExtraColumnHide_Range,HeaderFreeze_RowNum,ColoredSheet_Ind from telematicsmanage.ReportTab_Requirement_Details where Is_active_ind='Y' and Report_id in (select Report_id from telematicsmanage.subscription_fact where Subscription_Id ='" + subscription_id + "'" + ')', sqlConn);
TempTable.Load(sqlComm.ExecuteReader());
if (TempTable.Rows.Count > 0)
{
if (Coloredtab_Status == "Y")
{
int i = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
worksheet.Activate();
for (i = 0; i < TempTable.Rows.Count; )
{
string ColoredTab = TempTable.Rows["ColoredSheet_Ind"].ToString().ToUpper();
string a = worksheet.Name.ToString().ToLower().Trim();
if (ColoredTab == "")
{
i++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim())
{
if (ColoredTab == "Y")
{
worksheet.Tab.Color = Color.Orange;
}
i++;
}
else
{
i++;
}
}
}
}
// Get the worksheet that contains the data to filter.
if (Autofilter_status == "Y")
{
int j = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter = null;
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
worksheet.Activate();
string a = worksheet.Name.ToString().ToLower().Trim();
for (j = 0; j < TempTable.Rows.Count; )
{
string range = TempTable.Rows[j]["AutoFilter_Range"].ToString().ToUpper();
if (range == "")
{
string h = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
j++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim())
{
string ab = worksheet.Name.ToString().ToLower().Trim();
string kl = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
rngFilter = worksheet.Application.get_Range("" + range + "", Type.Missing);
rngFilter.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
j++;
}
else
{
string h = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
j++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
if (rngFilter != null)
{
Marshal.ReleaseComObject(rngFilter);
}
}
}
if (HideColumn_status == "Y")
{
int i = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
Microsoft.Office.Interop.Excel.Range rngFilter2 = null;
worksheet.Activate();
for (i = 0; i < TempTable.Rows.Count; )
{
string a = worksheet.Name.ToString().ToLower().Trim();
//get range
string HiddencolumnRange = TempTable.Rows["HiddenColumn_Range"].ToString().ToUpper();
string ExtraColumnHide = TempTable.Rows["ExtraColumnHide_Range"].ToString().ToUpper();
string j = TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim();
if (HiddencolumnRange == "")
{
i++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim())
{
string ad = worksheet.Name.ToString().ToLower().Trim();
string jg = TempTable.Rows["Sheet_Name"].ToString().ToLower().Trim();
rngFilter1 = worksheet.Application.get_Range("" + HiddencolumnRange + "", Type.Missing);
rngFilter1.EntireColumn.Hidden = true;
if (ExtraColumnHide != "")
{
rngFilter2 = worksheet.Application.get_Range("" + ExtraColumnHide + "", Type.Missing);
rngFilter2.EntireColumn.Hidden = true;
}
i++;
}
else
{
i++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
if (rngFilter2 != null)
{
Marshal.ReleaseComObject(rngFilter2);
}
}
}
if (Freeze_Header_Status == "Y")
{
int j = 0;
foreach (Worksheet worksheet in excelWorkBook.Worksheets)
{
Microsoft.Office.Interop.Excel.Range rngFilter1 = null;
worksheet.Activate();
for (j = 0; j < TempTable.Rows.Count; )
{
string abc = worksheet.Name.ToString().ToLower().Trim();
int FreezeRowNum;
if (TempTable.Rows[j]["HeaderFreeze_RowNum"] is DBNull)
{
j++;
}
else if (worksheet.Name.ToString().ToLower().Trim() == TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim())
{
string ab = worksheet.Name.ToString().ToLower().Trim();
string b = TempTable.Rows[j]["Sheet_Name"].ToString().ToLower().Trim();
FreezeRowNum = Convert.ToInt32(TempTable.Rows[j]["HeaderFreeze_RowNum"]);
worksheet.Activate();
worksheet.Select();
rngFilter1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
worksheet.Select();
worksheet.Application.Activewindow.SplitRow = FreezeRowNum;
worksheet.Application.Activewindow.FreezePanes = true;
j++;
}
else
{
j++;
}
}
if (rngFilter1 != null)
{
Marshal.ReleaseComObject(rngFilter1);
}
}
}
}
}
catch (Exception E)
{
Dts.Events.FireError(0, "My File Task", E.Message, String.Empty, 0);
string error = E.Message.Replace("'", "").ToString();
sqlComm = new System.Data.SqlClient.SqlCommand("update telematicsmanage.subscription_log set ReportExcelConversion_Exception = ' " + error + "' where ReportExcelConverted_dt IS NULL and ReportGenerated_dt!='' and Subscription_Id ='" + Dts.Variables["User::Var_SubscriptionId"].Value + "'", sqlConn);
sqlComm.ExecuteReader();
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
// Release the references to the Excel objects.
excelWorkBook.Save();
System.Threading.Thread.Sleep(10000);
//excelWorkBook.Close();
////excel.Workbooks.Close();
//targetSheet = null;
cm.ReleaseConnection(sqlConn);
//// Close the Workbook object.
//if (excelWorkBook != null)
// excelWorkBook = null;
////// Close the ApplicationClass object.
//if (excelApplication != null)
//{
// excelApplication.Quit();
// excelApplication = null;
//}
GC.Collect();
GC.WaitForPendingFinalizers();
if (targetSheet != null)
{
Marshal.ReleaseComObject(targetSheet);
}
if (excelWorkBook != null)
{
//excelWorkBook.Close(false);
Marshal.ReleaseComObject(excelWorkBook);
}
if (excel != null)
{
//KillProcess kp = new KillProcess();
//kp.GetExcelProcess(srcxlApp);
excel.Quit();
Marshal.ReleaseComObject(excel);
}
//if (targetSheet != null)
//{
// Marshal.ReleaseComObject(targetSheet);
//}
//if (excelWorkBook != null)
//{
// excelWorkBook.Close(false);
// Marshal.ReleaseComObject(excelWorkBook);
//}
//if (excel != null)
//{
// excel.Quit();
// Marshal.ReleaseComObject(excel);
//}
//if (excelApplication != null)
//{
// excelApplication.Quit();
// Marshal.ReleaseComObject(excelApplication);
//}
//Garbage collection.
//Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception E)
{
Dts.Events.FireError(0, "My File Task", E.Message, String.Empty, 0);
string error = E.Message.Replace("'", "").ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 18, 2017 at 8:01 am
I'm not going to pretend my C# is great (it's not, I use VB.net myself), but I notice there there do appear to be calls to Open the workbook. I can't see it anywhere, but if this is the case wouldn't this "Open" Excel and load the Workbook inside that? Thus, created a graphical interface? Infact, I can see references to Worksheet.Activate, which heavily suggests this.
If this is the case, my suggestion from further up the topic seems to be correct. you're trying to do something graphical in a non-graphical environment. you can't "Open" Excel in an SSIS Package (yes, it let's you in SSDT, as you're running Visual Studio Graphcially). Trying to open a graphical program is like trying to open one in a command line only interface; it doesn't work.
I do have to ask, why are you editing the data after creating it from SSIS anyway? Ideally, you should be exporting the data in the format you require in the first step; then there is no need to play around with the workbook after you're done.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 18, 2017 at 8:06 am
Thom A - Friday, August 18, 2017 8:01 AM...I use VB.net myself
The horror, the horror ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply