Script tasks don't work in Production

Hallo,

In SSIS Version 9.00.2047.00 I have build a few packages using script tasks. These packages work well in development.

When I try to run them Using DTUtil the systems displays in the Package Excecution Progress-window the message: 'Error: The tasks "reading registry" cannot run on this edition of Integration services. It requires a higher level edition.'

Emptying the script task from its variables and script doesn't make a difference. I did not find an explanation in BOL. Concerning script tasks I only find

The Script task uses VSA as its engine for writing and running scripts. To run a script, you must have VSA installed on the computers where the package runs. But since I even get these messages when I try to run on the machine where I build the package,I guess this can't be the problem.

Any ideas?

Kind regards,

Paul

Check out the solutions by Daniel Read @. http://www.developerdotstar.com/community/node/312

Thanks,
Loonysan

|||It looks like you only installed Tools component on Production server. You need to install SSIS component (a separate checkbox during Setup and different licensing requirements) to run packages stand-alone (outside of the designer). Actually, you may not need to Tools component on the production server at all - Tools are needed to design and debug packages, but for execution the SSIS server component should be enough.|||

Hi Michael,

Thanks for your swift reply.

I admit i'm not a SQL server specilist but while installing SQL Server 2005 from CD (Microsoft SQL Server 2005 Enterprise Edition December 2005) or DVD the option 'Integration services' is disabled. So I can not single it out for installation. . Is there still an other installation dvd I need or can I download the SSIS server component from somewhere? I did not seem to find it in the download center

With kind regards,

Paul

|||What is the OS on the production machine? Server components in Enterprise Edition can only be installed on Server operating system, not on Windows XP or Windows 2000 workstation. For production server, use server OS like Windows 2003 Server.

Script Task errors

I have a custom .dll that I put in MS.Net framework 2.0 + Gac for my development environment to create packages. I have a script task that references this dll and the precompile option is set to "False". When I change the precompile option to "True" and remove the dll from .net framework 2.0, I get errors. My question is why doesn't dtexecui.exe utility check GAC since I have set my precompile property for script tasks from false to true?

Is it possible that the script task is not getting refereshed in the package? I checked the .dtsx in notepad and found the binary listed right below the actual script. Any help would be appreciated.

After changing the PreCompile option to true, please open the script editor and close it. At this moment the precompilation should happen and you should no longer need the .dll in .NET framework folder (well, at least not until you need to modify the script again).

If you are still getting the errors after this, could you be more specific about what errors do you get?

|||

I replced my script tasks and everything works fine. So the only thing I can think of now is that when I started developing the package, we were on June cpt then Sept. CTP. With RTM, I cannot get the package to compile and work without errors unless I remove the script task, readd it, and reset the reference. Any thoughts?

sql

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 isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

Script Component – “extremely long lines of text”

In all my SSIS packages where I have created a Script Component (destination) in my Data Flow and set the PreCompile option to True; the binary of the script is being put in the dtsx fileas a single line. Subsequently every time I try to open the package, Visual Studio warns me that: “Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still wish to open the file?”

Any idea how to fix this?

NOTE: This does not happen for any PreCompiled Script Tasks. For these the binary is broken into multiple lines in the dtsx file.

Thank you for your help,

-Tim

Tim,

Unfortunately this is a hangover from using the VS shell. There's nothing you can do about it.

-Jamie

|||Oddly enough, this bug is listed as "fixed" with a note from an SSIS team:

Resolved as Fixed by Microsoft on 2005-10-18 at 16:20:03
Thanks for your report, we're getting this message removed in a service pack.

Best regards,
Sergei Ivanov
Integration Services Development Team Resolved as Fixed by Microsoft on 2005-11-17 at 12:25:30
Thanks, this is fixed.

Sergei Ivanov
Integration Services Development Team

You can see the full bug report here on the MSDN Product Feedback Ctr. It's BUG ID: FDBK37397.
However, the KB Article for SP1 (KB913090) detailing the fixes seems to make no mention of it, at least none that I could find in a quick passthru. When I can find a machine to test the SP1 CTP, we'll see.
|||

