Creating a VB.net event handler for a Stored Procedure

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.You can script a custom trace that captures the execution of this task that can instantiate a COM object which in turn can do whatever you want it to do.|||I'm new to developement is there anywhere I can learn to do this.|||I don't know of any way to call code in a VB app running on another machine, but you can create a simple COM object and then use sp_OACreate (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp) and the related procedures to launch your COM object on the server.

Will this do what you want?

-PatP|||There both running on the same machine so with a bit of luck I'll be able to get it working.

Thanks

Creating a table in Access from an SSIS package

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?

Did you tried with a script task ? Use the excel connection from the Connection Manager to connect to your Access DB and execute your create table query from a OleDbCommand object.

I've not tried that method, it just a thought.

sql

Creating a package with data flow task programmatically

I have created a simple package with 1 data flow task:
Source: data reader (to sql mobile)
Destination: oledb (to sql server)
And the aim is to do a "Select * from table_name" from source and move everything to destination database.

I have followed the steps in documentation, but the package fails! An exception is thrown with message: "Exception from HRESULT: 0xC0202072"

Since its the basic functionality of SSIS that I am implementing, it should work! I have spent 2 whole days trying to make this work and now its high time to ask for help. Can you please have a look!

Thanks,
Pragya

P.S. Any pointers to samples implementing a data flow task programmatically will help too!

Code:

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

//Add a SQL Mobile connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("SQLMOBILE");
cm.Name = "SQL Mobile ConnectionManager";
cm.ConnectionString = "Data Source =D:\\Program Files\\Microsoft Visual Studio 8\\SmartDevices\\SDK\\SQL Server\\Mobile\\v3.0\\Northwind.sdf";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "SQL Server Connection Manager";
cm1.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

//Adding source component for SQL Mobile.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "ADONETSource";
component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, blicKeyToken=89845dcd8080cc91";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
instance.SetComponentProperty("SqlCommand", "Select * from Employees");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData90 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.SqlServerDestination.1";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
instance1.SetComponentProperty("BulkInsertKeepNulls", true);

//set path between components
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// WORKS FINE TILL HERE -

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Well, 2 things I notice just from a first look is:

1. You don't set the ConnectionManagerID just the ConnectionManager, but both need to be set.

2. You don't specify a table or sqlstatement for your destination so how does the destination know what table to reinitialize its metadata for?

HTH,
Matt|||1. ConnectionManager.Id is a read only property. How to set it?
2. How can I do that?

Thanks|||

Ok! I got through one step. I added the following line:

instance1.SetComponentProperty("BulkInsertTableName","[Employees]");

to solve this.

However I have related questions,

1. Now the metadata can be initialized, but no rows are being transferred. Something is still amiss...

2. I need to manually create the employees table currently. How can i code the 'create table/view' option so that the table can be generated via code? Do I need to first compile the create table statement with column type information and then execute it using sql client? Is there a better way?

Thanks!

|||

Any information on this would help.

Thanks