I would like to create an event handler that would catch any errors that result from a sys.<table> not existing. The package is designed to run on both SQL Server 2000 and SQL Server 2005 and when I query sys.<tables> there is an error when the query is run on SQL Server 2000. I just need a good starting point...I would like something that when the server isn't 2005 it just skips the server and doesn't fail the package and doesn't get counted towards the max error count. Thanks for any help.
-Kyle
I am currently working to write a progress log for my SSIS packages. So far I am able write a new log entry, update this log entry using OnProgress and OnError Event Handlers. I'd like to take it one step further. Whenever the package ends whether cancelled or finished normally; I'd like to write to my logging table COMPLETED_ABNORMALLY on cancelled or COMPLETED_NORMALLY on a normal finish of the package. I'm not sure where to begin with this process. I'd like to utilize a simple method and event handler.
I have an SSIS Package that loads data to a SQL Server table and also logs package statistics along the way with individual SQL statements. In the event of failure, I want the data loaded to the target table rolled back but I want the statistics updates saved to the database. My package consists of several Execute SQL tasks that handle the logging and a Data Flow task that loads the data to the target table along with a couple of event handlers to handle errors. I have the Transaction Option property on the Package set to Required, to Supported on the Data Flow, and to Not Supported on the Execute SQL tasks and the OnError Event Handlers.
When we run the package (and cause an error) everything runs fine until it gets to the On Error event handler for the Data Flow task. This task hangs and never finishes. If we set the Transaction Option for the Event Handler to Supported (allowing it to enlist in the parent transaction) it works but the updates that it makes roll back along with the data from the Data Flow.
Is there a problem with having Event Handlers stay out of a transaction started by the parent package?
event handlers was not executed when my package get fails it will go to directly to on error...not executing on preexecute. but it was working fine previously...i haven't change anything i have run it again...got this issue...
I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be?
We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.
Context: The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.
OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.
Scenario1: Task in OnPreExecute of DFT1 fails. DFT1 is attempted and succeeded. OnPostExecute of DFT1 was not attempted. DFT2 was not attempted. Checkpoint file was created; however, no entries were made.
When restarted, execution started from first step in Control flow.
Scenario2: Task in OnPostExecute of DFT1 fails. DFT1 and its OnPreExecute Event were executed. DFT2 was not attempted. Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.
When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.
Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?
Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?
My project currently has task which have their own individual event handlers that get called onError (setup event messages). I also have a package level event handler that performs a generic task (sending events to the windows eventviewer) In the package level event handler there is a script task that decides on a boolean variable whether to "Success" or "Failure" to different task. When I fail one task of the main control flow, the task level event handler runs, then the package level event handler runs, and then it also runs again for some unknown reason. The second time it runs it picks up the value of a variable set in the variables window. However, I change this value at runtime to the value from a database. I can't understand why it would run the second time, and if it did run why it would have the value from the variables window and not the value that is set in memory. It's like the event handler runs with the value from memory and then runs and picks the values back out of the variables window, replacing the db values and re-runs.
Maybe the package itself is failing all together and then re-runing the package level event handler?
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.
Can anyone give a newbie a general script for creating an SSIS page to import data nightly from an Access database to sql 2005. They are not on the same server. The Access database us 366,968 kb and grows everyday from input from web content. It has 5 tables and 100's of queries, which is how it is updated.
Please give me a starting point, do not understand the info in books on line.
Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.
Open connection and read client location table on the local SQL Server database called PODO For each location id in the table do the following: Store locationid/clientid in a variable called CLLOC_ID Construct file name with mdb extension and store in a variable MDB_FILE Establish connection to the data import folder Search for that MDB_FILE in the folder on the file system If there is a file where match = true then do this: 1) Open the access database 2) Read and import the data from the customer experience table 3) Write that data to the SQL Server tables where location = CLLOC_ID 4) Exit process IF there is no match, exit process Keep looping until all the client ids/loc ids are read from the SQL Server client location table.
I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception. Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal? I am giving my code here too. I will appreciate any kind of suggestion on this.
Code snippet:
public void CreatePackage() { string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName; Package pkg = new Package(); pkg.PackageType = DTSPackageType.DTSDesigner90; ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg); ConnectionManager flatfileConnectionManager = CreateFileConnection(pkg); // creating the SQL Task for table creation Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask); ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask; execSqlTask.Connection = oledbConnectionManager.Name; execSqlTask.SqlStatementSource = "CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO"; // creating the Data flow task Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1"); TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable; MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject; // Put a precedence constraint between the tasks. PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable); pcTasks.Value = DTSExecResult.Success; pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint; // Now adding the data flow components IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New(); sourceDataFlowComponent.Name = "Source Data from Flat file"; // Here is the component class id for flat file source data sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}"; CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate(); managedInstance.ProvideComponentProperties(); sourceDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID; sourceDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);
managedInstance.AcquireConnections(null); managedInstance.ReinitializeMetaData(); managedInstance.ReleaseConnections(); // Get the destination's default input and virtual input. IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0]; // Here I dont find any columns at all..why?? // Now adding the data flow components IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New(); destinationDataFlowComponent.Name = "Destination Oledb compoenent"; // Here is the component class id for Oledvb data destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}"; CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate(); managedOleInstance.ProvideComponentProperties(); destinationDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID; destinationDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager); // Set the custom properties. managedOleInstance.SetComponentProperty("AccessMode", 2); managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]"); managedOleInstance.AcquireConnections(null); managedOleInstance.ReinitializeMetaData(); // Throws exception managedOleInstance.ReleaseConnections(); // Create the path. IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0], destinationDataFlowComponent.InputCollection[0]); // Get the destination's default input and virtual input. IDTSInput90 input = destinationDataFlowComponent.InputCollection[0]; IDTSVirtualInput90 vInput = input.GetVirtualInput(); // Iterate through the virtual input column collection. foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { managedOleInstance.SetUsageType( input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); } DTSExecResult res = pkg.Execute(); } public ConnectionManager CreateOLEDBConnection(Package p) { ConnectionManager ConMgr; ConMgr = p.Connections.Add("OLEDB"); ConMgr.ConnectionString = "Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"; ConMgr.Name = "SSIS Connection Manager for Oledb"; ConMgr.Description = "OLE DB connection to the Test database."; return ConMgr; } public ConnectionManager CreateFileConnection(Package p) { ConnectionManager connMgr; connMgr = p.Connections.Add("FLATFILE"); connMgr.ConnectionString = @"D:MyCSVFile.csv"; connMgr.Name = "SSIS Connection Manager for Files"; connMgr.Description = "Flat File connection"; connMgr.Properties["Format"].SetValue(connMgr, "Delimited"); connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine); return connMgr; }
I'm trying to create a SSIS package. I have used OLEDBSource adapter to get the source table's data and transferring the data to an OLEDBDestination adapter. I tried but I'm facing the problem in mapping the metadata contents.
My code: //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
static void Main(string[] args)
{
// Create a new package
Package package = new Package();
package.Name = "OLE DB Transfer";
// Add a Data Flow task
TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;
taskHost.Name = "Transfer Table";
IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;
// Get the pipeline's component metadata collection
I'm very new to ssis, so I hope I don't sound too ignorant. I was told there might be a way to create User interfaces for ssis projects. ie, I am trying to create an import package that will let a user import an excel file to a sql table. What I would like to have is some sort of web interface that lets the user select the source file and destination table.
The more I search for a way to do this the more it seems impossible without knowing a ton about coding. If anyoen can help I'd appreciate it.
I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts???
I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.
I follow the following steps to achieve this:
01. Created a new SSIS package 02. Create a connection string to "(local)/." server, "tempdb" database 03. Set the "RetainSameConnection" property value to "TRUE" 04. Set the "DelayValidation" to "TRUE", where ever I found this property 04. In Control Flow I added to items a. Execute SQL Task b. Data Flow Task 05. For "Execute SQL task" I set the connection to "tempdb" 06. I written the following query Create table #transfer_CompaniesToProcess_tbl ( companyID int not null ) GO 07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination" 08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command" 09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl" 10. When I clicked on the "OK" button; I ended with following error:
TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".
------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------
I gone through the following article and it seems I missed some thing. http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
Hi All, I have created fact tables and dimension tables in datawarehouse database, and i created a olap cube from those tables. I want to run SSIS Package which populates these fact and dimension tables from datasources.
I have a problem i receive the following error message when i try to add an new step into a SQL Server Agent job :
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Additional information: An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.
Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.
My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.
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.
We want to develop an error handling process that will log the errors into multiple destinations (eventlog, text files or sql database) depending upon a variable set in the package. Also we want that this errror handling process should be initiated by all the tasks in the package on error.
Is this possible? Can the same event handler be called from multiple tasks in the package? Also in the event handler can we call another package which actually does the error handling. This way we have only one place to change our error handling process in case required.
I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.
1)[Lookup [30]] Error: Row yielded no match during lookup.
2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
Can someone help me with this tutorial error? or Am I doing something wrong.
I have a package on which i've applied a package level OnError event handler. The OnError event handler includes a Script Task (that builds up a string of errorCode, errorDescription, MachineName etc...) and a WebService Task that calls a webservice to send an email including the built up string from the script task in the body of the email. This has worked fine in one package where i've applied it but for some reason in a second package the existence of an OnError package level event handler seems to be completed ignored. I'm causing various package object to fail but the OnError handler never fires. I know the obvious answer is find what's different between the two packages but i can't see that any is different (in relation to package level OnError event handling).
Has anyone else come across this? Any suggestions?
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.
I have created an event that contains a Data flow tasks with OLE DB source & Excel Destination.
This event is executed/triggered based on an execute SQL task failure in the control flow Sequence container.
However, when I execute the Data Flow task of the Event Handler, it runs successfully but fails when I execute the whole package.
I get the below error message:
[OLE DB Source [21]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TK463DW" 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 tried setting the property 'DelayValidation' to 'True' on all the Control Flow and Data Flow tasks on the package and on the Event Handler, but still I could not fix this.Not sure What I am missing.
