EncryptSensitiveWithPassword Protection Option Cannot Be Used With DTexec Execution Of SSIS Packages

Nov 6, 2006

Our SSIS packages use the Web Service Task to call services to send email and write package failure data to a department wide database. These Web Service Calls are failing with HTTP 401 errors. It was caused by the passwords for the HTTP connections not being saved when the SSIS pkgs were saved to .dtsx files. I have tried saving the package with a password and the EncryptSensitiveWithPassword protection option. This password can be supplied when the package is called from another package or a package is executed in Visual Studio but cannot be supplied to DTexec to execute the package in a job. DTexec does have a /Password parameter but it is rejected if the package is loaded from a .dtsx file.
This appears to be a bug in DTexec. It only accepts the /Password parameter when the package is loaded with the /SQL option. Specifying /Password and /File is not supported.

Is this a known bug? Are there any workarounds?
Has anyone successfully called a Web Service from SSIS executed via DTexec?

View 1 Replies


ADVERTISEMENT

Running SSIS-packages In Stored Procedure Using Dtexec

Apr 2, 2008



Hi,

I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.

The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.

Here is a sample list of ssis-packages from procedure:


exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataaaa.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedatabb.dtsx" '

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataccc.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataddd.dtsx"'

exec xp_cmdshell 'dtexec /f "E:SSISSourcedataSourcedataeee.dtsx"'


So, if eg bbb.dtsx would fail, is it possible to get the whole procedure failing?

View 5 Replies View Related

Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Jan 11, 2008

Hi,

I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.

I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.

The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.

The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).

We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).

I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.

In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).

If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.

The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.

Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.

I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).

Additional information:

The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.

The request:

Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!

Eero Ringmäe

View 2 Replies View Related

SSIS Too Strict On Conversion With No Option To Overwrite, Forcing Me To Use DTS 2000 Packages

Nov 1, 2007

This is more of a philosophical post, but feedbacks are welcome!

I am working at migrating SQL 2000 DTS packages that pulls data from MAS90 via ODBC connections.
At first, I REALLY tried to learn SSIS and I hated it at first, with all the new things one has to do to get a simple import to work. After a while, I begin to appreciate some of the new design and the more tiered approach. Indeed, I tried to use SSIS to import the tables and even learned how to overcome the Unicode/non-Unicode conversion errors by using the Import Wizard to do the grunt work.

But today I came across a show stopper: My imports are failing because the source lied about its metadata type and I am getting a "Value too large for output column" error. I tried to recreate the Task to no avail. I searched on the web and there are very few posts regarding to this and unfortunately I don't have a way to tweak my ODBC connection properties for MAS90 to some how "fool" SSIS. I finally give up and migrate the DTS 2000 package instead.

I am not too happy about this solution because I know that more likely or not Microsoft will discontinue support for such legacy approach and then it is more work down the road. I REALLY wanted to do it right, to rebuild it natively in SSIS but why does SSIS have to make things so hard by enforcing the type checks so tightly? Is it so bad to allow users who know the data better to by pass the validations? We are not working in a perfect Comp Sci 101 world where every thing is scrubbed clean, we work in a world of bad, old, malformed data.

If there is a way for me to overcome that "value too large" error, I am all ears.
Thank you for reading.

View 1 Replies View Related

A/Synchronous Execution Of SSIS ETL Packages

Jun 8, 2006

Hi

I'd like to know if there's a way to control the execution of ETL packages, such that:
Different packages, or at least packages that don't access the same table or database run asynchronously with respect to each other; e.g., two different packages run at the same time
and
If a package is called for execution more than once by different requests, force them to run synchronously, or one after the other.If this is possible, what resources would it require? Is this possible under, say, a dual or quad processor machine?

Thanks.

View 6 Replies View Related

Monitoring The Running And Execution Of SSIS Packages With MOM

Jan 11, 2006

Hi,

Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?

What is the recommended way to monitor SSIS execution?

Thanks,

