MS SQL Server :: LOGGING FOR SSIS Package

Logging Data Flow Steps In SSIS Package
Hello.

I am using the "SSIS Log Provider for SQL Server" to log events to a table for "OnError" and "OnPostExecute" events of a package. This works as expected and provides a nice clean output on the execution steps of the package.

I am curious as to why I do not see any detail for any/all tasks that fall under the "Data Flow" section of the package though. For instance, on my "Control Flow" tab, I added a "Data Flow" task that simply loads a few tables from a target to destination server. However, there is nothing shown in the logging output. Just that a Data Flow task was initiated. And when I'm configuring this logging under "SSIS-->Logging" in the checkbox area on the left, you cannot "drill into" data flow steps.

Is there a reason why there is no detailed logging for Data Flow tasks? Would getting to that require me to create a custom log provider?

Thanks for the help.

Greg

View Replies ! View Related

Logging Of SSIS Package Doesnt Work When Executed As A Job
I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.

The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.

I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.

Thanks

View Replies ! View Related

SSIS Logging Issues When Package Run From Job/Command Line

All,

I am logging all the tasks in my SSIS package to SQL Server. For each task I am logging atleast the Pre-Execute, PostExecute, OnError events. For Script tasks, I have custom logging and I am logging the ScriptTaskLogEntry event too.

When I run the package manually from BIDS, the logging works great ! But when I try to run the package from a job or from the command line, the number of events that is logged greatly reduces. For eg. when manually run, I get 104 records in the log table but when run from the command line I get 23 records only. Most of the custom logging messages from the Script Task do appear. Its the pre and post-execute events that are skipped. Any idea why ?

Here is command line from the job. I also use the same command line with "dtexec" from the command prompt.

/FILE "\MyServerprojectsSSISPackagesIRRLoadingPackage.dtsx" /DECRYPT mypwd /CONNECTION "ATLSQLDEV01.SSAM.User1";"Data Source=ATLSQLDEV01;User ID=user1;Initial Catalog=SSAM;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;" /CONNECTION CashFlowConnection;"\MyServerpublicReportingLoadingInProgress8634_Cashflow.txt" /CONNECTION "SMTP Connection Manager" ;"SmtpServer=ATLEXCH01;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /LOGGER "{59B2C6A5-663F-4C20-8863-C83F9B72E2EB}";"LogFile.txt" /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"ATLSQLDEV01.SSAM.User1"

Thanks in advance for your help.
Amir

View Replies ! View Related

Logging Package Name For Any Event In Sysdtslog90 Logging Table

Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.

How do I configure the package to always log the package information into the table, too?

Best regards,
Stefoon

View Replies ! View Related

Tie Together Custom Logging And SSIS Logging?
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.

View Replies ! View Related

Where Is DTS Package Logging ?
Dear all,

I am using SQL Server 2000 and specify DTS package logging by.

1. Open a DTS package.

2. Package -> Properties. Select tab logging. Check for "Log package execution to SQL Server" Then, specify correct user and password. I also check "Fail package on Log error'"

3. In Error handling, I specify the full path of error file on client machine and check "Fail package on Log error" and "Write completion status to event log"

I cannot find the error log. Where is DTS package logging ?

Is it the same as database log ? Does it contain execution time in each node?

Best regards,

CJira

View Replies ! View Related

Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

View Replies ! View Related

Programmatically Add Logging To Package
This is a sample for adding logging to a package programmatically.

It's a simple process, but it took some time to find all the pieces required so I wanted to share it for others who can use this in the future to save them the time I spent it.

        _Package.LoggingMode = DTSLoggingMode.Enabled

        Dim Log As LogProvider = _Package.LogProviders.Add("DTS.LogProviderEventLog.1")
        Log.Name = "Windows Event Log Logging Provider"
        Log.Description = "Logs Event info to Windows Event Log"
        _Package.LoggingOptions.SelectedLogProviders.Add(Log)

        Dim LoggingOptions As LoggingOptions = _Package.LoggingOptions
        LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion

        Dim Events As String() = {"OnError", "OnTaskFailed", "OnWarning"}
        LoggingOptions.EventFilter = Events

        Dim EventColumnFilter As DTSEventColumnFilter = New DTSEventColumnFilter()
        EventColumnFilter.Computer = True
        EventColumnFilter.Operator = True
        EventColumnFilter.SourceName = True
        EventColumnFilter.SourceID = True
        EventColumnFilter.ExecutionID = True
        EventColumnFilter.MessageText = True
        EventColumnFilter.DataBytes = False

        LoggingOptions.SetColumnFilter("OnError", EventColumnFilter)

