Package runs fine in Debug.. fails outside of debug

I created a package that runs fine while in debug... but when I run outside of debug (just choosing Debug.. Start without debugging) I get an error of:

Message: The task "Formulate SQL Query and Destination" cannot run on this edition of Integration Services. It requires a higher level edition.

I am on SP1 on my workstation and the only SSIS server I have. I am not doing anything fancy in my script.. I have created other packages and ran outside of debug mode without issue. I did uninstall SQL2005 and SSIS from my workstation as it was eating up too many resources.

The script I am running is (removed some of the SQL etc to shorten it):

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

' Create the SQL

Dim SQL As String

SQL += "SELECT "

SQL += " -- Lots of stuff"

SQL += "FROM "

SQL += CStr(Dts.Variables("gvSourceTableName").Value) & " "

SQL += "WHERE "

SQL += " stuff = '" & CStr(Dts.Variables("gvCurrentSymbol").Value) & "' "

SQL += "ORDER BY date"

Dts.Variables("lvSQLQuery").Value = SQL

' MsgBox(CStr(Dts.Variables("lvSQLQuery").Value))

' Create the filename

Dim Dir As String

Dim FileName As String

Dim FullFileName As String

Dir = CStr(Dts.Variables("gvDestinationRoot").Value) & "\" & CStr(Dts.Variables("gvRunID").Value) & "\"

FileName = CStr(Dts.Variables("gvCurrentSymbol").Value) & "--.txt"

FullFileName = Dir & FileName

If Not Directory.Exists(Dir) Then

Directory.CreateDirectory(Dir)

End If

Dts.Variables("gvDestinationFile").Value = FullFileName

' MsgBox(CStr(Dts.Variables("gvDestinationFile").Value))

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

FYI.. tried reinstalling 2005 SP1 on my workstation... still receiving the error message.|||This looks familiar. Try going to the SQL Server 2005 installer and install Integration Services on your local machine. The messages I've received that were similiar to this were solved by that. I believe you have the designer installed on your machine, but not the integration services engine. Good luck.|||Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.|||

Chris Honcoop wrote:

Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.

You're welcome. You know, you'd think that a better error message could be raised... maybe something that actually says you don't have SSIS installed... oh well.

Package randomly stops

I have a very weird issue in my latest package. I run it and it just randomly stops. When I watch it in debug mode or not in debugging a command prompt window will flash for an instant (too fast to read) and then the package will stop. It stops inside of a for each loop and the "on completion" is never fired for the loop. I never receive any errors - its just like someone hit the stop button. Sometimes it will process hundreds of files before stopping, other times only a few. (And its not stopping on the same file each time.. it doesn't appear to be related to the file at all)

Any ideas what could be going on? How to troubleshoot?

The process might have crashed (the console window is SqlDumper collecting the crash information). Check if C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\ contains any dumps.
Also see this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541734&SiteID=1
Make sure you install the SP1, and if the problem still occurs, contact product support services.|||

Yes that folder contains files from the times it stopped - what can I read them with?

That thread mentions turning on error reporting - do you do that on the server or my dev workstation? (and where do I find that setting?)

Another problem I have (maybe related?) is that about once a day visual studio will just "disappear" while I am working on a package .. (or sometimes I am not even working on it) however there are no files for those occurances.

Yeah, I've been using SP1 for quite some time.

Thanks
Chris

|||

Chris Honcoop wrote:

Yes that folder contains files from the times it stopped - what can I read them with?

That thread mentions turning on error reporting - do you do that on the server or my dev workstation? (and where do I find that setting?)

The files are minidumps that can be read by debuggers, like Visual Studio. I would not recommend you to try to read them - they are meant for product devepers, not for users (unless you've built a custom task or transform and suspect it might be involved in the crash).

Use SQL Server Error and Usage Reporting tool to enable "Send error reports..." option - this will allow development team and product support to analize the problem and likely provide a fix in next SP. If you need solution sooner, contact Product Support Services.

|||The only custom objects I have installed is the RegEx one available from microsoft download - however I am not using it in this package (as it wouldn't work unless I was running the package from my workstation). I did uninstall it - however its still in my list of available objects if I go into "choose items..."|||

Well I ran the package directly on the server and it crashed as well so it doesn't appear to be something specific to my machine.

Overview of the dataflow component that fails is I have a flat file that has data in the header I need to keep and tack on each row. (see my thread on cross join) as well as one set of meta data that determines which rows in the file I need to keep. So I take the file and split it four ways - one of the split is basically all data except the metadata. I join one set of metadata to the main data to retrieve the rows of data I need. Then I also add a column to each datastream and hardcode it to 1 and join all the metadata back to the main datastream (since there is no cross join function).

Funny thing is I have been watching what it looks like when it crashes and the file and the split always work but the sorting before joining back into the merges or the merges themselves is where it seems to die (if indeed the graphical view at the time of crash is correct).

How do I know if it is sending the crash info to msft? Its crashed 5 times since turning that on and only once did it bring up a box saying visual studio encountered an error.

|||

Tried some more things as well... still random crashes: I've remade the package from scratch, restructured the dataflows 4 or 5 different way.. still random "dissappearing act" and memory dump to that location.

|||Please contact product support services - they'll be able to analize the crash dumps and provide a solution for the problem, or temporary workaround until the full solution is developed.

It is hardly possible to analize crashes via forum.

Package Configuration in Dev enviornment

Am I missing something, I can't find any way to specific the XML configuration file when I try and run my package in Debug of BIDS. My OLE DB connections are all failing due to a lack of a password. I can set the password in BIDS but its forgotten as soon as I start Debug or build the solution. The only way I seem to be able to run my package (now that I have enabled configurations) is to de-select the enable configurations box.

I've tried putting a configuartion file in the Deployment folder, the bin folder and the project root folder all to the same result.

Unless you are using Indirect Configurations (http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx) the path to the .dtsconfig file is stored in your package so you have to make sure that that is where the file resides.

Its got nothing to do with the Deployment, bin or project root folder.

-Jamie

|||

The Indirect Configurations look handy I think I might try and change my package to use those.

So I finally figured out what was going on, this was driving me crazy. So when using the Package configuration wizard, in the specify configuration settings directly, I had typed a filename. I had clicked on the Browse button to see that it was looking into the project directory for the default directory on the file dialog. Apparently I never used the Save button while trying to set this up. I just assumed that was the directory and thus I never had the fully qualified path to the file. I had redone this while searching for a solution and even then, with the filename in the wizard text box I had clicked Browse, when I saw it was the directory where the file actually was I think I clicked Cancel.

So now I'm able to read the values. One error left and my first package is deployed, this might be a good friday after all! (edit: *no pun was intended*)