Failed to schedule DTS in SQL 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

sql

Failed To Save Package File

I am just beginning to work on an ETL project at a new client site using their computers. They are an IT department new to SQL Server. We have SQL Server 2005 up and running, no probkem. But I get that error when I try to add a new package to an SSIS project. The error number is 0x8002801D "Library Not Registered". I did see the post about the MSXML DLL files and when I try to load an existing package I get 3 errors regarding XML - stating that the MSXML binaries may not be installed or registered properly. I searched and found the MSXML3 & 6 DLLs but the MSXML4.DLL appears to be missing. If I repair the installation, will this fix it? BTW - the MSXML 6.0 Parser did show as being installed in Add/Remove Programs.

Thanks for you help!

I won't guarantee that it will fix it, but it's not going to hurt |||

I do not think SSIS uses msxml4.dll. Try registering msxml3.dll and msxml6.dll.

Thanks.

|||Thanks, I will. But I do have a question. My client installed the 64 bit version of SQL Server and the client side tools from the same CD. When I search on the files, I see that both the 32 bit and 64 bit versions of the DLLs are on the system. Should I register the 64 bit DLLs?|||You should register both.

Failed To Save Package File

I am just beginning to work on an ETL project at a new client site using their computers. They are an IT department new to SQL Server. We have SQL Server 2005 up and running, no probkem. But I get that error when I try to add a new package to an SSIS project. The error number is 0x8002801D "Library Not Registered". I did see the post about the MSXML DLL files and when I try to load an existing package I get 3 errors regarding XML - stating that the MSXML binaries may not be installed or registered properly. I searched and found the MSXML3 & 6 DLLs but the MSXML4.DLL appears to be missing. If I repair the installation, will this fix it? BTW - the MSXML 6.0 Parser did show as being installed in Add/Remove Programs.

Thanks for you help!

I won't guarantee that it will fix it, but it's not going to hurt |||

I do not think SSIS uses msxml4.dll. Try registering msxml3.dll and msxml6.dll.

Thanks.

|||Thanks, I will. But I do have a question. My client installed the 64 bit version of SQL Server and the client side tools from the same CD. When I search on the files, I see that both the 32 bit and 64 bit versions of the DLLs are on the system. Should I register the 64 bit DLLs?|||You should register both.

Failed to install: Cumulative hotfix package (build 2153) for SQL Server 2005 (KB918222)

Hi experts!

I try to update SQL Server 2005 SP1 with the Cumulative hotfix package (build 2153). I need to do this, because I get an error this hotfix is said to fix... and of course - the update completely crashes (sending an error report to microsoft).

Here is what the log looks like:

Failed ACL:
SetSecurityFileDescriptor is failed at the error code 5; Converted SDDL: '(A;OICI;FA;;;S-1-5-21-1276437202-3579633377-1796755180-1012)'
Error Code: 0x80077342 (29506)
Windows Error Text: Source File Name: sqlca\sqlsddlca.cpp
Compiler Timestamp: Tue Apr 11 11:45:24 2006
Function Name: ExceptionInSDDL
Source Line Number: 65

- Context --

Setting status of unmanaged components and removing unmanaged resources

MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
Error Code: 29506
MSI (s) (50!B4) [23:18:29:854]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:854]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Transforming table Error.

MSI (s) (50!B4) [23:18:29:870]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (50!B4) [23:18:29:870]: Product: Microsoft SQL Server 2005 -- Error 29506. SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user SQL. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.