View Replies ! View Related

SSIS Logging
What all would need to be done in order to capture a time that an SSIS package ran and completed? I have had a thread about this before here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1310953&SiteID=1

But, I have been busy and have been off and on with this issue. I really need to find some simple way to show in a report using SSRS the "as-of" date when the data was pulled in. Is using the Execute SQL task going to be that method?

Any information is greatly appreciated.

Thanks

View Replies ! View Related

Logging In SSIS
Hi:

Just implemented logging on my package, and the type of logging is to a text file. Any ideas on where should i store my log folder/file, so when I do my build and deploy, I end up moving the folder as well. Do I have to manually do that? How would that work? What if I save the log file outside of my project folder? Where is the log folder/file path specified? Thanks.

View Replies ! View Related

SSIS Logging
Hi,

I have migrated DTS package into SSIS. One of the ActiveX Script contains the following code. I have a question regarding changing this code.

What is the equilaent object of reading LogFileName in SSIS? I setup the Logging in SSIS, but didn't fidn any option using which i can refer to logfile as it is done in the following DTS code.

It'll be a great help if somebody can assist me in this.

Thanks in advance.

Neetu

Code:

Function Main()

Const ForReading = 1, ForWriting = 2, ForAppending = 8

dim fso

dim pkg

dim filename

dim srcfile, logfile

dim inchar

set fso = CreateObject("Scripting.FileSystemObject")

set pkg = DTSGlobalVariables.parent

set logfile = fso.OpenTextFile(DTSGlobalVariables.parent.Properties("LogFileName").Value, ForAppending, True)

filename = Pkg.Connections("CARSBalance").Properties("DataSource").Value

If (fso.FileExists(filename)) Then

set srcfile = fso.OpenTextFile(filename, ForAppending)

' Add a blank character to the file.

srcfile.Write " "

srcfile.Close

set srcfile = fso.OpenTextFile(filename, ForReading)

'Read a line from the source file

inchar = srcfile.Readline

If Len(inchar) <= 1 then

'Have to close the file, then open ForWriting

srcfile.Close

logfile.WriteLine ("File is empty: " & filename)

logfile.WriteLine ("Adding dummy record")

set srcfile = fso.OpenTextFile(filename, ForWriting)

srcfile.WriteLine(" ")

End If

Main = DTSTaskExecResult_Success

srcfile.Close

Else

logfile.WriteLine

logfile.WriteLine ("$$$$$$$$$$$$$ File does not exist: " + filename + " $$$$$$$$$$$$$")

logfile.WriteLine

SuccessReturncode = -1

Main = DTSTaskExecResult_Failure

End If

logfile.Close

End Function

View Replies ! View Related

SSIS Logging
I am executing the SSIS package and generating the execution log information in SQL server and text file.

i.e. using the SSIS---->logging and there SSIS log provider for SQL server and SSIS log provider for text file.

Now I want to push the execution information of all the events in SQL server whereas in text file I want to log only the OnError events.

Now I am unable to to select the different set of the events for two log provider (SQL server and text file)

Please suggest how to do this.

View Replies ! View Related

Logging In SSIS
Logging in SSIS as compared to DTS is more complex to set up with so many events. What events should I choose if I need something similar to DTS package logging i.e. I simply want to see which tasks executed at what time and whether they failed or succeded and if they failed, what was the error?

View Replies ! View Related

Logging In SSIS
Hi,

My job is to create an audit report. I have two tables A and B which is linked through a common field Id.

if Id column of A table doesnot find value for Field1 in B for the corresponding Id then an error should be reported as follows:

"Missing record in B"

if Id column of A finds value for Field1 but Field2 doesnot have value then warning should be generated.

