package variables passed to Execute SQL Task

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.

Verified I'm using an OLE DB valid connection.

Verified the variables are properly mapped in the parameters mapping section of the task properties.

Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

Changed the Bypassprepare property to 'True'.

Breakpoints and watch windows to verify the variable values.

I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

Also, the code for the first package that holds the parameter is:

DECLARE @.DatabaseName varchar(100)
SET @.DatabaseName = ?
DECLARE @.SPID smallint

And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

The Parameter Mapping has the following values:

User:atabasename Input Varchar 0

And the Variables set up has the following values:

databasename package string DB_NAME

Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

Thanks!|||

You could use an alternative approach to built the SQL statement. Create a new variable, let's say SQLStatement; then use an expression (set EvaluateAsExpresion=True) in that variable to form the SQL statement. something like:

"Select * from " + @.[User::Variable1]

Then in the execute SQL task set SQLSourceType=Variable and select the variable name in SourceVariable drop-down list.

I like this approach because you an see easily how the sql statement is being built.

|||Thanks for the quick reply Rafael. I am unclear as to how this would resolve the issue. The package contains three lengthy Execute SQL tasks, and the package needs to be replicated for several different databases that are independently accessed in a development environment. My original idea was to use the package variable to simply change the DB name. Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task? If so, it's definitely worth a try, but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Thanks again!
|||

jheywood wrote:

Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task?
Thanks again!

Yes, that is my idea. That give you the benefit of testing the expression and check how exactly the sql statement is gong to be sent.

jheywood wrote:

but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Not sure..did you try changing the value of ByPassPrepare in the execute sql task?

|||Yes, I tried that too. I was hoping it was maybe just some stupid syntax problem that I might have overlooked with the parameter or the variable, but I guess not.

I'll try the other method since that definitely seems like a viable option. Thanks so much for the help!

Please let me know if you think of anything else about the var use, or need more information. I'm really stumped on it.
sql

Package Synchronization?

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Assert.True wrote:

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Control access to the package so that it DOESN'T get fired simultaneously.

-Jamie

|||You can create Agent Job with your package. The Agent ensures only one instance of a particular Job is executed at the same time. Of course, it will be your responsibility to make sure the package is only executed via Agent (from ASP.NET - use sp_start_job SQL stored proc).

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

Somewhere there will be an error, and without that I have no idea. Try using DTEXEC through a job.

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

I need HELP please :)|||

We created a package which read a flat file from another server and imported it into a table. Our package would run, but the job would not.

We found that we had to change two options - one was the location of the flat file. Our package was looking at a specific directory like "J:\Import Files\file.txt". We replaced the specific drive letter with the shared directory alias \\ServerName\Alias\Import Files\file.txt.

The second change was to alter the package to "Rely on Server ..." instead of "Encrypt Sensitive With User Key".

These two changes allowed Sql Agent to run the job.

|||

Hello,

thank you for answering, I tried your solution but no effect appears.

Actually your solution enable to execute the package in a step with a job. It seems to not fail.

The package executes through DTEXEC works fine and my destination table is filled.

When I use the same command line with a job, it works but my table is still empty. No new lines from the package !

How is it possible considering I use the same command line ?

sql

Package Execution with Custom UI

Hi:

I need a user to pass an input to a package from a VB form, and then want to show progress/errors in the form.

I have been able to use Application. LoadFromSqlServer and I then set pkg.Variables.(MyVariable) to the required value before calling Execute.

Works fine so far.

I dont know how to show progress. The DTSExecResult just returns a Cryptic Success /Failure status message.

Is there a way to log the errors if any to my Windows form? I have seen samples on Console Apps(see below) but I would prefer to show it in my Windows form in a text Box or something, appending each error result to the text.

TIA

Kartik

Code Snippet

Class EventListener
Inherits DefaultEvents

Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _
ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _
ByVal helpFile As String, ByVal helpContext As Integer, _
ByVal idofInterfaceWithError As String) As Boolean

' Add application�Cspecific diagnostics here.
Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description)
Return False

End Function

End Class

That's the code you want - you need to capture the events, and write to a text box instead of the console.

Package Execution takes 100% CPU

Hi all,

I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).

Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?

Try to redefine your SSIS package...

Regards!

|||Is SSIS taking up the processor, or is it the relational engine? I assume it is the latter.