- Joel

View 2 Replies View Related

Remote Execution Of SSIS Packages Via Web Service

May 30, 2006

Is anyone executing SSIS packages using a web service similar to the example in http://msdn2.microsoft.com/en-us/ms403355(SQL.90).aspx

From what I've read, there is a new HTTP server embeded in SQL Server (so we don't have to have IIS) that this could be done from??







View 2 Replies View Related

Setting The Protection Level Of Packages

Aug 31, 2007

Hello everyone,

Can someone recommend me the most efficient protection level for SSIS
packages for my environment? I've been doing some reading on this lately, but can't really decide which one to use. The Rely on server storage for encryption (ServerStorage) seems interesting, but I'm unsure if that would work on my environment (Windows Authentication).

Currently I'm saving my packages to my dev server under "Encrypt sensitive data with user key" Protection level, but seems like the passwords for the FTP Connection for instance doesn't get saved when I deploy my package to the server.

Our environment consists of 2 dev servers + 2 prod servers with windows authentication, our SQL Agent Account is a domain account with local admin rights and sysadmin rights on the SQL Server and we try to use proxy accounts to access network resources instead of giving the agent access to everything.

This is what I found on MS's web site.
http://technet.microsoft.com/en-us/library/ms141747.aspx
http://technet.microsoft.com/en-us/library/ms141053.aspx

View 1 Replies View Related

Problem: SSIS Package Failure Using 32-bit DTExec And 64-bit DTExec

Apr 17, 2008

Hi all,


I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.


Here are the details:

Environment:

Windows Server 2003 64-bit (Build 3790: Service Pack 2)
SSIS 32-bit & 64-bit installed
SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)

SSIS Package details (compiled in 64 bit)

Script tasks only
Microsoft Visual Basic .NET (using TRY...CATCH block)
PreCompileScriptIntoBinaryCode = TRUE
Run64BitRunTime = TRUE

Execution

Batch file that uses DTExec to execute the Package.

SCENARIO
I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,


Wrong connection String using 32-bit Execution

While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.

Wrong connection String using 64-bit Execution

While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.

Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.

1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.

2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?


P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.

Sorry for the lengthy one and Thanks very much for you help in advance .



Thanks again!

Suresh






View 13 Replies View Related

DTEXEC /CONFIGFILE Option

Dec 28, 2006

I have the following command which uses the /CONFIGFILE option:

master.dbo.xp_cmdshell 'dtexec /DTS "File SystemPackagesMyPackagesMyPackage" /SERVER "MYSERVER" /CONFIGFILE "C:Program FilesMicrosoft SQL Server90DTSPackagesMyPackagescustomersite.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V '

When the package runs, one of the first things it reports is that the configuration file, 'customersite.dtsconfig' cannot be found:

...

Description: The package is attempting to configure from the XML file "CustomerSite.dtsConfig".

...

Description: The configuration file "CustomerSite.dtsConfig" cannot be found. Check the directory and file name.

...

Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.

The package hapilly executes (and, somehow does grab the appropriate values from config (in this case, it is the SQL Login passwords).

Can anyone provide some insight into this or is this a bug that should be ignored?

View 1 Replies View Related

Escape Character For /SET Option Of Dtexec

Jan 25, 2006

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

View 9 Replies View Related

DTExec /reporting Option - Unable To Exclude Anything

May 25, 2008




Hi,
I'm messing with the /reporting option of DTExec. In BOL it states:

The optional parameter event_guid_or_name is a list of exceptions to the log providers. The exception specifies the events that are not logged that otherwise might have been logged.
(dtexec utility)

Try as I might I cannot get any value for event_guid_or_name have the slightest afffect. I have tried all of the following:

/reporting v;Information
/reporting v;OnInformation
/reporting v;Info


Well, none of those made any difference. Information events raised from my package were still getting reported to the command-line so I'm obviously barkig up the wrong tree trying to name a particular event.

Please can someone tell me a value that I can supply for event_guid_or_name that will make a difference?

Thanks
Jamie


[Microsoft follow-up]

View 8 Replies View Related

Need Some Help With DTexec Execution

Jul 26, 2006

Hi!

When i excute in the sql server this works fine

xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx "'

but when i excute throught the C# code

jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx"'", cconn);