These Errors and warnings should be logged in Audit Report file.

Can any one guide me how to do it?

View Replies ! View Related

SSIS LOGGING
Second time around w/this one. I'm running my sqlserveragent with a domain admin account and everything works well. I then switch to a domain user and the package fails. Windows app log contains ths

Package "Package1" failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

So I go to the sql server agent jobs and select the job I'm trying to run. I then go to Job Step Properties and under the logging tab choose SSIS log provider for Text files. What is supposed to go in the Configuration String?

It will let me type a text file in there or choose from one of my "3" datasources in my package. But since I'm developing this on another machine the choice to log to the file system makes no sense.

Please do not refer me to the kb article (http://support.microsoft.com/?kbid=918760) I've read and re-read that. All that I want to do is find out why the package is failing.

Thanks.

View Replies ! View Related

SSIS Logging
Anyone know if there is a way to make the package only log errors? I only have errors checked, but it still logs the package onstart and onend events. Is there a way to turn this off?

Thanks

View Replies ! View Related

Embedding A DTS Package And Logging/error Reporting
Hi All,

When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:

COMException - error returned from a call to a COM component.

Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?

Cheers,

Tamim.

View Replies ! View Related

SSIS Logging Options
Hi,
What SSIS logging option should I check to log the success of each component? In log file, I'd like to see what component is currently SSIS executing and what component(s) passed the execution.
Thanks

View Replies ! View Related

SSIS Logging Feature
I am currently using the SSIS Logging feature in my SSIS package. Currently, I have defined a destination log file, and each time the package is executed the log file gets appended with that days log.

Im trying to figure out how best to keep the log file name static (it gets emailed out, and my email client looks for a particular log file name) yet include only todays log information and append the rest of the log information to a history log file or something like that.

Has anyone tried doing something similar, or have any ideas on how best this can be accomplished?

Thanks

View Replies ! View Related

Logging Exceptions In SSIS
Hello Everyone,

I have written a custom task which writes the details of an error in the windows event log.

I use the on error event to invoke this task.

To simulate the error I try to convert a string into a number. I see that there are multiple as many as 6 messges with different error codes being logged into the windows event log.

They vary from something tangible like number conversion failed to obsure things like thread was being aborted.

Is this normal? to see multiple messages for one type of error.

Can I control this, so that only one message is sent into the windows event log.

(reason is that we have a monitoring software which may raise multiple admin alerts for the same issue).

Thanks for your help in advance.

regards,

Abhishek.

View Replies ! View Related

SSIS Performance Logging
Hi,

Can anyone tell me the best way in SSIS to log performance at control flow level i.e. per task I have in my control flow and what performance characteristics it is possible to log.

Thanks in advance

View Replies ! View Related

SSIS ScriptTask Logging
Hi. I've tried several things to log some information inside of the ScriptTask. I've tried Console.WriteLine(), Debug.WriteLine(), and Dts.Log().

All compile and work, but my problem is I can't find the output to save my life.

I have logging turned on in my SSIS package, but the data doesn't show up in that file. I've check SQL Server's log. Event Log in OS. All no luck.

Please help.

View Replies ! View Related

SSIS Event Logging ...
When running SSIS packages, the package execution information is logged in the SYSDTSLOG90 table which contains the following columns.

id
event
computer
operator
source
sourceid
executionid
starttime
endtime
datacode
databytes
message

After executing a package, I found that the values in the "executionid" column are the only ones that are unique. Can we use this to determine what package was run? We are trying to architect a solution that would allow us to determine as to how long a package ran, if it ran into warnings / errors etc., We can easily accomplish this by having our own table and using Global variables within packages, we could insert / update this table. Appreciate any help.

View Replies ! View Related

Duplicate Logging In SSIS
I am having a problem where duplicate log statements are being written to a log file (as defined by a log provider).

I believe that this is because in the logging dialog box, I have ticked the checkbox next to a child task to override the logging functionality.
I need to do this because it is a script task and I want to capture "ScriptTaskLogEntry" events (something that I cannot do at the parent level).
However by doing this I seem to get the script events written at the parent, as well as at the Script Task level.

Is there any way of avoiding this, but still capturing the log events from the script task?

Another issue that is possibly linked is that I am getting an error from the log provider:

The SSIS logging provider "SSIS log provider for Text files" failed with error code 0x800700EA ((null)). This indicates a logging error attributable to the specified log provider.

Could this be because of the parent and child task are both attempting to write to the same log provider?

Thanks in advance

View Replies ! View Related

SSIS Logging Problem
I'm trying to use the information logged by SSIS packages into the database (the sysdtslog90 table) to track errors. Unfortunately, when the errors are written to the log they are associated with a source and sourceid and not the package and package ID. Sometimes the source is the package, but often it is a process within the package. All I need is some way to associate each error with a package--a package name/ID on each row in the log. This seems like it ought to be a pretty basic need, but I can't figure it out.

Your help is greatly appreciated.

devin

View Replies ! View Related

Logging Difference Between Designer And SQL Agent Package Executions
After fine-tuning my package logging, I built a query against SYSDTSLOG9 that uses the combination of the OnPreExecute events and any subsequent event for each task to build a nice view of a package in progress, including the duration of each event. (Running tasks would have a row with a NULL EndTime.)

When running the package within the Designer, everything works as expected. Events are logged at the task level and everything bubbles-up to the package level.

However, when I run the package from SQL Agent (using the SSIS job step), none of my OnPreExecute or sub-component events are written. That is, I only get package-level OnInformation, OnPostExecute and OnError events.

This means when the job/package is executing, I can't directly see which steps are currently in progress nor can I get a duration for each step once it's completed. This will be very disconcerting for my teammates who will have to help support this at some point.

Any idea how or why there would be a change in behavior between these two execution methods?

Thanks in advance for any help. Until then, banging my head against the wall...
Tim

View Replies ! View Related

Removed Logging And Connection Manager And Hosed The Package
I enabled logging by right clicking on the control flow and specifying a log provider.
after a while, i deleted the log provider from the tree view and the connection manager it was using from the connection manager collection.
now i cannot open the package.
here are the errors i can collect from the failure;

Error 1 Error loading ImportMedsSubmissionFiles.dtsx: Element "{A4F838A2-2660-4422-A0D2-669BAFBC6CAE}" does not exist in collection "LogProviders". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1

Error 2 Error loading ImportMedsSubmissionFiles.dtsx: Error loading value "<DTSelectedLogProvider xmlnsTS="www.microsoft.com/SqlServer/Dts" DTS:InstanceID="{A4F838A2-2660-4422-A0D2-669BAFBC6CAE}"/>" from node "DTSelectedLogProvider". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1

Error 3 Error loading ImportMedsSubmissionFiles.dtsx: Error loading value "<DTS:LoggingOptions xmlnsTS="www.microsoft.com/SqlServer/Dts"><DTSroperty DTS:Name="LoggingMode">2</DTSroperty><DTSroperty DTS:Name="FilterKind">0</DTSroperty><DTSroperty DTS:Name="EventFilter" DTSataType="8">18,7,OnError,19,OnExecStatusChang" from node "DTS:LoggingOptions". Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1

Error 4 Error loading 'ImportMedsSubmissionFiles.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . Visual Studio 2005ProjectsMeds2ImportMeds2ImportImportMedsSubmissionFiles.dtsx 1 1

is there some file to edit or a way recreate or revive the package, even if it means just duping the package into believeing what it wants is there?
as a baseline altertnative, is there any way to retrieve the work?
it consists of a boatload of derived column transformations of way too many columns that i would really rather not rewrite, so if its lying about on my drive somewhere in an xml file that can be used to recreate what i had, it would be just ducky.

thanks
drew

View Replies ! View Related

How To Add Error Logging File In SSIS
Just like in DTS where we can add error file so that if the DTS package fails we can see what caused the DTS to fail, likewise do we have anything like error logging file in SSIS. I greatly appreciate your help on this. thanks!!

View Replies ! View Related

SSIS Logging -- How To Overwrite Old Log File.
After logging is configured in SSIS package, it seems that after each execution the output is appended to the log file (we are talking about log provider for text files in this case). As a result the file just keeps on growing. I would like to overwrite old information with each run, but I can't find where to configure this. Anybody knows?

Thank you!

View Replies ! View Related

SSIS Data Migration Logging
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.

2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target
containers.

3) We have a master execute package which contains series of execute packages in relational order.

