i am currently in the process of moving a bunch of jobs into SSIS from another ETL tool. I would like to benchmark the two products against each other by comparing how long each step of an ETL process would take.
I see no way to do this in SSIS, there is the Progress tab but it doesnt list start/time and end/time. Plus I having loops and things which I want to know how long each iteration takes.
I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.
Here's the rub:
If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).
I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.
Any help is greatly appreciated.
I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)
Firstly thank you all for you help ... i needed it .
I now have a package residing on the SQL server that i can parm in a filename and it imports the file into two tables .... works great ... till
Till i call it from a VB.net application on a FTP server. The logging im doing on the FTP server indicates the package runs ok, however there is no data in the SQL tables. In the package i was using a OLE DB connection to the SQL server tables but when running the package from the FTP server i was comming up with a manager connection error. After extensive research on the various forums i found a snippit that suggested using an ADO.Net connection, which with a few modifications have now done. As i said, i can through VS on the SQL server, run the package and everything is as it should be.
I have tried using the logging to a text file to see whats happinig when the vb application runs the package .. but it is empty apart from one line ... and still no data in the tables
Hi,Say, you have two inter-dependent packages of pA and pB. With thesuccessful execution of pA you would proceed to execute pB. And sincethey would perform some repeatitive tasks on regular basis they arescheduled as a job. But you don't want to the job to make attempt torun pB if you know pA has failed and you want to nofiy sys adm of it.And let's say pA has log option checked (so, there's a log file forit). Now, it would seem to me quite clumsy to check this potentiallyhuge file to see if a particular package (pA here) is successful beforerunning pB. Any other viable and clean solution?Yes, one could possibly combine pA and pB into one big package (pBIG),however, for the sake of a clean and clear business process this is notdesirable since pA and pB handlestotally different business processes.TIA.
I have stored procedure which creates job with 3 job steps whichruns 3 SSIS packages in a sequence. But i dont know why it is skipping the 2nd step and executing the 3rd step. I can clearly observe it in the logs.
And if comment the 3rd step and re-run the job, now it is executing the 1st and 2nd step in a sequence. I can see the log for 2nd package also. Again i uncommented the code for calling the 3rd step which loads data into Oracle tables. This time again it is skipping the 2nd step. I dont know the reason why it is happening so. It is really frustrating me a lot. IS there any precedence/ priority given while loading data into Oracle database?
Job steps First step loads data from staging database to ODS Second step loads data from ODS to DataMart Third step loads data from DataMart to Oracle Tables
Can anyone please help me out.
I have tried all options.
Here is my stored procedure. Is anything wrong in the below stored procedure.
hii got a query that takes about 14 minshere it isselect BDProduct.ProductCode,BDProduct.ProductName,SALTer ritory.TerritoryID,SALTerritory.TerritoryName,SALAccount.AccountID,S ALAccount.AccountName,sum(SalesNetFact.Qty2) as Quantity,sum(SalesNetFact.bonus) as Bonusfrom SalesNetFactinner join BDProducton BDProduct.ProductID=SalesNetFact.ProductIDinner join SALAccounton SALAccount.AccountID=SalesNetFact.AccountIDand SALAccount.BranchID=SalesNetFact.branchidinner join SALTerritoryon dbo.SALAccount.TerritoryID = dbo.SALTerritory.TerritoryIDand dbo.SALAccount.BranchID = dbo.SALTerritory.BranchIDgroup by BDProduct.ProductCode,BDProduct.ProductName,SALTerritory.TerritoryID,SALTerritory.TerritoryNa me,SALAccount.AccountID,SALAccount.AccountNamethe SalesNetFact table has BranchID,TransactionLineID as primary keythe BDProduct table has ProductID as primary keythe SALAccount table has AccountID,BranchID as primary keythe SALTerritory table has TerritoryID,BranchID as primary keyi have no other indices in any of these tablesthe execution plan shows that the sort step takes 96% cost,that is themost expensive step,it is done after all the joining steps and beforethe group by stepfor the sort step:the estimated row count is 1552242,the argumentsare:ORDER BY [BDProduct].[ProductCode]asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName]asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)any ideas about how to improve this sort step
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table SOITEMS SO Items Table SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables. 2. Insert into a 3rd table (or set of them) the differences. 3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
I have a relational table with daily sales for 364 days (52 week span) for 60 stores. I have created a Microsoft Time Series model using store as the case and the historical/actual line charts appear in the Charts viewer for each store. But only one prediction step is shown no matter how many prediction steps I select. I have tried this with an OLAP-based model and also tried a simple DMX query, all with the same result.
Scenarion: 1.- SSIS Package execute tasks on 2000 SQL Server Database 2.- Execution takes places using Business Intelligence Studio Question: 1.- How can I tracked that SQl 2000 tasks took place using a SSIS Package?
when i right click and execute the package from the Management Studio, it runs fine, everytime. When i schedule a job to execute it, it fails, everytime.
"The package execution failed. The step failed."
i have made sure that the package and job ran using credentials with more than enough access to everything. Still, with no luck. PLEASE! I am at my wits ends here, and the boss is getting antsey...
I want to convert .rdl to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.
Hello Anybody ! I want to get the execution time of a query, I mean I will run the one sql statement like this " SELECT * FROM tblname WHERE field1 = '009' and then I want to get from my program execution time of this query. I think I just keep the sys time before run it and compare with sys time when finished it. But I don't like this one, So, can I get the execution time from sql server by running their sys s-procedure or something like. Thanks.
Is it possible that a stored procedure runs slower when called by an application,and runs faster when executed as 'exec xxxxx' on query analyzer? It's actually happening to us.Any clue?? thanks. Di.
i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).
the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB
the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.
i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.
i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.
When I manually run the ssis package i.e. by clicking the run button) it takes about a second to complete. This package is scheduled to run as a job every two minutes. In the history window of jobs in sql server 2005, I see that each time the job takes about 31 seconds. Do you know why it takes 31 seconds where it should take about 1 second to complete? Thanks
I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.
I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.
I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.
What I have so far is: 1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however. 2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005) 3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)
Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)
The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.
What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.
But I'll take pretty much anything that fixes the specific db connectivity problem I've described.
I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too. Hope to get ur response soon.
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'Fn_Get_Consensus_Curve_41_Data') AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN')))) DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data
GO
*/ declare @p_ENTITYID INT declare @p_CUSTOMERID INT
Declare @p_Login_Type int Declare @p_Result_Status int set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))
If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='') Set @p_Result_Status = 1 Else if @p_Login_Type > 1 Set @p_Result_Status = 2 Else Set @p_Result_Status = 0
If @p_Result_Status > 0 -- if user is valid and given enough parameters than Begin If @p_Result_Status = 1 -- if User is trader and gives customer id Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And CustomerID = @p_CustId And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID )) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End delete from @Temp_Curve_Submission_Data
----------------------- -----------------------
Open Cur_Fetch_Curve_Cust_Data fetch next from Cur_Fetch_Curve_Cust_Data into @p_CUSTOMERID WHILE @@FETCH_STATUS = 0 BEGIN
IF @@FETCH_STATUS <> 0 break BEGIN ----------------------- ----------------------- Open Cur_Fetch_Curve_Entity_Data fetch next from Cur_Fetch_Curve_Entity_Data into @p_ENTITYID WHILE @@FETCH_STATUS = 0 BEGIN
I have a query that is taking 30-40sec to execute in a SQL Server 2005 Standard Edition database. However, when I use that same query to create a named view, and then try to open the view, I get the following error (eventually) after I attempt to open the view:
Executed SQL Statement: select ....
Error Source: .net sql data provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation, or the server is not responding.
Is there a server or set parameter that I can adjust that will allow my view to complete execution?