First off, if SQL Server is sharing the box with other processes, you need to limit the max amount of memory it can take. Otherwise, it is likely to hog it all and starve the other processes.

Second, if you have a lot of rows to update you should bulk insert the changes to a staging table and then run a single UPDATE statement joined between the staging and the target table. Using the OLE DB Command, SSIS can only update a single row at a time, which is very inefficient.

Finally, for a large table you need to be very careful about your indexes. A table with a lot of indexes will be much slower to update than one without. Ideally, you only want one index on this table, the one that will allow you to find the row to update. Obviously, you'll need to balance this with the intended use of the table.

|||Thanks for your answer.

Package execution stability ?

I am running into some issue that i have found any good clue on this forum... although have seen a few threads dicussions. I have a master package which invokes a dozen of child packages.

1) If I only open master package inside IDE, I am keeping getting the following error when i run the master packages inside Visual Studio 2005 IDE.

The connection type "FLATFILE" specified for connection manager "XXXXX Flat File Destination Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name

2) If I opened up all child packages inside IDE, the master package run fine most of time.

Any suggestions?

Are you using a connection type of "flat file" or just the "file" connection manager type?|||

See below please

|||

It is a flat file type: Here is the error details:

Code Snippet

Error: 0xC0014005 at : The connection type "FLATFILE" specified for connection manager "Load Ready Output Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process Provider Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\Provider.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

But I also get this Error in the same time.

Code Snippet

Error: 0xC0014005 at : The connection type "OLEDB" specified for connection manager "Target Database Connection" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Target Database Connection{CBD29A65-2BBC-4BB6-AED" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process BenefitPlan Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\BenefitPlan.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

SO IT SEEMS THAT THE CONNECTION TYPE IS NOT RECOGNIZED FOR SOME WEIRD REASONS BEHIND....

Help..........

|||

Try to look at this thread and see if the answer solves your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1

Thanks,

Ovidiu Burlacu

|||

Thanks. Unfortunately, the link did NOT fix my problem.

I followed Mike's suggestion by creating the application and a non-administrative account. I did not see any incorrect registry key dosplayed. Help !!!

|||What version of SSIS do you have? RTM? SP1? SP2?

Seems like something is corrupt. Any changes to the system recently?|||

I have tried packages on various machines (RTM on Windows 2003, SP2 on Windows xp Pro ) and the error is repeatable on any machines.

Here is the version info for SSIS from "About" on Visual Studio 2005.

Microsoft SQL Server Integration Services Designer Version 9.00.3042.00

|||Microsoft follow-up:

I can create a simple package (OLE DB Source -> Flat File Destination) and recreate this error by opening the resulting .dtsx file in my favorite text editor. When doing so, searching by CaSE and replacing "FLATFILE" with "FLATFILE2" yields the same error message as Steve has indicated. Obviously, FLATFILE2 is not a valid connection manager type. So, with that said, where is the validation occurring to determine if the connection manager type is valid or not? And what can Steve do about it?|||

Thanks Phil for attention.

Here is complete info from package log in attempt to get help here. Any help will be greatly appreciated.

Code Snippet

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:04:56 PM,7/17/2007 3:04:56 PM,0,0x,Beginning of package execution.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnTaskFailed,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,0,0x,(null)
PackageStart,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:22 PM,7/17/2007 3:05:22 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:29 PM,7/17/2007 3:05:29 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:30 PM,7/17/2007 3:05:30 PM,0,0x,Beginning of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:31 PM,7/17/2007 3:05:31 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:36 PM,7/17/2007 3:05:36 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

|||

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

|||

Saurabh Kulkarni wrote:

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

He's followed the steps in that KB article already and no "bad" registry keys were identified.

|||[Microsoft follow-up]
again.

Come on guys.|||

something looks funny with this text from the error message:

Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node

almost as if the xml parsing has gone bad. the guid is chopped off and the string starts with -1.

do you have any objects with odd characters in the names. characters that could mess up a parser like < > " , - etc. ?

|||

Thanks for Microsoft's help coming out...

No, I checked packages and do not have/see any objects that contains weird name etc. Please take a look at my longer posting above.

The observation is that all of packages run successfully sometimes, while failed on the other times. It is not stable. I repeatedly re-run my packages above yesterday without any failure. But today it starts to fail occasionally.

If the packages contains object names with special characters, the packages should always fail.

I would be very willing to send you via email the four small and simple packages.

Steve

sql

Package execution stability ?

I am running into some issue that i have found any good clue on this forum... although have seen a few threads dicussions. I have a master package which invokes a dozen of child packages.

1) If I only open master package inside IDE, I am keeping getting the following error when i run the master packages inside Visual Studio 2005 IDE.