4) We want to log Insert Count which is not a system variable i.e. basically @@ROWCOUNT i.e. one per data flow/migration or source cum target tables combination. How do we achieve this? Can I make use of inherent table INSERTED? But there is update also involved & I need to log update count against package/table name.

5) We also want to mail/log details of error for OnError and warning for OnWarning i.e. what we get in debug mode when we execute packages in BIDS. Please post links if the steps/code is more to post.

View Replies ! View Related

Logging All SSIS SQL Statements For Audit

Has anyone attempted (with success) to capture the sql command text from SSIS packages at runtime for logging?
What approach was used?

ProjectREAL used a stored proc to execute all sql statements.
This seemed rather poor in design (formulate a string to pass to the sp just to log the sql command text).
This especially seemed problematic as the stored proc would have to be on the source system.

I have been trying to store my sql in variables which were set to eval as expressions and was hoping to use an ExecuteSQL task in the PreExecute event handler for each task, source, transform or destination which I required logging of the sql command text.

Problem is that, depending on how the expression is formulated (with coding parameter markers or replacing parameter's markers with values) I might only get the pre-parameter replacement version of the sql command text rather than the final parameter replaced sql command text. Also not sure that this design would work with destinations.

Any tricks of the trade to share?

View Replies ! View Related

Issues With Logging Option In SSIS

Hi,

I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.

Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.

Vivek S

View Replies ! View Related

SSIS Logging To Flat File Destination
Hi

I am trying to use a conditional split task so that I can check for specific fields. If the value doesn't exist I am piping the records to a derived field task, where I add an error. I then try to send these records to a flat file destination so that I can keep track of them. However, when I execute the SSIS data flow task I get the following error

[Log Invalid Records [5496]] Warning: The process cannot access the file because it is being used by another process.

This file isn't being used by any other process as far as I can tell, and the only process using it is the SSIS task trying to write to it.

If anyone has any ideas, then I would really really appreciate it

Thanks

Darrell

View Replies ! View Related

SSIS - Built-In Logging Execution ID Problem
Hi

I'vo got some trouble with the built-in logging feature and a self defined logging table.

Scenario :

A have a SSIS-package with enabled built-in logging to SQL-Server Data-Provider.

At the start of the package, i have an SQL Task which logs

the System Variable system::ExecutionInstanceGUID into the self defined logging table.

But the execution_id which the built-in logging stores to sysdtslog90 isn't the same

value like the value of the system::ExecutionInstanceGUID,

so i can't link my own table with the sysdtslog90 - table.

Any Ideas what is going wrong?

Thanks and sorry about my english

Ivo Becker (Switzerland)

View Replies ! View Related

SSIS Data Flow Execution Monitoring And Logging
Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package.

It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated.

Any ideas will be greatly appreciated.

View Replies ! View Related

Logging Data Flow Component Events In SSIS
Does anyone know how to hook up to data flow pipeline events via custom solution (C#)? I am trying to write code to log start and end times of components(lookup,merge joins etc) in a data flow task. I tried with a class inheriting from the EventsProvider class but it didn't work as this is only for container tasks. Any ideas will be greatly appreciated.

View Replies ! View Related

Error While Executing SSIS Package From Other SSIS Package
Hi,

In our project we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

View Replies ! View Related

Error Stating Package Failure While Executing SSIS Package In Standard Edition
Hi,

I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.

But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.

Can any one help me how to over come this problem.

View Replies ! View Related

Passing Execute DTS Package Result (success/failure) To Calling SSIS Package
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.

As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.

Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?

If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?

View Replies ! View Related

Several Different Errors When Creating And Running SSIS Package. Package Runs Successfuly Sometimes.
Hi All,

I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.

Sometimes when I am creating a package I get the following error in a message box:

SQL Server Import and Export Wizard

The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.

Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)

Other times when I run a package that has run successfully before I get the following error:

Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.

Other times I get this error message:

.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)

