Can't Precompile Script
Feb 9, 2006
Hi,
with a script task I get an error "This task is configured to pre-compile the script but the binary code is not found. Please visit the IDE in Script Task editor by clicking Desing Script button to cause binary code to be generated".
Well, if I do so the error doesn't disappear. The only chance I have is to switch of precopilation, which is quite a performance issue...
I have no idea where this error is comming from... The script is quite easy, just some string and file operations (find out file change date using system.io)...
Any idea?
View 8 Replies
Nov 27, 2007
I've been working on adding "copy a script task from one package to another package" functionality to my SSIS Package Manager (PacMan - http://www.codeplex.com/pacman) utility in order to ease some of the pain I'm feeling in my main SSIS dev project these days. The code I have today looks something like this:
Code Block
public void CopyScriptTaskPrototype(PackageUtil sourcePackage)
{
// Basic logic taken from https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941508&SiteID=1
const string CODE_MONIKER_TEMPLATE = "dts://Scripts/{0}/ScriptMain.vsaitem";
const string PROJ_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.vsaproj";
const string BINN_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.dll";
try
{
// Get the source task
TaskHost sourceScriptTaskHost = sourcePackage.ssisPackage.EventHandlers["OnPreExecute"].Executables[0] as TaskHost;
ScriptTask sourceScriptTask = sourceScriptTaskHost.InnerObject as ScriptTask;
// Get the target container
if (!this.ssisPackage.EventHandlers.Contains("OnPreExecute"))
{
this.ssisPackage.EventHandlers.Add("OnPreExecute");
}
DtsEventHandler targetContainer = this.ssisPackage.EventHandlers["OnPreExecute"];
// Delete the target task, if it exists
if (targetContainer.Executables.Contains(sourceScriptTaskHost.Name))
{
targetContainer.Executables.Remove(sourceScriptTaskHost.Name);
}
TaskHost targetScriptTaskHost = targetContainer.Executables.Add("STOCK:SCRIPTTASK") as TaskHost;
targetScriptTaskHost.Name = sourceScriptTaskHost.Name;
targetScriptTaskHost.Description = sourceScriptTaskHost.Description;
ScriptTask targetScriptTask = targetScriptTaskHost.InnerObject as ScriptTask;
targetScriptTask.SetUniqueVsaProjectName();
string sourceCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
string sourceProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
string sourceBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
targetScriptTask.CodeProvider.PutSourceCode(targetCodeMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceCodeMoniker));
targetScriptTask.CodeProvider.PutSourceCode(targetProjectMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceProjectMoniker));
// We've commented this out due to errors at package runtime
//targetScriptTask.CodeProvider.PutBinaryCode(targetBinaryMoniker,
// sourceScriptTask.CodeProvider.GetBinaryCode(sourceBinaryMoniker));
targetScriptTask.PreCompile = false; // We want to be able to say "true" here
}
catch (Exception ex)
{
throw new ApplicationException("Could not copy script task - oh no!", ex);
}
}
This method exists within a PackageUtil class that has a private member variable named ssisPackage of type Microsoft.SqlServer.Dts.Runtime.Package, and for my current purposes I only need to worry about copying the first task from the OnPreExecute event handler for the package, so some things are hard-coded now that won't be later on.
Now, with that said, here is the question: How, through the .NET API can I force the Script Task code to be compiled so that I can set the PreCompile flag to true and have it work as if I'd done the work through VSA in the designer.
Thanks in advance to anyone who can help out here. This is "icing on the cake" to some extent, as the core functionality I need is currently in place, but it would be excellent to have this 100% complete.
View 14 Replies
View Related
May 11, 2006
I have code which generates packages programatically, and script task is a part of the control flow. I've succeded to set source code programatically, but I do not know how to put binary code, because I need to have my script task precompiled.
Just setting PreCompile = true does not solve this problem
Thanks in advance.
Borko
View 1 Replies
View Related
Oct 6, 2006
I am pulling info out of MSDB to report on job schedules. As we have a mixture of 2005 and 2000 servers, I am varying my select statement according to the result of
[Code]
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
[/Code]
I have to vary the tables for each condition. Not a problem,
[Code]
if @Version = 9
begin
Select whatever from table1 join table2
end
Else
begin
Select whatever from table1 join table3
end
Problem is that SQL 2005 is trying to verify the existence of the fields in the tables on compile (it knows that the table, for example, sysjobschedules exists in 2005, but the field "Name" doesn't - although it does in 2000) , even after the if statement checking if the version is 8, and SQL 2000 uses 2 tables whilst 2005 needs 3, and the fields I need are on one table in 2000, but in a different table in 2005.
SQL 2000 is fine with it - it just does it.
Syntax checking is fine.
Just to clarify, here is my code:
[Code]
Declare @Version Char(1), @CurrentDate datetime
Set @CurrentDate = GetDate()
Create Table #TempSchedDetails
( Server VarChar(30)
,CurrentDate VarChar(19)
,JobName VarChar(80)
,ScheduleName VarChar(80)
,Freq_Type Char(1)
,Freq_Interval VarChar(2)
,JobEnabled Bit
,Freq_Subday_Type VarChar(25)
,ScheduleEnabled Bit
,StartTime VarChar(25)
,EndTime VarChar(25)
)
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
If @Version = '9'
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,GetDate()--@CurrentDate
,J.Name
,SS.Name
,SS.Freq_Type
,SS.Freq_Interval
,J.Enabled
,SS.Freq_SubDay_Type
,SS.Enabled
,SS.Active_Start_Time
,SS.Active_End_Time
From msdb..SysJobs J
Left Outer Join msdb..SysJobSchedules JS on J.Job_Id = JS.Job_Id
Join msdb.dbo.SysSchedules SS ON JS.schedule_id = SS.Schedule_Id
Order By J.Name, Active_Start_Time
End
Else
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,@CurrentDate
,J.Name
,S.Name
,S.Freq_Type
,S.Freq_Interval
,J.Enabled
,S.Freq_SubDay_Type
,S.Enabled
,S.Active_Start_Time
,S.Active_End_Time
From MSDB..SysJobs J
Left Outer Join MSDB..SysJobSchedules S On J.Job_Id = S.Job_Id
Order by J.name, Active_Start_Time
End
[/Code]
Any ideas as to how to turn the precompile checking off in 2005?
View 3 Replies
View Related
Nov 9, 2007
I have installed the Report Execution Sample reports and with it came the RSExecutionLog_Update.dtsx and instructions to enable it in a SQL Agent job. I have followed the instructions, however, where do I set the PreCompile property to false? Is there a way to pull in a dtsx file into BIDS?
View 7 Replies
View Related
Jun 8, 2007
Hi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!
View 11 Replies
View Related
Aug 24, 2006
I have a script task that I've created that just displays a MsgBox as listed in Professional SQL Server 2005 Integration Services in chapter 4. The problem is that when I exit the VSA design tool there is a red "X" on the task that says in a popup:
"The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE..."
I go back into the script design, and the code is there, and the PreCompile propterty IS set to True. Attempting to EXECUTE the task only results in a similar error, just more verbose without actually giving any additional insight.
I've read the thread on where the VSA code is deleted on closing.. but my code is still there.. it just isn't seeing the binary code (if it actually exists).
Ideas, comments or snide remarks anyone?
- Mark
View 10 Replies
View Related