The connection type "FLATFILE" specified for connection manager "XXXXX Flat File Destination Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name

2) If I opened up all child packages inside IDE, the master package run fine most of time.

Any suggestions?

Are you using a connection type of "flat file" or just the "file" connection manager type?|||

See below please

|||

It is a flat file type: Here is the error details:

Code Snippet

Error: 0xC0014005 at : The connection type "FLATFILE" specified for connection manager "Load Ready Output Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process Provider Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\Provider.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

But I also get this Error in the same time.

Code Snippet

Error: 0xC0014005 at : The connection type "OLEDB" specified for connection manager "Target Database Connection" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Target Database Connection{CBD29A65-2BBC-4BB6-AED" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process BenefitPlan Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\BenefitPlan.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

SO IT SEEMS THAT THE CONNECTION TYPE IS NOT RECOGNIZED FOR SOME WEIRD REASONS BEHIND....

Help..........

|||

Try to look at this thread and see if the answer solves your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1

Thanks,

Ovidiu Burlacu

|||

Thanks. Unfortunately, the link did NOT fix my problem.

I followed Mike's suggestion by creating the application and a non-administrative account. I did not see any incorrect registry key dosplayed. Help !!!

|||What version of SSIS do you have? RTM? SP1? SP2?

Seems like something is corrupt. Any changes to the system recently?|||

I have tried packages on various machines (RTM on Windows 2003, SP2 on Windows xp Pro ) and the error is repeatable on any machines.

Here is the version info for SSIS from "About" on Visual Studio 2005.

Microsoft SQL Server Integration Services Designer Version 9.00.3042.00

|||Microsoft follow-up:

I can create a simple package (OLE DB Source -> Flat File Destination) and recreate this error by opening the resulting .dtsx file in my favorite text editor. When doing so, searching by CaSE and replacing "FLATFILE" with "FLATFILE2" yields the same error message as Steve has indicated. Obviously, FLATFILE2 is not a valid connection manager type. So, with that said, where is the validation occurring to determine if the connection manager type is valid or not? And what can Steve do about it?|||

Thanks Phil for attention.

Here is complete info from package log in attempt to get help here. Any help will be greatly appreciated.

Code Snippet

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:04:56 PM,7/17/2007 3:04:56 PM,0,0x,Beginning of package execution.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnTaskFailed,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,0,0x,(null)
PackageStart,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:22 PM,7/17/2007 3:05:22 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:29 PM,7/17/2007 3:05:29 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:30 PM,7/17/2007 3:05:30 PM,0,0x,Beginning of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:31 PM,7/17/2007 3:05:31 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:36 PM,7/17/2007 3:05:36 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

|||

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

|||

Saurabh Kulkarni wrote:

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

He's followed the steps in that KB article already and no "bad" registry keys were identified.

|||[Microsoft follow-up]
again.

Come on guys.|||

something looks funny with this text from the error message:

Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node

almost as if the xml parsing has gone bad. the guid is chopped off and the string starts with -1.

do you have any objects with odd characters in the names. characters that could mess up a parser like < > " , - etc. ?

|||

Thanks for Microsoft's help coming out...

No, I checked packages and do not have/see any objects that contains weird name etc. Please take a look at my longer posting above.

The observation is that all of packages run successfully sometimes, while failed on the other times. It is not stable. I repeatedly re-run my packages above yesterday without any failure. But today it starts to fail occasionally.

If the packages contains object names with special characters, the packages should always fail.

I would be very willing to send you via email the four small and simple packages.

Steve

Package execution stability ?

I am running into some issue that i have found any good clue on this forum... although have seen a few threads dicussions. I have a master package which invokes a dozen of child packages.

1) If I only open master package inside IDE, I am keeping getting the following error when i run the master packages inside Visual Studio 2005 IDE.

The connection type "FLATFILE" specified for connection manager "XXXXX Flat File Destination Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name