And still other times

The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.

I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.

Does anyone have any other suggestions to try?

Thanks.

View Replies ! View Related

Is It Possible To Change Or Replace The Default Package Template Named New SSIS Package?
I would like to standardize SSIS development so that developers all start with the same basic template. I have set it up so it is an available template ( http://support.microsoft.com/kb/908018 ) but I would like it to be the default when a new project or package is created. Is this an option?

View Replies ! View Related

Problem When Running A SSIS Master-package-child Package On Non Default Sql-server Instance
Hi there

We have a SSIS run which runs as follows

The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.

The package is run from the file system, through a job-schedule.

We experience the following when running on a not default sql-server instance (called dkms5253uedw)

Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.

The master package then executes the first child package. We then in the sysdtslog get:

Error   - €œcannot connect to database xxx€?
Info     - €œpackage is preparing to get connection string from parent €¦€?

The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.

Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.

Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.

Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with

Error   - €œtimeout when connect to database xxx€?
Info     - €œpackage is preparing to get connection string from parent €¦€?

And the continues as in the first case

From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).

We have tried delayed validation in the packages and in the connection managers, but with the same results (error).

So we are desperately hoping that someone can help us solve this problem.

Thanx,
/Nils M - Copenhagen

View Replies ! View Related

Passing Value From A Child Package To The Parent Package That Calls It In Ssis
hi,

