Sql Activity log backup: task

Showing posts with label task. Show all posts

Sunday, March 25, 2012

additional logg for DTS_DataPump Task inDTS Package

hi
any one knows how can log in a dts package othere than the defalt log .

i can work it out using DTSPackageLog.WriteStringToLog using a seperate activex task in my package .

the problem is that in my package there are more than 15 dts datapump tasks ,so to make log for each task i have to use 15 such activex task.......

so any one know how we can use DTSPackageLog.WriteStringToLog
or any thing similar in dts datapump task so that we can know the beginning and ending of individual task

abhiCheck this link (http://vyaskn.tripod.com/sql_server_dts_error_file.htm) from Vyas.

Thursday, March 22, 2012

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Tuesday, March 20, 2012

Adding the Data Flow Task Programmatically

Is it possible to add a Fuzzy Grouping Transformation in a Data flow task by Programmatically ? If it possible, what is the C# or VB .net code for that ?Yes, you can. If you see this topic in Books Online it explains how to do it, and how to get the appropriate ProgID. "Adding Data Flow Components Programmatically"

Friday, February 24, 2012

Adding expression in Data Flow Task

I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.

Thanks in advance,

Vipul

Try using the Derived Column tool.|||

Hi TGnat

I am looking for some apis which will help me to write a code. I am not sure abt Derive Column Tool as there are some of the things which are not supported in it and i am looking to enhance the functionality for my use.

Thanks

Vipul

|||

Can you give an example of what you are trying to do.

When I first started out I thought I would need a script task or a custom control for everything... Now I find that the stock components can meet most of my needs!

|||

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

|||

Vipul123 wrote:

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.
Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.
Thanks
Vipul

"Stock components" simply means the stuff that is already in the toolbox.

If you want to build something that does the same as a LOOKUP and a DERIVED COLUMN, why not just use the LOOKUP and the DERIVED COLUMN?

-Jamie

|||

Also... Your lookup can return multiple columns, one or more for the look up match, and then you can select other columns to be added to your data flow. Follow this up with a derived column that implements your logic and you may have the functionality you need.

|||

Jamie:

Thanks for the reply. I have few more queries. In the OLEDB Command, there is a tab called "Column Mapping". In here we can map the input columns to the output columns.

I am writing this code in the function :

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

ComponentMetaData.Name = "Test";

ComponentMetaData.Description = "Test";

ComponentMetaData.ContactInfo = "Test";

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "Input";

input.Description = "Input Desc";

input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSRuntimeConnection90 rtc = ComponentMetaData.RuntimeConnectionCollection.New();

rtc.Name = "Source";

rtc.Description = "Source Desc";

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "Output";

output.Description = "Output desc";

output.ExternalMetadataColumnCollection.IsUsed = true;

}

I am not able to connect(link) the input to the output ports. I am able to add new output columns in "Input and Output Properties" tab. The output ports which i am adding in this tab are not visible in "Column Mapping" tab. Please let me know if i am missing something in the component properties which will enable the linking of the input to output ports.

Thanks,

Vipul

|||

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

|||

Vipul123 wrote:

TGnat,
Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.
Thanks
Vipul

So you want to be able to reuse functionality? Currently the only way to do this is to write your own component (very difficult) or good old copy-and-paste (very easy). I know which I would choose.

Incidentally, there is functionality probably coming in Vnext that would allow you to do exactly what you want to do (i.e. build reusable collections of components) without writing code.

-Jamie

Sunday, February 19, 2012

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work Smile [:)]

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right?? Sad [:(] |||See my post 3 posts up.

Thursday, February 16, 2012

Adding custom property to custom component

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.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.

Adding custom property to custom component

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.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;