2) If I opened up all child packages inside IDE, the master package run fine most of time.

Any suggestions?

Are you using a connection type of "flat file" or just the "file" connection manager type?|||

See below please

|||

It is a flat file type: Here is the error details:

Code Snippet

Error: 0xC0014005 at : The connection type "FLATFILE" specified for connection manager "Load Ready Output Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process Provider Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\Provider.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

But I also get this Error in the same time.

Code Snippet

Error: 0xC0014005 at : The connection type "OLEDB" specified for connection manager "Target Database Connection" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Error: 0xC0010018 at : Error loading value "-1Target Database Connection{CBD29A65-2BBC-4BB6-AED" from node "DTS:ConnectionManager".

Error: 0xC00220DE at Process BenefitPlan Data: Error 0xC0010014 while loading package file "C:\SSIS\newDas\BenefitPlan.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.

SO IT SEEMS THAT THE CONNECTION TYPE IS NOT RECOGNIZED FOR SOME WEIRD REASONS BEHIND....

Help..........

|||

Try to look at this thread and see if the answer solves your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1

Thanks,

Ovidiu Burlacu

|||

Thanks. Unfortunately, the link did NOT fix my problem.

I followed Mike's suggestion by creating the application and a non-administrative account. I did not see any incorrect registry key dosplayed. Help !!!

|||What version of SSIS do you have? RTM? SP1? SP2?

Seems like something is corrupt. Any changes to the system recently?|||

I have tried packages on various machines (RTM on Windows 2003, SP2 on Windows xp Pro ) and the error is repeatable on any machines.

Here is the version info for SSIS from "About" on Visual Studio 2005.

Microsoft SQL Server Integration Services Designer Version 9.00.3042.00

|||Microsoft follow-up:

I can create a simple package (OLE DB Source -> Flat File Destination) and recreate this error by opening the resulting .dtsx file in my favorite text editor. When doing so, searching by CaSE and replacing "FLATFILE" with "FLATFILE2" yields the same error message as Steve has indicated. Obviously, FLATFILE2 is not a valid connection manager type. So, with that said, where is the validation occurring to determine if the connection manager type is valid or not? And what can Steve do about it?|||

Thanks Phil for attention.

Here is complete info from package log in attempt to get help here. Any help will be greatly appreciated.

Code Snippet

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:04:56 PM,7/17/2007 3:04:56 PM,0,0x,Beginning of package execution.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073659899,0x,The connection type "FLATFILE" specified for connection manager "MembersWithUpdates" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073676264,0x,Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">MembersWithUpdates</DTS:Property><DTS:Property DTS:Name="DTSID">{D6867FEB-27D8-4576-80CB-A53449" from node "DTS:ConnectionManager".

OnError,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnError,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,-1073602338,0x,Error 0xC0010014 while loading package file "C:\SSIS\DasLoader\Member.dtsx". One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.
.

OnTaskFailed,MyComputerNameG,DomanName\MyUserAccount,Process Member Data,{874B05D3-3ED9-453A-BEF5-2595710C0B04},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:17 PM,7/17/2007 3:05:17 PM,0,0x,(null)
PackageStart,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:22 PM,7/17/2007 3:05:22 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:29 PM,7/17/2007 3:05:29 PM,0,0x,Beginning of package execution.

PackageStart,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:30 PM,7/17/2007 3:05:30 PM,0,0x,Beginning of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,CustomInformationCodeLookup,{C4C5D8F8-CD26-4C94-ADB4-D40607F67D70},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:31 PM,7/17/2007 3:05:31 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,BenefitPlan,{48289F86-1429-44B0-B5F7-295F31B6F39E},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:36 PM,7/17/2007 3:05:36 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,Provider,{6F1B4233-8A08-4688-827F-FB98DC7F975F},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

PackageEnd,MyComputerNameG,DomanName\MyUserAccount,OMXLoadTransformSequencer,{FC99AFBF-316F-458B-B989-30EC8DC97A45},{83B517B1-622A-4015-A99E-64944A225E09},7/17/2007 3:05:47 PM,7/17/2007 3:05:47 PM,0,0x,End of package execution.

|||

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

|||

Saurabh Kulkarni wrote:

Hi Steve,

Just came across this page in Microsoft support site. See if this helps you.