I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.

I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.

I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...

Any suggestions?

Thanks for any help in advance..

smathew

View Replies ! View Related

How To Compose The Connection String Of A SSIS Package That Execute Another Package?
Dear All,

I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?

Regards,

Strike

View Replies ! View Related

Report With SSIS Package Having Indirect Package Configuration Setup
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.

The Report works when Direct package configuration is used to same dtsconfig file.

What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.

View Replies ! View Related

Problem Setting Package Variables In SSIS Package
Hi,

I am making use of the DtUtil tool to deploy my package to SQL Server.
Following is my configuration:
32-bit machine and 32-bit named instance of Yukon.

I have some package variables which need to be set in the code.

Previously I did it as follows:

Set the package variables in the code. For example:

pkgFile.Variables["User::DestinationServerName"].Value = <myvalue>

Deploy the package as follows:

applnObj.SaveToSqlServer(pkgFile, null,
destinationServer, null, null);

Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.

Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool.
Now I am doing it this way:

Set the package variables as before.
Deploy the package to SQL Server using DTUtil tool.

Now is the problem:
The package is successfully deployed. But the variables are not set to the value that I have specified in the code.

I also tried DTexec utility to set the package variable. Even that does n't work.
Can anyone help me out? Is there any alternate method to set package variables?

Thanks,
Sandhya

View Replies ! View Related

Schedule A SSIS Package Which Execute DTS 2000 Package

I have successfully created a SSIS package which execute a DTS 2000 package and with no problem to execute the task. But I failed to schedule this package. I was not success in setting the logging. When running the package in command line:

dtexec file "C:Documents and SettingslyangMy DocumentsVisual Studio 2005ProjectsTraingDTSTraingDTSDTSTraining.dtsx"

Error: 2008-03-24 08:03:24.36
   Code: 0xC0012024
   Source: Execute DTS 2000 Package Task
   Description: The task "Execute DTS 2000 Package Task" cannot run on this edit
ion of Integration Services. It requires a higher level edition.
End Error
Warning: 2008-03-24 08:03:24.38

   Code: 0x80019002
   Source: DTSTraining
   Description: The Execution method succeeded, but the number of errors raised
(2) reached the maximum allowed (1); resulting in failure. This occurs when the
number of errors reaches the number specified in MaximumErrorCount. Change the M
aximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).

Any help will be greatly appreciated.

(32 bit machine, standartd edition of SQL 2005)

View Replies ! View Related

SSIS Parent Package Can Not Find The Child Package
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.

View Replies ! View Related

LOGGING FOR SSIS Package

Hi I want to have logging enabled for my SSIS package and have the logs wriiten to an log in the event viewer. How can I do it. Thanks Sai

Hi

I want to have logging enabled for my SSIS package and have the logs wriiten to an log in the event viewer.

How can I do it.

Thanks

Sai