Custom Stored Procs: Determining source table name

Hello all ...

I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.

It looks like I can get the data values passed as parameters just fine.

I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.

I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.

A handy trick or technique would be helpful!

Thanks!

DB

To answer your question, no the distribution agent does not and cannot pass the table name as a parameter. Since the custom procs are per article, it is expected the table name to be included in the proc.|||

OK, I'll work with that.

Thanks very much!

DB

Custom Source extension & file permissions

I programmed a Source extension, that accesss a text file. In my code I have the following code to open a file:

protected override void reader(PipelineBuffer buffer)

{

TextReader reader = File.OpenText(_filename);

The komponnet works well. How do I grant permissions to access this text file? Because I got problems when I try to run my package using a task created in the SQL Server Agent.

This sounds like a how do I use windows file security question, not a SSIS issue at all. Simple, use Windows Explorer, and select the drive, folder or file and right-click to access the security tab. Is that really what you are asking?

Have you determined the security context (account) used by SQL Agent? You will of course need to do this to grant that account with the correct permissions.

This also seems unrelated to the component used.

|||

I coudn't access the resources. I used the following \\server\share

And it works!

Custom Source Component Problem?

Hi,I have developed a custom source component to get data from a generic odbc connection with some special caracteristics.

The component works fine by getting and mapping the output fields etc...

The only two problems existing are that when i run the task it says that the data flow has no components inside... how is this possible since i have my source mapped to a flat file inside that data flow?

This is the error:

SSIS package "BVEIT000D.dtsx" starting.

Warning: 0x80047034 at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

Information: 0x4004300C at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: Execute phase is beginning.

SSIS package "BVEIT000D.dtsx" finished: Success.

The other problem is that if i want to <ignore> a certain source column the component already shows me an error saying that the no column with ID 0 was found...

Any one with experience in creating custom components?

Regards,

Hi Luis,

I might be able to help you but I need some more information. Are you creating the package using the Designer or programatically? Do you get an error when adding the component to the data flow?

It's hard for me to determine what is going on without seeing the package. Could you post more information about how you're adding the component to the package?

Jason
|||

Yes i am creating the package using the designer and no error is ocurring... everything works just fine...

I add the component and the file destination i set my component sql to return the fields i map the fields to the file component and then when i run the task it simply says that my data flow doenst have components? Jesus...

I hope you can help me here!

Regards,

|||Have you tried creating a new empty package and adding a Data Flow and your custom source?

|||

Yes, same thing happens...

This is very very weird...

|||I agree.

Could you post the DtsPipelineComponent attribute settings you are using?

It might help to see your actual package too. If you want, you can send it to me at jason.gerard AT idea.com

|||

Ok this is my attribute settings:

<DtsPipelineComponent(DisplayName:="WV ODBC Generic Source", Description:="Data Flow Task that allows you to freely return data based on a SQL Statement against a Generic ODBC Connection", ComponentType:=ComponentType.SourceAdapter, IconResource:="WorldVision.LuisSimoes.SQLServer2005.DataFlow.Sources.WVODBCSource.ico")> Public Class WVODBCSource

Inherits PipelineComponent

I think is pretty normal...

As for the component i sent it by email to you.

Regards,

Custom source adapter UI

I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).

My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?

Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.

Thanks

The transactional edit work by cloning the component - the UI works only with the component clone, and can use all the functionality of the component. So when the component property changes, your UI should apply the changes to the clone, the component (clone) refreshes it output columns and then UI can show refreshed list of output columns. After user closes the UI, the changes are applied to the original component.|||

great, thanks for the response

Custom error messages

Some of our reports use a stored proc as the source for base data. The proc contains RAISEERROR statements to handle error scenarios.

Is there a way in which I could display the message that is output from the RAISEERROR statements in the report?

ThanksThis is not natively supported. You would need to return it as a data column.

Custom Data Flow

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

ssiscoder wrote:

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

No you can't do this currently. Well, not with the out-of0the-box components anyway.

What you CAN do is build a package programatically in dotnet using the SSIS API. That dotnet code can be called from any place that can execute dotnet code. One of those places happens to be a SSIS package.

HTH.

-Jamie

|||

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

|||

ssiscoder wrote:

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

I can do better than that. here's a sample:

http://msdn2.microsoft.com/en-us/library/ms161541.aspx

-Jamie

|||

This might help as well: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

-Jamie

Custom conflict resolver example code in C#

Hi!

I'm trying to create a custom conflict resolver for SQL Server 2000 in

C# but i can't seem to find any source code examples of a *.dll where i

can actually see what needs to be done.

Any help is welcome.

Thanks

In SQL 2000, you can only do this in VB, C# isn't supported. However in SQL 2005, you can definitely use C#.

|||Thanks,

so that's why i couldn't find anything in C# :/

Going to try and find a different approach then.

Custom conflict resolver example code in C#

Hi!

I'm trying to create a custom conflict resolver for SQL Server 2000 in

C# but i can't seem to find any source code examples of a *.dll where i

can actually see what needs to be done.

Any help is welcome.

Thanks

In SQL 2000, you can only do this in VB, C# isn't supported. However in SQL 2005, you can definitely use C#.

|||Thanks,

so that's why i couldn't find anything in C# :/

Going to try and find a different approach then.

Custom Assemblies: Using same data source as in RS

Hi all,
most of all works like charm, but I'm thinking how to define database
data source for my custom assembly. The best option would be use same
datasource as is defined in RS, it should be in some way shared ?
Now I have defined it like:
connSql = New
SqlConnection("server=localhost;uid=foo;pwd=bar;database=MYDB")
It would be nice, if I change Data Source in RS, it will change also
in my assembly, but I'm not sure if it is possible. Also, next option
which comes to my mind is read CFG from external file...
Thanks for any idea
/branoBrano,
Are you implementing IDbConnection, IDbConnectionExtension? If you
are, you'll get all the datasource connection information, including
the User/PW defined in the report datasource.
I then have a constructor on my Command implementation that looks like
this:
public DsCommand(string aCmdText, DsConnectionWrapper aConnection)
{
mCommandText = aCmdText;
mConnection = aConnection;
}
I grab any pertinent Connection information I need down the road.
Andy Potter|||Hello Potter,
Wednesday, January 25, 2006, 2:30:37 PM, you wrote:
> Are you implementing IDbConnection, IDbConnectionExtension? If you
> are, you'll get all the datasource connection information, including
> the User/PW defined in the report datasource.
Thats good, I exatly need to do this. I dont know what is
IDbConnection.
> I then have a constructor on my Command implementation that looks like
> this:
> public DsCommand(string aCmdText, DsConnectionWrapper aConnection)
> {
> mCommandText = aCmdText;
> mConnection = aConnection;
> }
> I grab any pertinent Connection information I need down the road.
should you please send me some code with examples, or where I should
read about this ? I am really new to VB .NET (you are using C# as I
see, but thats nearly same now) and need more help.
Really thanks, good news is it is possible :)|||Look at Teo Lacehv's sample (do a google search). He implements the
IDbConnection and should give you a better idea of all the interfaces
you need to implement.
Andy Potter|||Hello Potter,
> Look at Teo Lacehv's sample (do a google search). He implements the
> IDbConnection and should give you a better idea of all the interfaces
> you need to implement.
Thanks, sure I will.