http://support.microsoft.com/kb/913817/en-us

Regards

Saurabh

He's followed the steps in that KB article already and no "bad" registry keys were identified.

|||[Microsoft follow-up]
again.

Come on guys.|||

something looks funny with this text from the error message:

Error loading value "-1Load Ready Output Connection Manager{3A5E4ACC-9BF" from node

almost as if the xml parsing has gone bad. the guid is chopped off and the string starts with -1.

do you have any objects with odd characters in the names. characters that could mess up a parser like < > " , - etc. ?

|||

Thanks for Microsoft's help coming out...

No, I checked packages and do not have/see any objects that contains weird name etc. Please take a look at my longer posting above.

The observation is that all of packages run successfully sometimes, while failed on the other times. It is not stable. I repeatedly re-run my packages above yesterday without any failure. But today it starts to fail occasionally.

If the packages contains object names with special characters, the packages should always fail.

I would be very willing to send you via email the four small and simple packages.

Steve

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manually
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
Thanks
Two possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manuall
y
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
ThanksTwo possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package Error.

Hi everybody,

I have created a package which contains execution of a set of SQL Stored Procedures. I scheduled the job to run every morning at 7:00 AM. I am getting the following error:

Executed as user: APD-DEV-CS517\SYSTEM. The package could not be found. The step failed.

What do you think the problem is?
Thanks again.Looks to me that "the package could not be found." Have you ensured the path is correct?|||Yeah I am sure.
Let me make sure about that and wait till tomorrow morning.

Thanks though,
Murthy here

Overlapping Trigger Precedence

Hi,
What is the execution precedence if there exist more than one update, delete
or insert triggers
I mean if there are more than one type of trigger such as more than one
update trigger.
Also, Can we specify a precedence for them?
and last If we call an update statement in the same update trigger and for
the same table that has this trigger, Is the trigger considers itself, I
mean is it recurs or not, or is it disables itself for that execution period
?.
Thanks in advance...
Emre GuldoganThere is no guarantee as to what order they'll fire in.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Emre Guldogan" <ask me please...> wrote in message
news:e$KbhKMJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Hi,
> What is the execution precedence if there exist more than one update,
delete
> or insert triggers
> I mean if there are more than one type of trigger such as more than one
> update trigger.
> Also, Can we specify a precedence for them?
> and last If we call an update statement in the same update trigger and for
> the same table that has this trigger, Is the trigger considers itself, I
> mean is it recurs or not, or is it disables itself for that execution
period
> ?.
> Thanks in advance...
> Emre Guldogan
>|||You can use sp_settriggerorder to define the first and last trigger to
execute. For the rest the order is undefined. If it matters to you then
put all the logic in one trigger.
David Portas
SQL Server MVP
--|||On Wed, 9 Mar 2005 18:11:48 +0200, "Emre Guldogan" <ask me please...>
wrote:

>What is the execution precedence if there exist more than one update, delet
e
>or insert triggers
>I mean if there are more than one type of trigger such as more than one
>update trigger.
>Also, Can we specify a precedence for them?
Hi Emre,
Already addressed by David and Adam.

>and last If we call an update statement in the same update trigger and for
>the same table that has this trigger, Is the trigger considers itself, I
>mean is it recurs or not, or is it disables itself for that execution perio
d
>?.
There are two options governing this:
1. Recursive triggers. If this is off, a trigger can't fire itself. So
if an update trigger on table AA performans an UPDATE AA command, that
specific trigger is not called. This disables only direct recursion: if
the update trigger on AA performs a DELETE FROM BB statement, and the
delete trigger on BB performs UPDATE AA, the trigger will be called
recursively.
The recursive triggers option is a database setting: it will affect all
triggers in the current database, but it won't affect other databases on
the same server. You use ALTER DATABASE to change it.
2. Nested triggers. If this is off, statements executed from a trigger
don't fire any trigger at all.
The nested triggers option is a server setting: it will affect all
triggers in all databases on the server. You use sp_configure to change
it.
Other options to prevent recursive execution of triggers for specific
triggers only are:
* TRIGGER_NESTLEVEL() - check this to find how many nested trigger
executions there currently are.
* TRGIGER_NESTLEVEL(object_ID('trigger_nam
e')) - check this to find how
many times the specified trigger is currently nested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)