Error 29506. SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user SQL. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
<Failure Type='Fatal' Error='29506'>
<EndFunc Name='LaunchFunction' Return='29506' GetLastError='0'>
MSI (s) (50:34) [23:18:29:886]: User policy value 'DisableRollback' is 0
MSI (s) (50:34) [23:18:29:886]: Machine policy value 'DisableRollback' is 0
Action ended 23:18:29: InstallFinalize. Return value 3.
MSI (s) (50:34) [23:18:29:964]: Executing op: Header(Signature=1397708873,Version=301,Timestamp=910146116,LangId=1033,Platform=0,ScriptType=2,ScriptMajorVersion=21,ScriptMinorVersion=4,ScriptAttributes=1)
MSI (s) (50:34) [23:18:29:964]: Executing op: DialogInfo(Type=0,Argument=1033)
MSI (s) (50:34) [23:18:29:964]: Executing op: DialogInfo(Type=1,Argument=Microsoft SQL Server 2005)
MSI (s) (50:34) [23:18:29:964]: Executing op: RollbackInfo(,RollbackAction=Rollback,RollbackDescription=Rolling back action:,RollbackTemplate=[1],CleanupAction=RollbackCleanup,CleanupDescription=Removing backup files,CleanupTemplate=File: [1])
MSI (s) (50:34) [23:18:29:964]: Executing op: RegisterBackupFile(File=C:\Config.Msi\643b07e2.rbf)
MSI (s) (50:34) [23:18:29:964]: Executing op: RegisterBackupFile(File=C:\Config.Msi\643b07e3.rbf)
MSI (s) (50:34) [23:18:29:979]: Executing op: RegisterBackupFile(File=C:\Config.Msi\643b07e4.rbf)
MSI (s) (50:34) [23:18:29:979]: Executing op: RegisterBackupFile(File=C:\Config.Msi\643b07e5.rbf)
MSI (s) (50:34) [23:18:29:979]: Executing op: RegisterBackupFile(File=C:\Config.Msi\643b07e6.rbf)
MSI (s) (50:34) [23:18:29:979]: Executing op: ActionStart(Name=Do_sqlFileSDDL.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Setting File Security,Template=File: [1])
MSI (s) (50:34) [23:18:29:979]: Executing op: ProductInfo(ProductKey={130A3BE1-85CC-4135-8EA7-5A724EE6CE2C},ProductName=Microsoft SQL Server 2005,PackageName=SqlRun_SQL.msi,Language=1033,Version=151062527,Assignment=1,ObsoleteArg=0,ProductIcon=ARPIcon.ico,PackageMediaPath=\Setup\,PackageCode={3185FC73-41E0-43F4-97DF-2254C4D7C667},,,InstanceType=0,LUASetting=0,RemoteURTInstalls=0)
MSI (s) (50:34) [23:18:29:979]: Executing op: ActionStart(Name=Do_sqlUserSecurity.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Setting User Security,Template=Account: [1])
MSI (s) (50:34) [23:18:29:979]: Executing op: ActionStart(Name=Do_sqlGroupMember.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Installing Local Groups,)
MSI (s) (50:34) [23:18:29:979]: Executing op: ActionStart(Name=Rollback_sqlGroupMember.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Installing Local Groups,)
MSI (s) (50:34) [23:18:29:979]: Executing op: CustomActionRollback(Action=Rollback_sqlGroupMember.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1345,Source=BinaryData,Target=Rollback_sqlGroupMember,CustomActionData=1 0 0 Installing Local Groups 50000 SQLServer2005MSFTEUser$KMAUSQL01SRV$MSSQLSERVER NT AUTHORITY\LocalService )
MSI (s) (50:68) [23:18:29:995]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSICD9.tmp, Entrypoint: Rollback_sqlGroupMember
<Func Name='LaunchFunction'>

I would really be thankful for any hint regarding what to do to solve this problem.... As said - I have an error occuring, this hotfix is said to fix - to be exact, it's the one stating "not to be able to build the execution plan under some circumstances when using a SQL query inside a stored procedure"

Help greatly appreciated!!

Greetings, Wolfgang

Make sure the acct used to startup sqlserver service has read/write access to the folder listed. Also, be sure you login as admin to run setup.

|||

Thanks for your reply oj!

Actually I was logged in as member of the 'Administrators' group... That's what bugged me so much... But as I followed your hint and tried to modify the permissions of the folder manually I immediately got a Windows-Authentication Modal Popup - telling me that I have to authenticate to the domain...

So the problem was that I was local administrator but the hotfix-installation in fact needed mit to be also domain-administrator (don't really understand why though).

I just contacted the Domain-Admin and made him install the hotfix ;-) It worked... Thanks for the hint!

Regards,
Wolfgang

Failed to decrypt XML node "DTS:Password"