It shows up red lines near the " and ' qoutes. I know that quotation marks are wrong but don't know how to solve it.

some help please

Thanks

jas



View 4 Replies View Related

Dtexec And Passing Variables Between Packages

Oct 12, 2007

When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!

View 3 Replies View Related

DTExec:The Package Execution Returned DTSER_FAILURE&&<1&&>

Nov 5, 2007

thanks in advance

Hi, I am web developer. But for the last few weeks i have been working on SQL2005. I actually exported data from a table (SQL 2000) into the Excel sheet and then i have been trying to import data from Excel sheet into another table(SQL 2005). For this i had created SSIS package successfully. I had added this into integration services project(MicroSoft). After pressing CTRL+F5. I am repeatedly getting the following error
DTExec:The Package Execution returned DTSER_FAILURE<1>
Please help me because requirement is urgent.

View 10 Replies View Related

Package Execution With SQL Server Agent Or DTEXEC

Apr 4, 2006



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 !

View 3 Replies View Related

Remote Execution Of Package Via Dtexec And Access Denied

Feb 17, 2006

I am finishing a port of a project that was done with RS2000/AS2000/DTS2000. The cube process is triggered remotely by a Korn Shell script from Services for Unix that runs a DTS package:

dtsrun.exe /S "$ANALYSIS_SERVICES_HOST" /U "$ANALYSIS_SERVICES_USER" /P "$ANALYSIS_SERVICES_PASSWORD" /N "$PACKAGE_NAME"

I have finished porting the AS and RS parts to their 2005 equivalents and now I am trying to get the cube processing converted. I have built a SSIS package that processes the cube and I have deployed it to the SQL Server in MSDB. When I am logged on to the SQL Server box directly, I can execute the package via the Management Studio and from the command line via:

dtexec /Ser "$ANALYSIS_SERVICES_HOST" /DT "$PACKAGE_PATH_NAME"

When I try to register the SQL Server in Integration Services from my client PC Management Studio it fails with "Access Denied". When I try to run the dtexec from my client PC it fails with "Access Denied".

I have walked through Kirk Haselden's instructions at:

http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

(adding myself to Distributed COM Users, noting the correct DCom Config settings for MsDtsServer and restarting the Integration Services service) and the problem is still not resolved. I have posted to the DTS group on Technet and now I am posting here. This can not be run scheduled by SQL Agent as it needs to be triggered by other load/postload activities. Are there any other fixes for this problem that I can try?

Thanks,

Keehan

By the way, being able to have all the parts of this project in one dev environment is great. The previous application was developed in 3 separate locations which was pretty clunky.

View 6 Replies View Related

DTExec: The Package Execution Returned SUCCESS But Had Warnings

Jun 23, 2006

hi!

i am trying to execute through command line the following:

dtexecui /SQL "ArchiveMain" /SERVER "SE413695AASQL2005" /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I am actaully creating an archiving package.

1. All it does is get some data from the Table.

2. creating directory and set usedirectoryifexist = true

shows some warning when i run that directory already exists cos i don't want to create new one i want to use the existing one.

3. actual archiving to get call to another package for archiving - warning while running the package that parent variable is missing

The pacakge seems to run while i execute it thorugh command prompt but i get this message

DTExec: The package execution returned DTSER_SUCCESS (0) but had warnings, with
warnings being treated as errors!

Thanks,

Jas

View 1 Replies View Related

Does The Sql Server 2005 EVAL Distribution Support Run Of Packages Under DtExec?

Mar 23, 2006



I believe I have made a complete install of Sql Server 2005 on my dev. machine, including all client tools, and can run SSIS packages from the dev. environment.

