Hi,
I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.
Thanks
Even I am also trying for the Same. Update me if you get a solution.sqlHi,
I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.
Thanks
Even I am also trying for the Same. Update me if you get a solution.sqlI'm executing an SSIS package using the following stored procedure
ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END
I'm then using a tableadapter to execute this from my ASP.NET page using the following code,
Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub
If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.
When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.
Can someone suggest how I can get the value 0-6 to my code?
Sorted!
Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.
I am facing some issues while working on SSIS in VS2005,
The Scenario is we have 35 excel files from which we have to import data
to Sql 2005.
We have to execute these packages in a specific sequence so we are not
using For Each Loop Control.
Now the Problem is this that we have 35 executables as an output that is
corresponding to each package, and from each executable we can Change
excel path and Sql Connection String.
The problem is that one has to open each executable to set the path; we
wanted to keep them Configurable like , as if we can have any variable
define in the packages for path, and in the packages we use that variable
and append the XLS file name to it like ..
@.PathVariable+ test.xls
And from the Configuration file at run time we can set the path to this
variable like
@.Path Variable="C: /Windows/ Test Folder/"
And then executable picks the path from there.
Is there any possibility to implement any thing similar, we have tried but
its not working, any help in this regard will be really appreciating.
Its really urgent, I will wait for feedback on this.
Yeah you can do that. just use an expression on the ConnectionStrig property of the connection managers. This should give you some clues:
SSIS Nugget: Dynamically set a logfile name
(http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx)
-Jamie
I just want to confirm my thinking here.
-I have a server (serverA) which has SSIS installed and some .dtsx files sitting in a shared folder.
-I have a server (serverB) which is running some ASP.Net apps.
One of my ASP.Net apps on serverB needs to execute the packages stored on serverA.
Am I correct in thinking that I need SSIS installed on serverB or is there another way?
-Jamie
You need to install SSIS on machine where the package is run.So if you need to run the package on serverB - yes, you need
to install SSIS on serverB.
If you can run the package on serverA - you don't need SSIS
on serverB. Instead, run the package remotely.
E.g. you can create an Agent job (without schedule) that
executes on serverA and then run it on demand from serverB.
Thanks,
Michael.|||Thanks for the confirmation Michael.|||
I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)
Wes
|||Wes Linz wrote:
I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)
Wes
Use sp_start_job SQL stored procedure (see syntax in Books Online)
sqlI just want to confirm my thinking here.
-I have a server (serverA) which has SSIS installed and some .dtsx files sitting in a shared folder.
-I have a server (serverB) which is running some ASP.Net apps.
One of my ASP.Net apps on serverB needs to execute the packages stored on serverA.
Am I correct in thinking that I need SSIS installed on serverB or is there another way?
-Jamie
You need to install SSIS on machine where the package is run.So if you need to run the package on serverB - yes, you need
to install SSIS on serverB.
If you can run the package on serverA - you don't need SSIS
on serverB. Instead, run the package remotely.
E.g. you can create an Agent job (without schedule) that
executes on serverA and then run it on demand from serverB.
Thanks,
Michael.|||Thanks for the confirmation Michael.|||
I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)
Wes
|||Wes Linz wrote:
I have done as you suggested here and created an Agent job ... How do I call for the job in my vb code in the application? (Both SSIS & Application are on the same server)
Wes
Use sp_start_job SQL stored procedure (see syntax in Books Online)
I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.
If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.
Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.
Many thanks in advance,
Grant
Are any exceptions being thrown within the WS code?
Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?
Do you get any error events?
You really need to have decent error handling and make good use of the events from those methods to find out what is going on.
Without an error message we are in the dark out here.
|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.I suggest you to verify the permissions given to this user into the DB tables and also read this articles:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
regards|||Hi Darren,
You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.
The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".
If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.
In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.
I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.
Thanks,
Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.
What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?
Cheers,
Grant|||
Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)
Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)
I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.
Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.
|||hi Darren,Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.
It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?
Many thanks for your help,
Grant
Hello Everyone,
I am going through the following article
http://msdn2.microsoft.com/en-us/library/ms141766.aspx
and I have some clarifications to seek.
Based on the above article it seems its possible to run a DTS package on a SQL 2006 X64 machine.
Furthermore it seems possible that this package will run without any upgrade being performed to SSIS.
Also, to execute the DTS package in 32 bit mode, all I have to do is to set the job step type of SQL Agent to Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.
Are the above deductions correct? Please let me know if some correction / refinement is needed.
There is no 64-bit design-time or run-time support for DTS packages.
You can run DTS packages (SQL Server 2000), as well as SSIS packages (SQL Server 2005), on the same 32-bit machine. You can use the dtsrun.exe utility to run the DTS packages from the command prompt. You also have the option of running a DTS package from a SSIS package (SQL Server 2005) by adding the Execute DTS 2000 Package task to the SSIS package.
For more information about SQL Server 2005 support for DTS packages, see "SQL Server 2005 Integration Services Backward Compatibility" at http://msdn2.microsoft.com/en-us/library/ms143706.aspx#runtime_installing.
Hi All,
I have a scenario where I am executing bunch of DTS packages from within an SSIS package. These packages are saved as structured files and everything works fine. However I am concerned about one thing which is whether these SSIS/DTS packges will run fine on my production server where I dont have any SQL SERVER 2000 /2005 installed , it just has SSIS installed on it. Appreciate all help.
Thanks
I did a little research on MSDN and other websites and I think it should not be any problem executing the DTS packages from within SSIS packages on a server having just Integration Services Installed on it.
http://msdn2.microsoft.com/en-us/library/ms137907.aspx
Thanks
|||Installing SSIS gives basic DTS suport for free, so it will already be there. Notable exceptions are the DTS AS Processing Task and Data Mining Task.
Installing Run-time Support for SQL Server 2000 DTS Packages
You can run DTS packages and SSIS packages on the same computer.
When you select Integration Services for installation, Setup also installs support for DTS packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Support in the runtime is enhanced to enable DTS packages to access SQL Server 2005 data sources.
SQL Server 2005 Integration Services Backward Compatibility
(http://msdn2.microsoft.com/en-us/library/ms143706.aspx#)
Hi, I'm new to this forum, so please bare with me.
I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.
This package works fine in visual studio and when i deploy it to the server.
The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.
Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.
DontSaveSenstive is clearly not an option as this just creates an unusable package.
EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)
EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.
Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.
Thanks
Bob.
There is a comprehensive KB article that may cover your question:
http://support.microsoft.com/kb/918760
|||Thanks, that has helped.
for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.
this is the only option on the KB article that worked for me.
Thanks.
Hello,
Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?
Better could that stored proc receive a prameter?
Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?
I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...
If not, are there any plans to enahnce the MS provider to handle that?
A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?
Thanks
Hi,
I can attempt to provide the beginning of an answer.
I use an ADO.NET OracleClient data provider in ssis package data Flow to access Oracle 8 where I have a Function that I call this way
SELECT ONE.TABLE_READY('Customer_Restated') as Ready FROM DUAL
For stored procedures it works like that in Toad but I cannot confirm if it works from SSIS.
Call one.verify_job_completion('AA') ;
Philippe
|||Thanks Philippe,
I had tried that but my stored proc does inserts/updates, so I can't "hide" it inside a function, otherwise I get my "ORA-XXXX can't do DML inside DQL..."
I made that work with ADO.NET and IsQureyStoredProc=True on the ExecuteSQLTask, but still struggling with parameters and stored procs that return cursors (data into my dataflow).
sqlI am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.
My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?
Thanks,
Nathan
Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()
lblErrorOther.Text = pkgResults.ToString()
End Sub
There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.
The code seems fine.
By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,
Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?
Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.
Thanks for your quick response.
Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.
To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.
Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||
Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
AND
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
|||The messageAlso see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?
Thanks,
Nathan|||
Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).
|||I can get my package to execute successfully now using ASP.NET.Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.
I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.
Thanks for your help on this.
Nathan|||
Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).
Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);
Thanks,
Mark.
I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.
My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?
Thanks,
Nathan
Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()
lblErrorOther.Text = pkgResults.ToString()
End Sub
There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.
The code seems fine.
By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,
Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?
Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.
Thanks for your quick response.
Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.
To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.
Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||
Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
AND
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
|||The messageAlso see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?
Thanks,
Nathan|||
Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).
|||I can get my package to execute successfully now using ASP.NET.Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.
I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.
Thanks for your help on this.
Nathan|||
Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).
Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);
Thanks,
Mark.
I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.
My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?
Thanks,
Nathan
Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()
lblErrorOther.Text = pkgResults.ToString()
End Sub
There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.
The code seems fine.
By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,
Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?
Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.
Thanks for your quick response.
Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.
To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.
Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||
Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
AND
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.|||The message
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
indicates an error with SQL Server destination, not the Excel spreadsheet.
Also see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?
Thanks,
Nathan|||
Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).
|||I can get my package to execute successfully now using ASP.NET.Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.
I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.
Thanks for your help on this.
Nathan|||
Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).
Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);
Thanks,
Mark.
Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:
I also tried
And in the parameter mapping, I added two date user variables, one with a parameter name ‘sd’ and the other ‘ed’
--
I tried many other combinations as well. The error I would get would say “parameter name unrecognized”. [Execute SQL Task] Error: Executing the query "dbo.DimBuild sd, ed" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is there something wrong with my syntax? Interestingly, I tried executing the stored procedure using an ADO.Net connection, with similar parameter mappings, and it worked just fine.
Thanks,
- Joel
I modified the package I was working on to use parameter names 0 and 1 (with "exec sproc ?, ?" as the query) and it succeeded.
Don't you just love the great documentation?|||I know the documentation lacks on this. In this post http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=57637 Kirk has given us indication which Parameters can be used with which connection manager etc.
Can you execute a SSIS project from an ASPX page? Or do you have to wrap it into a SQL job and run that?
Does anybody have a code snippet to do this?
Well, you can, but it is rarely a good idea for at least two reasons:
you are sharing address space and threads with IIS, so the scalability of this solution is limitedOnce you have created the COM Callable Wrapper (CCW) for the managed SSIS runtime (ManagedDTS assembly), the code will look almost identical to previous VB6 code for calling a DTS package -- after instantiating a Package object, not much more than LoadFrom and Execute.
However I do not have a sample to provide. But the VB.NET sample should be helpful. http://msdn2.microsoft.com/en-us/library/ms136090.aspx
-Doug
|||Thank you Douglas.Now i can think of CCW first.By the way do you know any of the tools for creating CCW for ManagedDTs.dll.
I do not have .net framework installed on my system.so is that possible to create CCW without .net framework on my machine?
thanks in advance
|||I doubt it. You need the tlbexp.exe utility from the Framework SDK. But I haven't used that tool in a long time. (Visual
Studio performs this step for you invisibly.) Please look for "COM Callable Wrapper" in the MSDN Library for more information.
-Doug
How can i do to execute SSIS package without SSIS console ?
This can occur when you use a hosted database with your hosted web.
thanksHi,
I'm not quite sure what you mean by the SSIS console.
Packages can be executed using DTExec - a command line execution tool. You can also execute packages from managed code.
Does this help?
-Jamie|||Do you mean that i can execute a dts package from an aspx web page ?|||
Coroebus wrote:
Do you mean that i can execute a dts package from an aspx web page ?
Yes. Search this forum for clues.
You should use the Microsoft.SqlServer.ManagedDTS assembly
A few links:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_Package_Members.htm
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Package_Execute.htm
-Jamie|||Also, please see the Books Online topic, "Running an Existing Package using Visual Basic."
-Doug
|||
Is there any performance difference in running it from CLR and dtsui or command
vb code link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2f9fc1a8-a001-4c54-8c64-63b443725422.htm
Dim app As New Application
Dim pkg As New Package
Dim pkgLocation As String
Dim pkgResults As DTSExecResult
pkgLocation = "C:\\Program Files\\Microsoft SQL Server\90\Samples\\Integration Services\\Package Samples\\CalculatedColumns Sample\\CalculatedColumns\\CalculatedColumns.dtsx"
Designer UI and DtExecUI has some overhead to show the package progress and logging. Designer also executes package out-of-process to ensure designer stability, so it might be slower than other options.
DtExec speed depends on amount of logging and event reporting you turn on using command line.
Executing programmatically does not have any performance penalty.
In the code above please use (If you pass True to three-argument version, you get neutral-threaded package COM object, which might be somewhat slower for execution. It is better and faster for package manipulation though).
pkg = app.LoadPackage(pkgLocation, Nothing)
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
|||Steve in Crystal Lake Illinios wrote:
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
Of course, see Application object documentation in Books Online. There are LoadFromSqlServer and LoadFromDtsServer methods.
|||Is it possible to set this dtsx file to be scheduled from 6 am to 6 pm from monday to friday ?
Thanks a lot
|||This is a snippet of code of our vb service:
"Fic" is when you have your dtsx allocated on Windows folder
"Sql" is allocated on MSDB
"dts" is allocated on SSIS Package Store (both Sql Server and Windows folder)
Select Case ObjSSIS.sSourceType
Case "Fic"
pkg = app.LoadPackage(ObjSSIS.sRutaDts & ObjSSIS.sSSISName & ".dtsx", Nothing, True)
Case "Sql"
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing, Nothing, Nothing)
Case "dts"
pkg = app.LoadFromDtsServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing)
Case Else
Throw New ArgumentException("El paquete SSIS no tiene ubicación")
End Select
How can i do to execute SSIS package without SSIS console ?
This can occur when you use a hosted database with your hosted web.
thanksHi,
I'm not quite sure what you mean by the SSIS console.
Packages can be executed using DTExec - a command line execution tool. You can also execute packages from managed code.
Does this help?
-Jamie|||Do you mean that i can execute a dts package from an aspx web page ?
|||
Coroebus wrote:
Do you mean that i can execute a dts package from an aspx web page ?
Yes. Search this forum for clues.
You should use the Microsoft.SqlServer.ManagedDTS assembly
A few links:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_Package_Members.htm
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Package_Execute.htm
-Jamie|||Also, please see the Books Online topic, "Running an Existing Package using Visual Basic."
-Doug|||
Is there any performance difference in running it from CLR and dtsui or command
vb code link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2f9fc1a8-a001-4c54-8c64-63b443725422.htm
Dim app As New Application
Dim pkg As New Package
Dim pkgLocation As String
Dim pkgResults As DTSExecResult
pkgLocation = "C:\\Program Files\\Microsoft SQL Server\90\Samples\\Integration Services\\Package Samples\\CalculatedColumns Sample\\CalculatedColumns\\CalculatedColumns.dtsx"
Designer UI and DtExecUI has some overhead to show the package progress and logging. Designer also executes package out-of-process to ensure designer stability, so it might be slower than other options.
DtExec speed depends on amount of logging and event reporting you turn on using command line.
Executing programmatically does not have any performance penalty.
In the code above please use (If you pass True to three-argument version, you get neutral-threaded package COM object, which might be somewhat slower for execution. It is better and faster for package manipulation though).
pkg = app.LoadPackage(pkgLocation, Nothing)
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
|||Steve in Crystal Lake Illinios wrote:
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
Of course, see Application object documentation in Books Online. There are LoadFromSqlServer and LoadFromDtsServer methods.
|||Is it possible to set this dtsx file to be scheduled from 6 am to 6 pm from monday to friday ?
Thanks a lot
|||This is a snippet of code of our vb service:
"Fic" is when you have your dtsx allocated on Windows folder
"Sql" is allocated on MSDB
"dts" is allocated on SSIS Package Store (both Sql Server and Windows folder)
Select Case ObjSSIS.sSourceType
Case "Fic"
pkg = app.LoadPackage(ObjSSIS.sRutaDts & ObjSSIS.sSSISName & ".dtsx", Nothing, True)
Case "Sql"
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing, Nothing, Nothing)
Case "dts"
pkg = app.LoadFromDtsServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing)
Case Else
Throw New ArgumentException("El paquete SSIS no tiene ubicación")
End Select
How can i do to execute SSIS package without SSIS console ?
This can occur when you use a hosted database with your hosted web.
thanksHi,
I'm not quite sure what you mean by the SSIS console.
Packages can be executed using DTExec - a command line execution tool. You can also execute packages from managed code.
Does this help?
-Jamie|||Do you mean that i can execute a dts package from an aspx web page ?
|||
Coroebus wrote:
Do you mean that i can execute a dts package from an aspx web page ?
Yes. Search this forum for clues.
You should use the Microsoft.SqlServer.ManagedDTS assembly
A few links:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_Package_Members.htm
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Package_Execute.htm
-Jamie|||Also, please see the Books Online topic, "Running an Existing Package using Visual Basic."
-Doug|||
Is there any performance difference in running it from CLR and dtsui or command
vb code link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2f9fc1a8-a001-4c54-8c64-63b443725422.htm
Dim app As New Application
Dim pkg As New Package
Dim pkgLocation As String
Dim pkgResults As DTSExecResult
pkgLocation = "C:\\Program Files\\Microsoft SQL Server\90\Samples\\Integration Services\\Package Samples\\CalculatedColumns Sample\\CalculatedColumns\\CalculatedColumns.dtsx"
Designer UI and DtExecUI has some overhead to show the package progress and logging. Designer also executes package out-of-process to ensure designer stability, so it might be slower than other options.
DtExec speed depends on amount of logging and event reporting you turn on using command line.
Executing programmatically does not have any performance penalty.
In the code above please use (If you pass True to three-argument version, you get neutral-threaded package COM object, which might be somewhat slower for execution. It is better and faster for package manipulation though).
pkg = app.LoadPackage(pkgLocation, Nothing)
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
|||Steve in Crystal Lake Illinios wrote:
Is there an alternative LoadPackage method for when the Package is contained within the MSDB database vs. the File System?
Thanks
Of course, see Application object documentation in Books Online. There are LoadFromSqlServer and LoadFromDtsServer methods.
|||Is it possible to set this dtsx file to be scheduled from 6 am to 6 pm from monday to friday ?
Thanks a lot
|||This is a snippet of code of our vb service:
"Fic" is when you have your dtsx allocated on Windows folder
"Sql" is allocated on MSDB
"dts" is allocated on SSIS Package Store (both Sql Server and Windows folder)
Select Case ObjSSIS.sSourceType
Case "Fic"
pkg = app.LoadPackage(ObjSSIS.sRutaDts & ObjSSIS.sSSISName & ".dtsx", Nothing, True)
Case "Sql"
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing, Nothing, Nothing)
Case "dts"
pkg = app.LoadFromDtsServer(ObjSSIS.sRutaDts & ObjSSIS.sSSISName, ObjSSIS.sServer, Nothing)
Case Else
Throw New ArgumentException("El paquete SSIS no tiene ubicación")
End Select
EXECUTE AS question,sql server