Yes, this is fixed by SP1. AFAIK the KB is not the complete list of fixes.
There is a limitation with the fix though: you need to create new script component with SP1 build of SSIS. The script components created with RTM build still have this issue (this is done so that the precompiled script of these components can be read by RTM build of SSIS, otherwise we would break backward compatibility).

By the way, you don't need SP1 CTP, the final SP1 has been released.

|||I have a package with no script components yet I still get this error message each time I load it. Here is what I have discovered. When I set the ProtectionLevel property to EncryptAllWithPassword I get the error message. When I set the ProtectionLevel to EncryptSensitiveWithPassword the message no longer appears. Any Ideas why?|||

dgrm44 wrote:

I have a package with no script components yet I still get this error message each time I load it. Here is what I have discovered. When I set the ProtectionLevel property to EncryptAllWithPassword I get the error message. When I set the ProtectionLevel to EncryptSensitiveWithPassword the message no longer appears. Any Ideas why?

I would guess that the encrypted results in some rather long lines. AcryptAllWithPassword will result in more encryprion than EncryptSensitiveWithPassword.

Take a look at the contents of the .dtsx file and see if it becomes obviousl I would also guess that the size of the file differs quite a bit as well.

-Jamie

Script Component – “extremely long lines of text”

In all my SSIS packages where I have created a Script Component (destination) in my Data Flow and set the PreCompile option to True; the binary of the script is being put in the dtsx fileas a single line. Subsequently every time I try to open the package, Visual Studio warns me that: “Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still wish to open the file?”

Any idea how to fix this?

NOTE: This does not happen for any PreCompiled Script Tasks. For these the binary is broken into multiple lines in the dtsx file.

Thank you for your help,

-Tim

Tim,

Unfortunately this is a hangover from using the VS shell. There's nothing you can do about it.

-Jamie

|||Oddly enough, this bug is listed as "fixed" with a note from an SSIS team:

Resolved as Fixed by Microsoft on 2005-10-18 at 16:20:03
Thanks for your report, we're getting this message removed in a service pack.

Best regards,
Sergei Ivanov
Integration Services Development Team Resolved as Fixed by Microsoft on 2005-11-17 at 12:25:30
Thanks, this is fixed.

Sergei Ivanov
Integration Services Development Team

You can see the full bug report here on the MSDN Product Feedback Ctr. It's BUG ID: FDBK37397.
However, the KB Article for SP1 (KB913090) detailing the fixes seems to make no mention of it, at least none that I could find in a quick passthru. When I can find a machine to test the SP1 CTP, we'll see.
|||

Yes, this is fixed by SP1. AFAIK the KB is not the complete list of fixes.
There is a limitation with the fix though: you need to create new script component with SP1 build of SSIS. The script components created with RTM build still have this issue (this is done so that the precompiled script of these components can be read by RTM build of SSIS, otherwise we would break backward compatibility).

By the way, you don't need SP1 CTP, the final SP1 has been released.

|||I have a package with no script components yet I still get this error message each time I load it. Here is what I have discovered. When I set the ProtectionLevel property to EncryptAllWithPassword I get the error message. When I set the ProtectionLevel to EncryptSensitiveWithPassword the message no longer appears. Any Ideas why?|||

dgrm44 wrote:

I have a package with no script components yet I still get this error message each time I load it. Here is what I have discovered. When I set the ProtectionLevel property to EncryptAllWithPassword I get the error message. When I set the ProtectionLevel to EncryptSensitiveWithPassword the message no longer appears. Any Ideas why?

I would guess that the encrypted results in some rather long lines. AcryptAllWithPassword will result in more encryprion than EncryptSensitiveWithPassword.

Take a look at the contents of the .dtsx file and see if it becomes obviousl I would also guess that the size of the file differs quite a bit as well.

-Jamie