I'm working on an application that involves various components two of which are an SSIS package and a C# windows service.
The SSIS package imports data from a number of structured flat files into database tables and then runs a number of stored procedures that rearrange/normalize the data and perform certain calculations the results of which are also stored in the database. The package will run at regular intervals as new data structured flat files become available.
The C# windows service is a caching server and the data it needs to cache is the results of the stored procedures run by the SSIS package. I'm therefore looking for a way to notify the C# app when the SSIS package has completed.
I've looked at Sql Servers Query Notification but, if i've understood correctly, that seems to depend on the client code issuing a SQL statement and attaching a sqldependency object that will check for changes to the resultset of the particular query which is not really what i'm after.
I've considered SSIS events but again i think for a C# app to monitor SSIS events the package needs to have been executed from within the C# app. If there's a way for the SSIS package, running completely independently of the C# process, to fire an event that the C# windows service can subscribe to then that would be ideal.
I don't know whether other options like Sql Server Notification Services or even something quite crude like writing a flag to a flat file that the C# service can read would be more ideal?
Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle? Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side? Any article or knowledge articles would be appreciated also.
If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS? That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle. I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.
Is there a way to trigger a SSIS package automatically when it ends successfully. Actually i am moving data from one database to other.......i am passing start date and end date as my paramter...So is there some way once my SSIS package ends succesfully then it can start again on its own reading a new set of date range from a file or something....
Is there a way (during the Control Flow) to force a package to complete successfully when a specific condition is met?
Here is what I'm trying to do:
A package will be scheduled to execute once a day The first thing it does is downloads Excel files from an FTP site. If there are no files to download, I don't want the package to fail, I simply want the package to stop (preventing the subsequent Data Flow tasks from executing), returning a successful completion.
The reason, I need a successful completion is because I plan to have MOM monitor the Windows event logs, notifying us of any errors that this package logs.
I want to set multiple child packages running without waiting for them to complete in a parent SSIS package. The catch is that I will be running the same child package in multiple threads with different configuration variables set. I want to drive the config variables for the child packages via a SQL Server table, and start the execution of each child package from within a for-loop container.
Here's what I've tried, and why it didn't work:
1) Execute package task. Didn't work: Waits for the child package to complete before moving to the next step.
2) Kicking off the package via the "sp_start_job" stored proc in the msdn db. Didn't work: Can't specify config variable values.
3) Using the DTExec command line prompt. Didn't work: Same issue as 1). Waits for the package to complete.
Anything I'm missing? Any ideas on how to accomplish this?
We have an application comprising of a number of SSIS packages that run every few hours. When a package fails, we would like an email to be sent to a pre-configured address with the required information. I would like to use Send Mail task with SMTP server like smtp.sbcglobal.yahoo.com, but this server requires authentication and I need to provide my user_id and password for that. I have not been able to figure out where I can configure user_id and password for getting authenticated by the SMTP server. If SMTP connection cannot be used, is there any other way to notify admin of the failure?
i've scheduled a few ssis packages as sql server agent jobs. is there a way to get a daily notification via mail where all planned and all processed jobs are listed?
I'm trying to reinstall SQL Server 2005 STD but I cannot get Setup to install Integration Services or Notification Services. I looked in the log and this is what I found. Needless to say, it makes no sense to me. It seems that Setup is trying to find a missing source list for a certain product. I have no clue what that product is. I've spent endless hours trying to figure it out and I'm still drawing a blank. This is a printout of the log.
Integration services - ---------------------- MSI (s) (F0:B0) [02:33:01:437]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (F0:B0) [02:33:02:188]: End dialog not enabled MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:ServersSetupSqlRun_DTS.msi MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:WINDOWSInstaller188966.msi MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: looking for appcompat database entry with ProductCode '{EE8CFFD9-6E29-4DC3-A967-7348D5F41F44}'. MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (F0:B0) [02:33:02:339]: MSCOREE already loaded, using loaded copy MSI (s) (F0:B0) [02:33:02:379]: Couldn't find local patch ''. Looking for it at its source. MSI (s) (F0:B0) [02:33:02:379]: Resolving Patch source. MSI (s) (F0:B0) [02:33:02:379]: User policy value 'SearchOrder' is 'nmu' MSI (s) (F0:B0) [02:33:02:379]: User policy value 'DisableMedia' is 0 MSI (s) (F0:B0) [02:33:02:379]: Machine policy value 'AllowLockdownMedia' is 0 MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Media enabled only if package is safe. MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Looking for sourcelist for product {8B7E2749-624E-4C61-92FC-2BC18307FF24} MSI (s) (F0:B0) [02:33:02:379]: Note: 1: 1706 2: {8B7E2749-624E-4C61-92FC-2BC18307FF24} 3: MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Failed to resolve source MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 1708 MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 2729 MSI (s) (F0:B0) [02:33:02:409]: Note: 1: 2729 MSI (s) (F0:B0) [02:33:02:409]: Product: Microsoft SQL Server 2005 Integration Services -- Installation failed.
MSI (s) (F0:B0) [02:33:02:439]: MainEngineThread is returning 1612 The installation source for this product is not available. Verify that the source exists and that you can access it. G:ServersSetupSqlRun_DTS.msi MSI (c) (B8:88) [02:33:02:439]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (B8:88) [02:33:02:439]: MainEngineThread is returning 1612 === Verbose logging stopped: 5/19/2006 2:33:02 ===
----------------------- Notification services - ----------------------- MSI (s) (F0:30) [02:33:07:566]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (F0:30) [02:33:08:087]: End dialog not enabled MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:ServersSetupSqlRun_NS.msi MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:WINDOWSInstaller18898f.msi MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: looking for appcompat database entry with ProductCode '{37E9AD9F-3217-4229-B5A5-7A0C82364C6C}'. MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (F0:30) [02:33:08:167]: MSCOREE already loaded, using loaded copy MSI (s) (F0:30) [02:33:08:187]: Couldn't find local patch ''. Looking for it at its source. MSI (s) (F0:30) [02:33:08:187]: Resolving Patch source. MSI (s) (F0:30) [02:33:08:187]: User policy value 'SearchOrder' is 'nmu' MSI (s) (F0:30) [02:33:08:187]: User policy value 'DisableMedia' is 0 MSI (s) (F0:30) [02:33:08:187]: Machine policy value 'AllowLockdownMedia' is 0 MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Media enabled only if package is safe. MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Looking for sourcelist for product {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1706 2: {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} 3: MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Failed to resolve source MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1708 MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729 MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729 MSI (s) (F0:30) [02:33:08:187]: Product: Microsoft SQL Server 2005 Notification Services -- Installation failed.
MSI (s) (F0:30) [02:33:08:197]: MainEngineThread is returning 1612 The installation source for this product is not available. Verify that the source exists and that you can access it. G:ServersSetupSqlRun_NS.msi MSI (c) (B8:DC) [02:33:08:207]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (B8:DC) [02:33:08:207]: MainEngineThread is returning 1612
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.
I'm trying to create a SSIS package that will send me a email when the package runs successfully and when it fails. My entire package runs successfully until it get to the Send Mail Task, where it always fails. Do I have to configure something on the database end or what? Thank you in advance.
Using SQL Server 2005 Using sample database AdventureWorks
We are installing 2 instances of sql server in cluster env.
When intalling "Default" instance, I m getting Notification services, Integration, reporting services for default instance.
My question is : When we intall second named instance on cluster, will named instance share Notification services, Integration, reporting services with default instace.
So if above is yes, do I have to install Notification services, Integration, reporting services on C: drive of cluster nodes, so that it is shared by all the instances on the cluster nodes.
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"
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?
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.
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.
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.
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?
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
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.
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...
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.
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?
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.
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).
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:
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?
when i run the job using network service account credentials job is failing. But when i run the package individually, it is tasting success. when it runs as job, this is the error message i am getting
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I have changed to 32-Bit run time and ran the excel package, even then it is failing...i tried to use my credentials (i am admin on the box), even then it is failing...please suggest
I want to achieve the following in (SSIS/SSDT for SQL 2012) -Â
I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).
I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.
I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?
When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.
I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.
I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages €¦ just not my new one.
Any thoughts why the agent will not run the child newly crated childe package?
Dear Friends, I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database. How you think is the better way to do that? Using a FlatFileSource to read the file and a script to save the value into a SSIS variable? Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable. How the best way you suggest?! Regards!! Thanks.
