Would anyone be aware of anyplace I could find some good information on creating DTS custom tasks? I've come across a couple of articles from SQL Server Magazine, but nothing too substantive...
Better yet, if anyone has any success (great or small), I'd like to hear from you and hear some of the things you did, what your custom task does, difficulty.
I'm just trying to get an idea of how much work I have ahead of me....
Thank You...
I have developed a custom task where I have a lot of properties on. Then I realized that I was missing some properties and added them to my task.
The problem is that my previous version of the task does not "upgrade" properly resulting in me needing to type in all the property values once again. I have severel task that need to upgrade but I can't figure out how to use the functions: Update, CanUpdate and Version
Any suggestion - maybe a working example?
Help much appreciated!
Hi everyone!
How do I create my own descriptions as shown in the buttom of the Properties pane in Visual Studio 2005 when I create a Custom Task?
Thanks in advance!
Hello,
I had an idea that it would be nice to be able to extend the functionality of an existing SSIS component or task by inheriting from it. Perhaps in a similar way to how it is possible to extend user controls in .Net.
e.g. The rowcount is a useful component but it would be good to create a new component that inherits from it and then override the PostExecute() method to fire an Information event containing the number of rows. That's a very simple example but I think you get the gist.
Does anyone think that would be useful? Or even made possible?
I was also wondering whether it would be possible for the SSI team to make all the icons used within SSIS available as .ico files so that we could modify them for tasks/components that might do similar things.
Thanks
-Jamie
[Microsoft follow-up]
All aspects of my custom task work well except for the icon!
I have included the .ico in the assembly as an "Embedded Resource" and the icon has both 16X16 and 32X32 images. I have edited the IconResource property of the DtsTask attribute as follows:
IconResource="MyNamespace.MyDLLName.MyIconName.ico",
Where the output assembly is called "MyNamespace.MyDLLName.dll".
Has anybody got this working before? The Icon just never shows (I get the default blue square task icon).
Thanks in advance.
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
Thanks!
Matt
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
Whenever I make a breaking change to a custom SSIS component/tasks and update the Assembly Version, it seems to break my packages beyond repair, telling me it can't load the task:
Error loading Package1.dtsx: Error loading a task. The contact information for the task is "". This happens when loading a task fails.
All of the properties of said task now show:
Could not get value for property 'c-155-designer-name'. Specified cast is not valid.
Typically, a "breaking" change when it comes to code just means that you need to update your components to adhere to the new contract of the updated signatures. But with SSIS, it seems the only solution to this is to completely remove the component, and re-add the new version, and re-enter all of the property values/expressions. If I have a package containing 10 instances of a task that only had one property removed, for example, this results in a very time-consuming process of fixing my package.
So my questions:
1) Am I doing something wrong in my versioning/deployment that is causing my packages to unnecessarily break?
2) If this is just "by design" and the way it's meant to behave, what is the best practice for making breaking changes to custom tasks/components used by many packages? Should I just never change the assembly version, even when it is a breaking change (this seems to be less disastrous)?
3) As a last resort, if I'm stuck with having to fix the broken tasks, is there a better way to fix them rather than having to completely remove them, re-add them, and re-set all of their properties/expressions?
Thanks in advance.
Jerad
As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.
I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.
Which option is better?
1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months
OR
2) Sending records to a web service in a batch and get the result. I don€™t know how to do this though.
Any help or better solution is appreciated.
Hi,
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
Any help appreciated.
Thanks
Naveen
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies View RelatedI am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
are there restrictions on what a DBA can do on a developer's edition?
i'm asking coz i get really bad connection problems when changing service accounts, client aliases, port numbers, connecting to different components like ssis, reporting services, database engine, etc...
in sql2000, changing these things are a breeze, is there some guidelines when making these changes in 2005?
TIA
--------------------
keeping it simple...
Hi guys
Just want to know whether you guys do the following as a dba:
1. Setup a logging for tracking database as well as table size that containing size, indexing size. You can measure the growth.
2. Record for indexing for each database. I think this is over kill task.
3. Record database setup such as create statistic, update statistic etc.
These tasks can be automate to record every month for example and record this into Administration database for instance.
What else you guys record?
Thanks
If I'm in the Data Flow tab in VS 2005, how can I select only certain components to run to test? I tried highlighting the ones that I want to run but it's running all of them in the tab...some of the components I want to take out for testing then maybe put back in later. If I delete the tasks I don't want to run, then I end up having to recreate them
View 11 Replies View RelatedWhere to look for the web assitant created jobs on the server?I created a web page using the web assistant which is suppossed to get updated each time a value changes in a particular table,but I can't find the task which I created ,where do i look for that?I didn't use this wizard previously.thanks for any help!
Sheila.
If I have 2 scheduled tasks set for the same time (perhaps accidentally), will the
SQL Executive start 1 and queue the other one until the first is complete and
then run the 2nd task? Or will they both be started simultaneously?
Thanks!
Toni Eibner
I have been running the following production job successfully for a long time.
It now fails, and the Task History Last Error Message displays 'No Message'.
The log file ( C:MSSQLLOGMaint_TombV50.txt) shows it ran successfully,
with a Return Code 0.
SQLMAINT.EXE -D TombV50 -RebldIdx 10
-CkDB -CkAl -CkTxtAl -CkCat
-UpdSts -Rpt C:MSSQLLOGMaint_TombV50.txt
It looks like SQL Executive burps for some reason.
Any ideas, comments, suggestions?
Thanks,
sb
To all,
If I have a scheduled tasks that is owned by 'sa', how can I assign permissions to allow another user, even the database dbo, to register the SQL server and view the scheduled tasks?
Thanks in Advance,
Ed Molinari
Hello ,
I need create tasks, which will be wake up daily to backup 4 databases.
My questions are:
1. Can I create 1 task to backup all databases using TSQL command?
Ex: DUMP DATABASE test1 to test1_backup
DUMP TRANSACTIONS test1 to test1_backup with truncate_only
DUMP DATABASE test2 to test2_backup
DUMP TRANSACTIONS test2 to test2_backup with truncate_only
Etc.
If it’s possible can I just print this command to ‘Command:’ text box in the ‘New task’ window
without writing the TSQL command into a text file and execute it with the ISQL program through
CmdExec??????
2.If first doesn’t work: can I do the same job, but create for every backup own task and run the same
TSQL command for particular database. And if I can is it possible to schedule run all 4 tasks the same time or I should put time’s interval?
3. If 1. And 2. Are false. Give me your smart advice.
Thank you,
Sev
Is anybody knows how to transfer all the tasks in one server to another?
Our development database will be transferred/copied to a new production box
and that includes all tasks that we've created. We have almost a hundred
tasks defined and we don't want it create manually. If someone had done
this before, please give me a hint, i appreciate it very much!
Thanks.
vic
Hi all,
I've got a DTS job which has lots of tasks in it. I've also got quite a few flows of workflow and i've noticed that a task won't execute if it has both Failure and Success workflow pointing to it... It can have multiple failures pointing to it and it will execute but it can't have multiple successes or a combination of workflows...
Does anyone know a way to get around this or to change the 'AND' ing that seems to be on the workflow going into a task..
Thanks.
Hi,
I just volunteered to be the database administrator of our new sql server. I am normally the programmer so I have little experience with database administration. There are some basics that I need some assistance with mainly when to perform various maintanence checks. For example, how often do I need to perform some of the dbcc commands that I have read about? How often do I check for data integrity, index fragmentation, rebuilding indexes, or defrag indexes?
I have to come up with a plan by tomorrow and I don't know if I have anything solid enough since I don't exactly know when to perform some of these tasks. :confused:
Thanks so much for your advice.
Can someone let me know any 10 things that are usally automated using the SQL Server Agent,
I mean besides backup what all can i automate
Thanks
could some one help in giveing solution for this tasks..
[CODE]
Task 1 : Get the first day and last day of the week
Ex: input : '04/12/2006'
Required ouput :
start_date End_date
04/09/200604/15/2006
Note :1. start_date should be sunday
2. end_date should be saturday
Task 2 -- Find the product sales week wise
Find the product sales week wise
Output Required:
gasstationID productClass week1 week2 week3 week4
-------------------- ------------ ----------- ----------- ----------- -----------
111 DSL 1 0 0 0
111 GAS 2 0 1 1
222 DSL 1 0 1 1
222 GAS 1 0 0 0
create table trial_sales(
gasstationIDbigint,
salesDatesmalldatetime,
gallonsbigint,
productClass varchar(4))
insert into trial_sales values(111,'11/01/2005',10000,'GAS')
insert into trial_sales values(111,'11/02/2005',1000,'GAS')
insert into trial_sales values(111,'11/02/2005',20000,'DSL')
insert into trial_sales values(222,'11/01/2005',10000,'GAS')
insert into trial_sales values(222,'11/02/2005',10000,'DSL')
insert into trial_sales values(111,'11/15/2005',1000,'GAS')
insert into trial_sales values(222,'11/15/2005',10000,'DSL')
insert into trial_sales values(111,'11/25/2005',1000,'GAS')
insert into trial_sales values(222,'11/25/2005',10000,'DSL')
Task 3 Display unique values
Display unique values in
create table t (colA varchar(25))
insert into t values('apple')
insert into t values('bat')
insert into t values('car')
ouput required:
a
b
c
e
l
p
r
t
Task : 4
I have a table in the database I am working with with the following columns:
(I have simplified this down for the sake of clarity)
int p1
int p2
int p3
int s1
date d1
string s1
There is no primary key and the table more resembles a log than anything.
e.g.
p1 p2 p3 s1 d1 s1
1 12 14 100 23May comment 2
1 12 14 800 22May comment 1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
1 12 15 800 21May comment 1
2 12 15 100 23May comment 1
What I want to do is exttract the rows from the table where the combination
of p1 p2 and p3 is distinct/unique and the date is the most recent one for
all combinations of p1, p2, p3. So from the table above I would end up with
the resultset:
p1 p2 p3 s1 d1 s1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
2 12 15 100 23May comment 1
Task 5
say i have the following data...
uId(int PK), productcode(int), quantity(int)
1,12,5
2,12,3
3,12,5
4,11,9
....and i have an order for productcode 12 with quantity of 6 products.
i need to select only enough rows to reach a quantity of 6.
in this case, i would want the first two rows which would give me a sum of
8.
how can i do this without looping through each record, getting the quantity
and exiting the loop when i have a sum >= 6?
Task 6
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
IDPNamePPrice
-------------------------------------
1A30
2B20
TABLE 2: History
IDLdateAmount
-------------------------------------
12005/8/750
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
LdatePNameAmount
------------------------------------
2005/8/7A50
2005/8/7Bnull
and when I pass the date of 2005/8/8, it will return the result as below:
LdatePNameAmount
------------------------------------
2005/8/8Anull
2005/8/8Bnull
Task : 7
Reverse the Name
create table sample_judge(
judge_namevarchar(50))
go
insert into sample_judge values ('J Smith')
insert into sample_judge values ('M Samuel')
insert into sample_judge values ('S Shan')
insert into sample_judge values ('K Anand')
insert into sample_judge values ('K K K K Anand')
insert into sample_judge values ('K R Shanth Anand')
insert into sample_judge values ('K R Shanth Anand Kumara Velu')
go
Expected Output :
Smith J
Samuel M
Shan S
Anand K
Anand K K K K
Shanth Anand K R
Shanth Anand Kumara Velu K R
[CODE]
thanks in adv
chakri
Hi,
I would like to run several checklists against each sql server 2005 instance.
I can get a list of the sql server 2005 machines (Which are on the network) from a sql server table.
There is a another table which holds the list of the checks that need to be carried out against each sql server 2005 machine called tblCheckList.
The list of tasks in the table is briefly as follows:
1) get the path where the .mdf file is placed.
2) get the path where the .ldf file is placed
3) get the path where .BAK or .TRN are placed
4) get the path where the sql 2005 is installed
5) get the servicepack version
6) get the authentication
7) remove sample databases i.e. northwind or pubs, etc...
8) get the recovery model on UserDB
9) get the size of each database
...
...
...
The list goes on
Thanks
Can someone let me know any 10 things that are usally automated using the SQL Server Agent,
I mean besides backup what all can i automate
Thanks
Just wonder what do you dba do in your daily tasks. I usually have meetings where I have to say what Ive been doing during the week.
=============================
http://www.sqlserverstudy.com
Is it possible to schedule tasks in SQL Express, for example a database backup, or to periodically execute a stored procedure?
View 3 Replies View Related
Hi,
I am using Windows 2000 and created around 15 scheduled tasks. My scheduled tasks supposed to run every morning. I have to set password to first scheduled tasks every morning; otherwise none of the scheduled tasks works. As soon as i put password in the scheduled tasks, other tasks work fine.
Any ideas how to save password to schedule task?
Thanks in advance..