Hi!
I made an SSIS package and it runs fine on my workstation, but it fails when I run it on the SQL Server.
I'm using an ODBC connection and when I run the package, I get the error message:
"Login failed. Check for valid user ID, server name or password".
I tried to load the project in Visual Studio on the Server and got the error message above:
Failed to decrypt XML node "DTS:Password"
How can I set up the permissions for decrypting the password?

Thanks for any help!

The problem is that you have the ProtectionLevel set to "EncryptSensitiveWithUserKey". This means the package can be executed only by the user that created the package and on the machine that it was created. (unless you use a domain account and use machines in the same domain)

If you import the package on your server I recomend to change the ProtectionLevel to "EncryptSensitiveWithPassword" - will ecnrypt sensitive information like passwords - or if you store the package on SqlServer you can use "Rely on server storageand rolse for access control" - this means that the package will be stored unencrypted on the SQL Server.

The package can be stored unencrypted ONLY in the SQL Server.

HTH.
Ovidiu Burlacu

|||Thanks for the explanation!
I tried to use the last option"Rely on server storageand rolse for access control", however it causes a lot of error messages in the Business Intelligence Studio and I can not create a build with a deployment manifest. The help sais, this option is not accessible from Visual Studio, but I can not figure out, how to use it.
Can you tell me, how I can use it?
|||

Are you saving the package on a SQL Server or to a file? You can use ServerStorage ONLY if you save the package on SQL Server

To save a package from BIDS directly to SQL Server use File-> "Save Copy of <package_name>.dtsx As..." and in the dialogs that appears you can specify a SQL Server

If you want to create a deployment manifest encrypt with a passord and when you run the Deployment wizard and specify the destination to be a SQL Server the you can choose the "Rely on server storage and role for access control".

HTH,
Ovidiu

|||Thanks alot!

Failed to call SSIS package from ASPX C# Web Page

Hi, Everyone:

I am getting the following error message when I try to execute a SSIS package from an asp.net page written in C# 2.0. What I am trying to do is basically just click on a button in the web page and it will execute the package. The code to execute the package is pretty simple. I pass the path of the DTS package stored in the local folder. This works fine in the machine where SQL2005 is installed locally. But it fail when I have a seperate Web Server and SQL Server. Any ideas? Do I have to install SSIS or SQL2005 on a web server as well?

Thanks

static public string Execute_SSIS_DTS(string DTS_Path)

{

Microsoft.SqlServer.Dts.Runtime.Application app;

app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package package = app.LoadPackage(DTS_Path, null);

DTSExecResult result = package.Execute();

return result.ToString();

}

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Line 226: static public string Execute_SSIS_DTS(string DTS_Path)

