Script works in DTS, but has problems as a Stored Procedure

I have a script that builds multiple tables and then builds tables from those tables, etc..

Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence.

As a result, some of the tables are blank when this script is run as a stored procedure.

Do I need to use transactions to prevent this compilation problem, or is there an easier setting that I can use to keep everything in the original sequence?

Thanks in advance.Anyone have any ideas on this? I can't use "GO", but I am still still having this compilation issue.sql

Script Task working in Visual Studio but not when the package is run by a job?

I have a script that changes the name of a file after a data upload. The script works fine if I execute the package in Visual Studio but when I run the file package from a SQL server job it does not rename the file. The data does get uploaded it just does not run the final script.

Any help would be appreciated.

Steve

Have you enabled logging to get the error?

The problems when running under Agent are mostly related to authentication and permissions issues - as the job probably runs under different credentials compared to interactive execution. Your options are either configure job to run under your account, or fix the permissions to allow job account to perform the operation that is failing.

See this KB for troubleshooting steps:
http://support.microsoft.com/kb/918760

Script Task Not Updating Package Variable

I am trying to update a package variable. The package consists only of a script task and a package user variable. I have included the variable, myVar (scope: package; type: string), in the ReadWriteVariables property of the script task.

The only code I have used, in Public Sub Main, is:

Dts.Variables("myVar").Value = "2"

The package runs successfully but the variable does not change. I thought that maybe the underlying value really does change even though the value as seen in the package variables window does not (I tested this in another package/solution but it does not seem to - not even during runtime).

I also tried running the variabledispenser method but this resulted in the package running continuously until I stop debugging.

Any suggestions greatly appreciated.

Regards,

Puzzled Again

Hi, Create a local (within script) variable that is a copy pacakge. The following is how I populate a variable in a parent package(failflag), from a variable in a child package(childfailvalue). This script in the child package.

Dim localfailflag As Variable = Dts.Variables("failflag")
Dim localchildfailvalue As Variable = Dts.Variables("childfailvalue")
localfailflag.Value = localchildfailvalue.Value

|||

Thanks Craig. I tried your code but it didn't work...probably because I didn't include other parts of code that were required.

I changed the routine to using an Execute SQL Task to update the package variable and this seems to have worked.

Regards,

Puz

Script Task Hang - Had to Re-Compile

Greetings,

This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.

There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?

Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.

Thanks

Read the first post at the top of this forum.|||

Thanks Phil,

Just wanted to give some details about what I understand was our situation here.

We have had SQL Server 2005 SP2 for several months. This morning there was a big push which included Windows SP2, .NET 2.0, and .NET 3.0. So it doesn't surprise me that I'd have to recompile, but according to the article referenced at the top of this forum, it seems that SQL SP2 should have taken care of this. Possibly it was our unique sequence of updates here.

|||I'm surprised .Net 2.0 wasn't already installed. It should've been, I believe. You might have to reinstall SQL Server SP2 to reap its benefits.

I'm not entirely sure on the whole deal. That sounds like a pretty major update though.|||

Yes I was thinking the same thing, that the DBA must have been mistaken when he told me that. I thought .NET 2 was installed with SQL 2005, a prerequisite. Will probably just wait to see if it happens again, and if so then reinstall as you suggested.

Thanks for for your time on this

Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.

sql

Script Task - Am I leaving any resources open?

I've got a service that waits for a file to drop and then calls a ssis package that then gets a date from the first line of the file, sets it to a variable, then imports the rest of the file using the flat file import through a data flow task.

Occasionally the script task fails with this error message:

The script threw an exception: Access to the path 'C:\path\flatfile.txt' is denied.

Here is the code of my script:

Public Sub Main()
'
' Add your code here
'
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("User::FullFilePath", vars)

Dim filepath As String = vars("FullFilePath").Value.ToString()

vars.Unlock()

Dim file As File
Dim reader As StreamReader

reader = file.OpenText(filepath)

Dim line As String
line = reader.ReadLine()

reader.Close()

'MsgBox(line.Substring(29))

Dim asofdate As String
asofdate = line.Substring(29, 5)
asofdate = asofdate + "20" + line.Substring(34)

'MsgBox(asofdate)

Dim writeVars As Variables
Dts.VariableDispenser.LockOneForWrite("User::AsOfDate", writeVars)
writeVars(0).Value = asofdate

'MsgBox("done")

writeVars.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Does anyone see anything that I'm not. I believe i'm closing all necessary resources and streams. Does anyone have suggestions.

