Hi,
We have 8 SSIS jobs which need to be made dependent on the arrival of a single file.
All the flows need to be triggered based on the presence of a file
Whats the way to do this
Thanks
Manish
Has anyone explored a process whereby the Transaction Log would be backup up based upon a defined threshold, ie. 75% full? All the research against news group posts and SQL2000 literature seems to point to scheduling a log backup job on a periodic basis. My workflow isn't 24 hour consistant and even adjusting the interval during the day isn't a good answer when the multiple databases on a server fill their logs at different rates.
View 8 Replies View RelatedHas anyone explored a process whereby the Transaction Log would bebackup up based upon a defined threshold, ie. 75% full? All theresearch against news group posts and SQL2000 literature seems topoint to scheduling a log backup job on a periodic basis. My workflowisn't 24 hour consistant and even adjusting the interval during theday isn't a good answer when the multiple databases on a server filltheir logs at different rates.
View 1 Replies View RelatedI need to send some reports only when there is data in the relevant tables. This is totally random and could be at anytime during a given day or any day the week. This involves multiple reports and the user is adamant that she does not have time to initiate the reports herself. Any ideas how this can be accomplished?
View 3 Replies View RelatedI am migrating DTS packages to SSIS (recreating all the logic).
I have a Data Driven Query task in DTS with
Source query - select x,y from table1 (from database db1)
Binding - table2 which contains columns which match table1 x,y (fron database db2)
Transformation - maping from source table1 x,y to Binding table2 x,y
Queries - type update update table2 set x=? where y=?
I know that there is no similar task in SSIS,can someone tell me how to replicate this in SSIS
Thanks in Adv
Hi,
Can someone share how to setup an event-driven cache refreshing option? The options provided from SSRS interface are based on a fixed schedule. If I would like to setup a dependency in determining when to refresh reports caching, based on the successful refreshing of the underlyinn SSAS cube, how can I achieve it?
Is there an utility/API provided by SSRS? Any samples scripts are very much appreciated.
Thanks,
Jet
I have a requirement of migrating DTS package which is done in Sql Server 2000 to SSIS 2012.
I started with one package having data driven query task and done with source for which i chose OLE DB Source and given the required select query in ssis 2012
I'm stuck now and i'm unable to choose the relevant tools in ssis 2012 for binding, transformation,queries and lookup tabs used in dts 2000 for this DDQT.
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!
View 12 Replies View RelatedHi,
Can anybody help me with this? I have tried this and got confused big time.
Can you point me in the right direction please?
TIA.
Barath
Hello;
I need to create a data driven query task where the destination is a view. The option to select a view is only available for the source.
I have created a view because the first attempts to create a data driven query againt the required table (db is over 4000 tables, 4 GB of data)resulted in workstation lockups.
Thank you.
Barb
Hi all,
I've a query which i need to run as Parameter Driven, where SQL asks me for an input let's say for a Date in a query. is this possible in SQL SERVER like in Oracle, if so can anybody suggest me where do i look at.
here's my quer.
SELECT * FROM TABLE
WHERE DATE > '1/01/2008'
instead of 1/01/2008 Sql Prompts me to enter my own Date and display the result.
pls.help..me and thanks in advance.
I'm trying to perform a simple DTS tassk and am having no luck. All I'm trying to do is to run a task that will delete rows from an Access table. I presume I have to create a Data Driven Query in order to do this, but I can't seem to get the formula just right.
Can someone shed some light on the subject, please? BOL says little to nothing about how to set up a simple task. Microsoft.com is also pretty lsackluster with regards on what to do.
Again, thanks for any help.
Anthony Robinson
I would like to use a Data Driven Query Task to conditionally update/insert some data from a source table into a dest. table, but I can't find any decent doc or examples on this. Is there any place that explains in gory detail how to use these DTS tasks?
Thanks,
Patrick
I use a data driven query task to perform row updates along with appending row data to a text file. In the source I use 'select top 1000 field1, ..., fieldn from table'. The package hangs when executing the data driven task. If I reduce the return count to 'select top 100...' the package execute with no problems. I can do that 10 times, with no problems. But anything more then 100 will hang the package at that task. Please help!
Thanks,
Monique
Hi guys, im using this vb script.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function
what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements)
as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:
if record exists in destination
do update statement
if record not exist
do insert startement
many thanks in advance.
Hi All,
I have some problems when changing and running report subscription in the existing data-driven Email subscription using console app.
arg[0] is the ReportID
and arg[1] is the permissionkey
I passed these parameters in command_line when I call the executable. (e.g RunSubscription.exe 1 2)
After I run my application, the query changed successfully,
however, I didn't get the email from the subscription So I checked the subscription status,
Error: Cannot create a connection to data source ''.
If I remove the get & set subscription property codes, then application runs fine but then I can't change the query on the fly. Please help!!! I will be very appreciate if someone can give me some helps or suggestion.
static void Main(string[] args)
{
ReportService.ExtensionSettings ExtensionSetting;
ReportService.DataRetrievalPlan DataRetrievalPlan;
String Description;
ReportService.ActiveState ActiveState;
String Status;
String EventType;
String MatchData;
ReportService.ParameterValueOrFieldReference[] Parameter;
ReportService.ReportingService2005 MyRS = new ReportService.ReportingService2005();
MyRS.Credentials = System.Net.CredentialCache.DefaultCredentials;
ReportService.Subscription[] subs = MyRS.ListSubscriptions("/MyReports/" + args[0], null);
if (subs != null)
{
int i=0;
while (i < subs.Length)
{
// Get Subscription Property
MyRS.GetDataDrivenSubscriptionProperties(subs.SubscriptionID, out ExtensionSetting, out DataRetrievalPlan, out Description, out ActiveState, out Status, out EventType, out MatchData, out Parameter);
//set the query based on parameters
DataRetrievalPlan.DataSet.Query.CommandText = "exec dbo.GetReportPermission @ReportID=" + arg[0]+ ", @RegionPermission=" + arg[1];
MyRS.SetDataDrivenSubscriptionProperties(subs.SubscriptionID, ExtensionSetting, DataRetrievalPlan, Description, EventType, MatchData, Parameter);
MyRS.FireEvent(sets.EventType, subs.SubscriptionID);
Console.WriteLine("Event Fired: "+ subs.SubscriptionID);
i++;
}
}
} //main
When running SSIS packages, the package execution information is logged in the SYSDTSLOG90 table which contains the following columns.
id
event
computer
operator
source
sourceid
executionid
starttime
endtime
datacode
databytes
message
After executing a package, I found that the values in the "executionid" column are the only ones that are unique. Can we use this to determine what package was run? We are trying to architect a solution that would allow us to determine as to how long a package ran, if it ran into warnings / errors etc., We can easily accomplish this by having our own table and using Global variables within packages, we could insert / update this table. Appreciate any help.
I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)
select * from A where ReceiptTS > '2014-09-30 00:00:00.000'
select * from A where ReceiptTS > (select ReferenceTS from Reference)
I am currently working to write a progress log for my SSIS packages. So far I am able write a new log entry, update this log entry using OnProgress and OnError Event Handlers. I'd like to take it one step further. Whenever the package ends whether cancelled or finished normally; I'd like to write to my logging table COMPLETED_ABNORMALLY on cancelled or COMPLETED_NORMALLY on a normal finish of the package. I'm not sure where to begin with this process. I'd like to utilize a simple method and event handler.
View 0 Replies View RelatedSSIS Checkpoint and restart event handling.
I am using checkpoints in my SSIS packages which determines the step at which the package failed the last time and restart at that step.
But i need some other task also to kickoff only during restart? There is not an OnRestart handler as OnError in SSIS.
How can i do this ?
hi!
I am using a simple Data Flow within a Squence Controller and have added a Event Handler for OnPostExecute which contains a simple insert to a table, but this is not working. My package gets successfully execute but data is not getting inserted in the table used in Event Handler. I have also tried OnError, OnPackageFailer etc... but no results. Please guide.
Hi everyone,
I'd like to alter OnInformation event in order to add more parameters (as TaskHost). Is it possible? I've tried but appears an error:
OnInformation' cannot to implement OnInformation' because of it doesn't exists on the Microsoft.SqlServer.Dts.Runtime.IDTSEvents'
Sub OnInformation(ByVal taskHost As TaskHost, ByVal [source] As DtsObject, ByVal informationCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnInformation
I suppose that I must add an overload method but how?
Thanks for you help/advices,
Enric
Hi
I'm still fairly new to some of SSIS's enhanced funtionalities, one of them being Event handlers. I have tried creating a simple package, that simply contains a SQL Execute task that basically creates a simple table. Now i have then added a OnPostExecute even handler as a test which basically runs a script task that simply shows a msgbox. I have gone to execute the package, however after the completion of the SQL Execute task, nothing happens, the even handler doesnt get fired for some reason, i switch to the even handler page and the script task has not been executed. It is like this for every event handler i have tried, even the OnError event. Could this be a problem with my installation of SSIS or have i done something wrong?
Any help would be greatly appreciated as i have done SSIS training and to create an event handler was never this hard!
Regards
Singstar
Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?
View 1 Replies View Related
I have an SSIS Package that loads data to a SQL Server table and also logs package statistics along the way with individual SQL statements. In the event of failure, I want the data loaded to the target table rolled back but I want the statistics updates saved to the database. My package consists of several Execute SQL tasks that handle the logging and a Data Flow task that loads the data to the target table along with a couple of event handlers to handle errors. I have the Transaction Option property on the Package set to Required, to Supported on the Data Flow, and to Not Supported on the Execute SQL tasks and the OnError Event Handlers.
When we run the package (and cause an error) everything runs fine until it gets to the On Error event handler for the Data Flow task. This task hangs and never finishes. If we set the Transaction Option for the Event Handler to Supported (allowing it to enlist in the parent transaction) it works but the updates that it makes roll back along with the data from the Data Flow.
Is there a problem with having Event Handlers stay out of a transaction started by the parent package?
Any help would be appreciated.
I am using the sample SSIS Event Log reports provided by Microsoft: http://www.microsoft.com/downloads/details.aspx?familyid=526e1fce-7ad5-4a54-b62c-13ffcd114a73&displaylang=en
The Event Log Summary report is showing a negative value for #Abort. Why is the aborted count negative?
I would like to create an event handler that would catch any errors that result from a sys.<table> not existing. The package is designed to run on both SQL Server 2000 and SQL Server 2005 and when I query sys.<tables> there is an error when the query is run on SQL Server 2000. I just need a good starting point...I would like something that when the server isn't 2005 it just skips the server and doesn't fail the package and doesn't get counted towards the max error count. Thanks for any help.
-Kyle
Hi,
Is it possible to do an event based scheduling of SSIS packages from SQL Server Agent? Like schedule a package to run when a file is available in a particular folder.
Does anyone have any similar experiences to share?
Regards,
Emil
Has anyone used Checkpoint files in conjunction with the OnError Event Handler ? I'm having a problem getting the OnError event to fire when the SSIS package reruns with the Checkpoint file.
The first run of the package (without a checkpoint file) works fine. The error occurs, the OnError event handler is called, the package stops and the checkpoint file is created.
When the package is restarted is goes to the correct spot (where the error occured) using the checkpoint file, then it throws an error within the For Loop container and does not call the OnError event handler. The OnError event handler is setup on the For Loop container. The ForLoop performs three loops. Each one of these loops creates an error. Not one of these errors within the three loops will trigger the OnError event handler...
Any help would be appreciated.
thanks
My project currently has task which have their own individual event handlers that get called onError (setup event messages). I also have a package level event handler that performs a generic task (sending events to the windows eventviewer) In the package level event handler there is a script task that decides on a boolean variable whether to "Success" or "Failure" to different task. When I fail one task of the main control flow, the task level event handler runs, then the package level event handler runs, and then it also runs again for some unknown reason. The second time it runs it picks up the value of a variable set in the variables window. However, I change this value at runtime to the value from a database. I can't understand why it would run the second time, and if it did run why it would have the value from the variables window and not the value that is set in memory. It's like the event handler runs with the value from memory and then runs and picks the values back out of the variables window, replacing the db values and re-runs.
Maybe the package itself is failing all together and then re-runing the package level event handler?
Any help would be greatly appreciated.
-Chris
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM
Log SQL Server (Current - 10/25/2011 10:55:00 AM)
Source spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.