Line 227: {

Line 228: Application app = new Application();

Line 229: Package package = app.LoadPackage(DTS_Path, null);

Line 230: DTSExecResult result = package.Execute();

[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +43

[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +169
Utilities.Execute_SSIS_DTS(String DTS_Path) in c:\Inetpub\wwwroot\MasterTables\App_Code\Utilities.cs:228
MasterTables_Admin_MasterTables_LOINC_External.btn_SyncLISTest_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\MasterTables\MasterTables_Custom\MasterTablesCustom_LOINC_External.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +141
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3215

Yes, to use SSIS object model and to execute package you do need to install SSIS on this machine, of course.

See my blog entry for some more details
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

failed laod xml error 0xc00ce556

after i completed working with all my packages finally in my master package i was trying to set ssis->logging

i came up with this error with all my packages how to get back my packages..

Microsoft visual studio is unable to load this document:Failed to load XML due to error 0xC00CE556 "Invalid at the top level of the document. Line 1099, Column 1". This happens when loading a package and the file cannot be opened or loaded correctly into XML document. This can be the result of either providing an incorrect file name to the LoadPackage method or the XML file specified having an incorrect format.

How can i solve this problem please me

Curious, do you use Source Safe or any source control software?|||

I have the project in my local machine.

Everything package and the master package which call the child packages where working just fine.

What i did was after doing all my work in the master package i was try to set ssis logging and all my packages came out with this error...just couldnt open any of my packag...all my packages which i have r the same error..i am just redoing everything...

any help is appreciated...

|||Woops! It looks like the files are corrupted... don't you have copy previous to the changes? I have never see such error. Sorry.

Failed DTS Package locks user out of SQL2005 (Windows Authentication) ... for good!

Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.

In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.

Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.

The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).

We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).

We have been able to replicate this consistently - every annoying time we've tried it!

(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).

Are you saying that you can't connect to Integration Services? From your post the Database Engine is fine, and 'locks user out' is misleading as the account is not locked out.

What does the package try or fail to do (and which step)?
What appears in the application event log when you fail to connect to IS?
Is IS service still running?|||

Thanks for your reply. Allow me to attempt to answer your queries.

Both Integration Services and Database Engine (MSSQLSERVER) are still running. As stated, we can connect to the Database Engine using SQL Authentication, but Windows Authentication (no matter what account) no longer works to either the Database Engine or Integration Services.

OK, perhaps my terminology is not quite correct - it's not an account lock-out, but that's pretty much the symptom. Perhaps I should say "Windows Authentication is broken"?

Sample Event Log (descending timestamp order ie. same as in event log viewer):

MSSQLSERVER Failure Audit (4) 18456 SERVER\Administrator Login failed for user 'SERVER\Administrator'. [CLIENT: <local machine>]

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""DTS_TEST"" failed."

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""ShellPackage"" failed."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""ShellPackage"" started."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""DTS_TEST"" started."

At this stage I can't determine which step in the package is broken, but that's not my biggest issue at this point. Why Windows Authentication "breaks", and how to fix it is my real concern.

Fail to run as job but success execute in VS2005

hi all,

please help,

i using Execute DTS 2000 Package Task to create a package.dtsx, and execute successful in VS2005

but when i created it as a job and it fail to run.

the package.dtsx is calling a package2000.dts, which having 2 steps to run.

i execute the job by step, and the i found that the 1st job is running successful but the 2nd job was fail.

may i know where can i found the error log? or any where to debug it?

please help......

thanks 1st.....

Hi Khin,

* Note that configuring log for SSIS will not provide the log for Individual steps run in the Execute DTS 2000 Package task.

* Configure a Separate log for the DTS file enclosed by opening it in DTS designer, right click -> Properties ->Logging -> <Give a file Path to Store this log file >.

Thanks

Subhash Subramanyam

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to call child package

Have a parent package that calls many child packages (over 30) for a daily data warehouse update. On any given day, it randomly fails to call a child package with the following error:

Error 0x800706BE while preparing to load the package. The remote procedure call failed.

Its not failing the same package each day. I need a better explanation of this error message.

Environment: SQL2005 Enterprise Dec2005 RTM on Itanium64 with Windows DataCenter.

Are all the Execute Package tasks in the parent package configured identically? Where are the child packages stored?|||Yes, all Exec Package tasks are set up the same. All packages are stored in MSDB on our DataWarehouse server. Because we have such a large volume of packages (200), we are using folders to separate the packages. The parent package calls children in the Dimensions and various other folders. The call failures span many of the children folders, so there doesn't seem to be a pattern.

Fail package with expression

How do I use the expression to force a failure using an If statement to evalute a rowcount variable?

I'd evaluate that variable in a script task instead.|||

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

|||

Ken Augustine wrote:

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

Actually, use an expression. Add the script task and hook up a precedence constraint to it. Set it to evaluate the expression.

For the script code, it's basically the default code that is generated when you add a new script task. The only difference is that you call Dts.Results.Failure instead of success.

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Failure
End Sub

End Class

|||

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

|||

Ken Augustine wrote:

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

Just use:

@.rowcount < 1

That's all you need. When you double click on the connection between the ?Data flow? and the script task, set it to use constraint and expression. Then use the above (copy and paste if your variable is named 'rowcount') in the expression box. Note that I believe the CaSE of the variable name is important.

fail component error notification

I have created a package which forms data from flat file to sql database.I have a data flow where all my data is processed to destination.if the package fails by any chance i want an email notification with the cause of the error.how can i do this.please let me know..I'd start with Send Mail task and the OnError event handler for the package.|||Will i be able to send mail with the list of errors occured if the do the above|||Can you specify your goal in more detail? Do you want one email with all errors listed? Do you want all errors, or just the primary error that caused the package to fail?|||I want all the errors in one email.|||

This post might help:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

I created an example that uses a Script task in the OnError event to collect all the errors, and then uses a second Script task in the OnPostExecute event to process them. You could simply add a Send Mail task to the OnPostExecute to send the email.

fail an execute sql task

I have a dts package that is essentially three transformation tasks that creates 3 text files. The transform data is accomplished through an sql query. Before I execute the transform tasks I would like to peform a couple of simple existence checks to verify data: i.e,

if exists (select * from table where week_ending = endoflastweek(getdate())

I could put the statement into the sql (if exists, begin/end) of each of the transformation tasks and prevent the process from executing, but it would be easier to put it into an "execute sql task" and if it succeeds, fail the task or make some kind of declaration not to proceed. I'm not sure how to do this...I know this can be done with an activeX script,

Main = DTSTaskExecResult_Failure

Is there a way to do this in an execute sql task step?I am not sure if you are looking for raiserror or return here. Look up both in Books Online, and see what you think. I am leaning raiserror.

Extremely long lines of text

I have a dtsx package that works fine with one exception. When I open the dtsx package in BI, it gives me the following message:

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 want to open the file?

When I respond yes, the package opens and I can edit or execute with no problem. Still, I want to understand what could cause this message to occur and, more importantly, how I can get rid of the message. When I try to simply execute the package I still get the same error and it seems this will be a problem for trying to run the package from SQL Server agent.

It seems likely to me that this message refers to the dtsx file (in xml format) itself. Does that make any sense?

This issue has been discussed in previous posts:

"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 want to open the file?"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=313598&SiteID=1

|||Thanks for the information. I obviously did not use the right terms when I searched.

Extracting package information from SSIS

Hi all,

I am supposed to maintain a bunch of excels documenting all the mappings I'm doing in SSIS. The excels have the following format:

Target Field | Target Type | Source Table | Source Field | Source Type | Transformation Rule

Apart from being incredibly tedious, it is hard to keep every excel current, as there are other people adding and taking information from the database model.

I plan to extract this information from SSIS and create the excels dinamically, but, apart from parsing the .dtsx for each package, I see no other way of getting what I need.

Any suggestions on how I should do this? Is there an easier way?

Cheers,

David LealThe SSIS object model provides access to all aspects of the package, you can use it from both managed and unmanaged code. Also, take a look as SQL BI Metadata Samples that has similar goal of documenting the package, you may find some of its code useful for your project.|||Thank you very much, Michael. I'll look into that.

Extracting Cube detailed info

Hello,

Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.

Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.

-Lawrence

If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.

If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.

Adrian Dumitrascu

|||

Hello Adrian,

If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.

Sincerely,

-Lawrence

|||

Hi,

here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.IO;

using Microsoft.AnalysisServices;

namespace GetCubeInfo

{

class Program

{

static void Main(string[] args)

{

string dateFormat = "yyyy-MM-dd HH:mm:ss";

try

{

FileStream stream;

stream = File.Create(@."GetCubeInfo.xml");

XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

// Causes child elements to be indented

writer.Formatting = Formatting.Indented;

// Report element

writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");

writer.WriteStartElement("Server");

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");

writer.WriteAttributeString("Name", null, server.Name);

writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));

// Get the Adventure Works cube(s)

foreach (Database database in server.Databases)

{

writer.WriteStartElement("Database");

writer.WriteAttributeString("Name", null, database.Name);

writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));

writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));

foreach (Cube cube in database.Cubes)

{

writer.WriteStartElement("Cube");

writer.WriteAttributeString("Name", null, cube.Name);

writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Cube

}

foreach (Dimension dimension in database.Dimensions)

{

writer.WriteStartElement("Dimension");

writer.WriteAttributeString("Name", null, dimension.Name);

writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));

writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));

writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));

writer.WriteEndElement(); // Dimension

}

writer.WriteEndElement(); // Database

writer.Flush();

}

writer.WriteEndElement(); // Server

writer.Flush();

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

|||

Hello,

I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.

BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.

Sincerely,

-Lawrence

|||

Hi Lawrence,

I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.

Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).

Regards,

Bertil

|||

Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.

Your help is appreciated!

-Lawrence