Custom Transformation Component tutorial

Hi all,

Is there any tutorial to learn how custom transformation component works? maybe a blog, pdf or something...

Specifically, i need to learn how to generate an output column composed from 3 input columns. The problem is i dont know how to set the column value... anyone have some sample code?

Thanks!

I don't think there is any one source. Books Online has some very good topics on this. Make sure you have the latest version Feb 2007, not just the one which came with the original media.

The MS downloads site has some good samples too, just search for SSIS on there. The samples cover lots of functions and build on each other, even when non functional as they stand.

There plenty of sites with articles but I'll cheat and point to some on mine as I know them -

The ReverseString Component Explained
(http://www.sqlis.com/25.aspx)

When to use ProcessInput or PrimeOutput in your Component
(http://www.sqlis.com/24.aspx)

You can also buy books.

|||

Thanks Darren,

I need this new calculated output column as well as the same 3 columns that were the input of the component.

I have been trying my self using msdn, the problem is that , either i get the new column or the three ones, not these 4 columns i need...

I'll read this articles you post.

Thank you.

|||

Sorry for my insistence...

How can i link the inputs of the "custom transformation component" to its outputs in order to use this data in next step?

It's possible to mix this kind of outputs with other outputs generated by the objects?

thanks

|||

I do not really understand the question. Link inputs, why? Do you have multiple inputs? Do you mean an input or an input column?

You can "link" the input to the output via the SynchronousInputID property of the output makes a synchronous component.

Synchronous vs asynchronous is explained in Books Online.

If you have a synchronous component (make sure you know what that mean, see BOL), then an input column is always an "output" column, but it does not appear in the columns collection of the output itself. You can change data and it is available to downstream components.

|||

Dear Friend,

See this example:

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Helped? :-(

Regards!

custom transformation and broken FK

Hi,
I need to do the following through Enterprise Manager (EM).
I need to copy the structure of all the tables along with their PK, FK
etc and need to copy the data of only select set of tables (not all of
them). I tried doing this in EM but lost the relationship between
tables in the target database.
Here is the example:
TablePK has an indexed column. TableFK has a column which is the
Foreign key to TablePK.
- I create ine DTS package1 in which I transfer both tables as objects
along with their PK & FK, and without data.
- I ceate a second package2 in which TablePK is only transferred, this
time with Data.
- At the end I notice that the relationship between TablePK and TableFK
is broken in the target database.
Does anyone have a clue how do I go about this problem. I am a newbie.
TIA.
You could use the copy objects task in DTS seeing as this is one of the NGs
from which you have asked for a reply.
You could try using SQLDMO to script all the details you require from the
tables you want as well.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||try DB Ghost http://www.dbghost.com you can copy over schema and select those
tables where you want to copy data and it takes care to propagate objects and
data in the correct order so relationships aren't broken.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"aamirghanchi@.yahoo.com" wrote:

> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||When I copy all tables as you have done, I script it using SEM...
Select your source database -> All Tasks-> Generate SQL Script... One of the
last tabs gives you the option of taking FK relationships and indexes etc.
It is NOT checked by default..
Make sure you select it and then run the script in QA to create the tables
in the new DB..
For a small number of tables I usually select INTO to move the data, For a
larger number of tables I use DTS...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
sql

Custom DataFlow Transformation not showing up in toolbox

I've created a custom data flow tranformation and it isn't showing up in the Tool Box Items to be added under the Data Flow Items tab (right click on tool box, 'Choose Items...', then clicked Data Flow Items).

I have done the following:

signed the assembly,

added to GAC,

copied the dll to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents.

It worked previously when I was just starting out, however now I cannot see it. What would cause it to not show up? Everything compiles fine. How would I determine how to fix it so that it shows up?

Apart from the component being in the GAC, there are two key steps to be taken for a custom component to be recognized by the BI Studio:

placing the binary under the folder where SSIS design time checks to enumerate pipeline components, which is at <SQL Installation Folder>\90\DTS\Pipeline Components (I'd double check this on the target machine via the reg key at the very bottom, cause it could be different on a 64bit or in a production server)