However DtExec refuses to run any package and always throws the "insufficient version" error (as does the "RunPackage" component, probably for the same reason).

I have repeatedly attempted to find something more to install from the Sql Server 2005 eval release that will allow DTExec to run without the "insufficient version" error, but can find nothing else to install from the eval. The install wizard (and the "change" wizard) indicates everything is installed and will not build a list of any additional components to install.

Are we sure that DtExec will install and run from a sql server 2005 eval distribution?



Licenses are in the pipeline (not that pipleine) but we need to start testing our SSIS solution in the meantime.

p.s. Kudos to the dev. team, it really is an excellent tool!

Thanks,

Ken

View 3 Replies View Related

SSIS Package Protection Level - DontSaveSensitive

Feb 1, 2008

For those of you who have had a hard time like me trying to figure out using Protection level for an SSIS package whilst deploying the package via the SQL Server Agent, here is a piece of advice:


Firstly the protection level is set by Default to - "EncryptSensitivewithUserKey".

The encryption actually takes place only if you have things like - passwords etc..

From my experience - using both - "EncryptSensitivewithUserKey" and "EncryptSensitivewithPassword" Security features have turned out to be unreliable when deploying through SQL Server Agent (even while using a Proxy account having all previliges).

This is it seems because of issues with the user who created the package being different from the user who deployed the package. (which is really ridiculous).


So I used the ProtectionLevel - "DontSaveSensitive" - which means it is not going to encrypt anything in the package and so ur sensitive information would be blank. You would have to then supply your password etc using a configuration XML file. - using SSIS "Package configuation" in your menu....


This has been the most reliable way of solving the whole problem with encryption.

bear in mind that you might want to put the XML file in a secure location to which no one else has access to.



Thanks
Pramod

View 1 Replies View Related

SQL 2012 :: SSIS Deployments - Protection Level Settings

Jun 23, 2014

I am currently designing a SSIS package that will migrate data from Syabse to Sql server. If these packages are to be deployed to file system (as this is one time run for migrating data) or to SSIS catalog or to Sql server.

Also, I have not changed the default protection level value in packages during design time and would like to know if I have to change it while handling the deployment using DTutil (yes, i need to deploy & execute using command line utilities).

Please note that I do not have access to PROD, UAT environments and deployment team will use my BAT file that is expected to deploy and execute the packages.

View 3 Replies View Related

OPTION(RECOMPILE) And Execution Plan

Jan 23, 2008



Hi,


I use recompile option in SQL query to dynamic pass variable to optimizer.

I verify explain plan with SET STATISTICS PROFILE ON

and optimizer chose nested lookup ,ok. But if use Display Estimated Execution Plan (CTR+L) I€™ve get merge join. It€™s very confusing, some suggestion €¦?


Use AdventureWorks

go

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= @StartOrderDate

OPTION(RECOMPILE);

SQL2005SP2


djedgar

View 4 Replies View Related

SSIS Package Will Not Execute When DontSaveSensitive Protection Level Is Selected

Nov 9, 2006

Hi,

Can anyone explain to me why my ssis packages will not work when DontSaveSensitive protection level is selected? My package configurations are set as SQL server configuration type, and I have a table in a database that contains all the sensitive information (passwords and such). If I select "EncryptSensitiveWithUserKey" everything works, but I will be the only one able to execute the packages (not good, I need others to be able to execute them as well).

The error I'm getting tells me that the connection is not configured correctly or I may not have the right permissions on the connection.

My guess is that the DontSaveSensitive drops the passwords, but when I edit the data source and re-enter the password, it still does not work. Also, the database table I use that contains the sensitive data is not affected, all data remains.

What the fudge is going on here? I'm a newbie at this, can someone help me out?



View 7 Replies View Related

Error Viewing The Execution Option Of A Report

Jun 10, 2005