Thank you very much in advance.

Is it possible that your service calls the package multiple times simultaneously, or that the package could be executed before the drop is completed? The access violation in either of those cases would be due to the file still being locked by something else (e.g. the process writing the file, or the first package processing the file).

If neither are the case, then you could try a small re-write to see if it helps:

Replace:

Dim file As File
Dim reader As StreamReader

reader = file.OpenText(filepath)

Dim line As String
line = reader.ReadLine()

reader.Close()

With:

Dim line As String

Using reader as new StreamReader(filepath)

line = reader.Readline()

reader.Close()

End Using

HTH,

Patrik

|||Thanks for your response.

After banging my head against the table for a day i realized that the reason I was having issues is because the package in question was running as a service. The service was running under a particular user or usergroup. This usergroup was not one that had access to the files being run through my package, and therefore the package had its access deined by the OS.

In the interest of helping others, I'll leave this post up, even though it should have been one of the first things I tried.

Script task

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

Natali Rozin wrote:

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

If you have compiled them into assemblies then you can reference them.

You should read this though:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

|||

Natali Rozin wrote:

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

Natali,

You should put it into the appropriate .Net Framework version folder (I always just pick the highest). e.g.:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

My apologies, the post I linked to above does not explain this does it?

-Jamie

|||

Thank you - all are working!

The problem was I put mine into C:\WINDOWS\Microsoft.NET\Framework\v3.0 and do not have VS SDK yet.

In C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 works well!

thanks again

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script errors in SSIS

I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task 1

Could you have a database named '2003', maybe it's being converted to an integer and then when building the sql string dynamically it is failing because it needs to be of a character data type.

Do you have the 'resultset' property of the execute sql task set to 'none'?

Does that code work when executed from within management studio?

Why don't you try putting your code into a stored procedure and call the stored proc from the execute sql task

|||-Its very possible that a database could be named a number, how can I remedy this situation?
-The resultset is properly set to none
-The code works from within the management studio
-Can I just create an sp that the SSIS package calls from the repository server? I don't want to have to create the sp on each server.

Thanks for your help
-Kyle
|||

Try substituting this portion of the script.

Code Snippet

from (
select b.name as USERName, c.name as RoleName
from [' + @.dbName+'].dbo.sysmembers a '+char(13)+
' join ['+ @.dbName+'].dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join ['+@.dbName +'].dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

This will take care of any database names that throw errors.|||SP_HELPLOGINS is the answer.
-Kyle

Script errors in SSIS

I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task 1

Could you have a database named '2003', maybe it's being converted to an integer and then when building the sql string dynamically it is failing because it needs to be of a character data type.

Do you have the 'resultset' property of the execute sql task set to 'none'?

Does that code work when executed from within management studio?

Why don't you try putting your code into a stored procedure and call the stored proc from the execute sql task

|||-Its very possible that a database could be named a number, how can I remedy this situation?
-The resultset is properly set to none
-The code works from within the management studio
-Can I just create an sp that the SSIS package calls from the repository server? I don't want to have to create the sp on each server.

Thanks for your help
-Kyle
|||

Try substituting this portion of the script.

Code Snippet

from (
select b.name as USERName, c.name as RoleName
from [' + @.dbName+'].dbo.sysmembers a '+char(13)+
' join ['+ @.dbName+'].dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join ['+@.dbName +'].dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

This will take care of any database names that throw errors.|||SP_HELPLOGINS is the answer.
-Kyle

script component not executed

Hi

I have a ssis project that contains a parent package and 2 child packages. The parent package loads data from multiple flat files into a database and then kicks off the 2 child packages using separate execute package tasks.

The child package has a data flow.Within the data flow data is extracted from a database. The data is transformed using a script component and then loaded into a second database.

The problem I have is that the second child package is not working. It appears as if the data is being extracted fine.However the script component does not seem to be being executed so the columns that are being transformed are not being changed and so the write to the database fails. When I send the error rows to a database table with all the fields varchar(200) the write completes but the transformed columns are blank.

Also if I put a message box or ComponentMetaData.FireInformation in the script component I get no output.

However when i run this project on my development machine it runs fine but when I run it on the staging server it gives the problems explained above.

Any ideas please?

Thanks

G

You have two child packages. Do they both have scripts? Do the child packages run in-process or out-of-process?

How are you executing the packages in each environment? Any differences there?

Script Component failed validation and returned validation status "VS_ISBROKEN". Error

I have been running this package before (with no errors) but now I get this error message. Is it my enviroment that is broken? All my Script Compnents end up with this error code.

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Script Component" (53)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "src_abs_budg_sales_wc.dtsx" finished: Failure.

The CLSID quoted in the error message is the Visual Studio for Applications runtime, progid "VsaVbRT.8.0," described as "Microsoft Visual Basic Scripting Engine." It looks like the corresponding COM DLL is VsaVb7rt.dll. So it looks like your VSA installation is corrupted or incomplete. Can you try re-registering that DLL using regsvr32.exe?|||

It was my installation that was corrupted, thanks

Script component Errors

How come when i compile the package and try and run the package outside of the Developement enviroment(Visual studio) it complains about all my ssis scrip tasks. It brings up an error saying, it can not run under this edition of Integration services. It requires a higher level.

Is there another type of ssis that i do not know about, can you supply me a URL to help solve this problem

Thanks

Well, what version of SSIS are you using outside of the Development environment? And have you installed SSIS there?|||

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

This is the version, it is the version that comes standard with the SQL 2005 server

|||Are you using Fuzzy Lookups, Fuzzy Grouping, or the Text mining tasks? These are only available in the Enterprise Edition of SSIS.|||And you still have to install the SSIS client software. Even though it comes with SQL Server, there is still a client part that needs to be installed.|||

Yes there is fuzzy logic.

I do not know what has been installed,

can you tell me how to find out weather the client software has been installed

|||

If you have not installed SSIS correctly, then any task or component can produce an error "product level is insufficient..."

http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

The Fuzzy Tasks however are examples of those that really mean it, you need Enterprise Edition (or Developer).

To check which edition of SSIS you ahve installed look in the registry-

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

Look at the Values Edition and/or EditionType values.

Script component and variable!!

Hi All,

I have developed a simple package utilizing script component and variable. What I am trying to do is declare a variable in the parent package that can be change at run time to either 1 or 0. If 1 I want the package to succeed and branch off to run some child packages and if 0, then branch the other way and run another child package. In ether way, I want to be able to load one of the two child packages and not both.

I I declare a variable in my parent package like IsExist, int32 with default 0.

Then I have this code thanks partly to Jamie Thomson in " SSIS: Writing to a variable from a script task"

Public Sub Main()

'

' Add your code here

'

Dim t As Integer

Dim vars As Variables

Dts.VariableDispenser.LockOneForWrite("IsExists", vars)

t = CInt((Dts.Variables("IsExists").Value))

'Dts.Variables(IsExists).Value = t

If t >= 1 Then

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

I also place the variable name in the ReadWriteVariables in the script component.

The problem now is it is always failing. It stopped at the script task and fail. This is not what I want. I want the package to execute another task upon failure. Anyone knows what I am doing wrong?

Hi,

You can achieve this without using a script task. Simply use expressions on your precedence constraints to check the value of IsExists. Here's how: http://www.sqlis.com/default.aspx?306

Oh, and you may want to make IsExists a boolean variable as well.

-Jamie

|||

Thanks alot Jamie. I tried using the Precedence constraint by seting the @.IsExists to 1 on one flow and 0 on the other but it is always running the one for 0. This is without using script task.

Again, when I use a script task with the following code, it gave me bunch of errors:

Public Sub Main()

Dim vs As Variables

'We need to lock the variables so we can read it without anything else changing it
Dts.VariableDispenser.LockOneForWrite("IsExists", vs)

'Assign it a value
vs.Item("IsExists").Value = (Dts.Variables("IsExists").Value)

'remember to unlock the variable now
vs.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Thanks

Omon

|||

OK, so you're using the script task to set the variable as well. I see.

What errors are you getting?

|||

Thanks jamie,

Yes. I used below script to set the value:

Dim vs As Variables

Dim t As Boolean

'MsgBox("variable_Passed)

t = CBool((Dts.Variables("IsExists").Value))

If t = True Then

'MsgBox("Condition_Stage")

Dts.TaskResult = Dts.Results.Success

'MsgBox("Success")

Else

Dts.TaskResult = Dts.Results.Failure

'MsgBox("Failure")

End If

So I was getting conversion error and when I placed CBool in front of the IsExists like: CBool((Dts.Variables("IsExists").Value)) it worked. I had to set

LockOneForWrite in the script task instead of using the code.

Again, I also had to select Logical OR in the presedent editor . This fix my problem.

I think I am all good for now.

Thanks very much.

Omon