I select the option execution from a report (it's in properties option) and then raise an error which says An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

View 29 Replies View Related

Parent/Child Packages And Transaction Option Not Working As Expected

Dec 18, 2007

Has anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.

The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.

So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored



Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored



for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.

View 7 Replies View Related

Calling SSIS Packages From ASP.NET - Packages With File System Tasks End Abruptly

Jan 9, 2007

I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.

Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.

When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.

I've tried giving administrative permissions to the ASPNET worker process user to no avail.

I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.

I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.

Anybody got any idea on how to go about this?

View 33 Replies View Related

Integration Services :: Remotely Execute Packages On SSIS Server - Packages Are Deployed In File System

Apr 22, 2015

We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.

SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.

We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.

One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.

View 4 Replies View Related

Logging Packages Execution

Sep 13, 2006

Hello all,
I am struggling around defining a logging mechanism for my packages. I have 2 questions concerning that matter:
I have used event handlers for my loggings (as defined here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx ), but the problem is with packages that failed validation. I cannot find log entry for these cases since no "onerror event" doesn't trigger (for instance when the table I'm loading to doesn't exsist).

And the second question: many of my packages are executed using execute process task (using dtexec command line). I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error€¦
Thanks,
Liran






View 11 Replies View Related

Slower Execution Of SISS Packages

Apr 6, 2006

Hello,

I am experiencing that my SISS packages are executing faster during development from within the Sql Business Intelligence Development Studio environment than when I execute them with dtexecui after I have deployed them as either File System Or Sql Server deployments.

After deployment the packages run about twice a long as they do from within Sql Business Intelligence Development Studio (i.e they reference the same data and are executed on the same machine).

In short the packages import data, during this import stage the data is massaged certain calculations are performed thereon where after this massaged data is used to perform further calculations.

A summary of tools used in SISS are Execute Sql Task, Flat File Source, Oledb Destination, Data Conversion, Aggregate, Conditional Split, Merge, For Each Ado Enumerators, Script Tasks, Derived Columns and Recordset Destinations.

In terms of time from within Sql Business Intelligence Development Studio when the packages are executed for 1 month's data the total combined time is about 10 minutes and when deployed as either File System Or Sql Server deployments they run for about 20 minutes. My problem is when they are executed for a years data instead of running for 120 minutes they run for at least 240 minutes or longer.


Is this normal or am I missing some settings when building the package for deployment?

Luke.

View 3 Replies View Related

EncryptSensitiveWithPassword NOT Prompting For Password

Feb 14, 2008

I have 2 tasks. Both of them were set for EncryptSensitiveWithPassword. One of them prompts for the password when inporting to the server and again when the job is scheduled. it puts the DECRYPT statement in the config line. Everything works as it should.

The second task does not prompt for the password at any time. There is no DECRYPT statement added and the task fails to run.

WTF is going on????

View 3 Replies View Related

EncryptSensitiveWithPassword NOT Prompting For Password - AGAIN

Feb 26, 2008

I posted this earlier but did not get a sufficient response.

I have 2 tasks. Both of them were set for EncryptSensitiveWithPassword. One of them prompts for the password when inporting to the server and again when the job is scheduled. it puts the DECRYPT statement in the config line. Everything works as it should.

The second task does not prompt for the password at any time. There is no DECRYPT statement added and the task fails to run.

I have deleted the task and recreated it. I have used dtutil and forced the password. In any case I CANNOT get the job scheduler to see a password is required and add the DECRYPT line, hence I can't execute the tasks.

WTF is going on????

View 2 Replies View Related

Execute DTExec Without Installing SSIS !!!!!!!

Apr 5, 2007

Hello :

On my server, I did not install the SQL Server 2005, I have SQL server 2000, thus I did not install Integration Services.

I want to execute one package by line of command DTExec.

I need what to execute this command without installed SSIS?

Thank you.

View 4 Replies View Related

Error Running SSIS Pkg From Dtexec Utility

Feb 21, 2008

Hi All,

I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.

COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'

ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds

PLease let me know solution to creating batch job for executing this pkg with input param.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved