I currently have a SQL backup process that backs up my databases via the network to a backup hard drive on a separate system. I recently began getting strange issues with my backup process in which it continually writes to the backup drive until the drive fills up and then the job fails. I also noticed that when I kill the job on the host server, the backup file drops to the normal file size. The normal file size is 300 GB but it has grown to over 400GB. I looked at various logs and even performed several backup tests with success.
I am trying to figure out if this is a known SQL Server issue or an issue with the OS?
I created a package which passes some infornmations( through parameters) to its child package.
I need to do some processing in parent package based on execution status of child package.i.e.
if child fails then some operation and if child succeeds then other operation.
To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".
My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.
I am using SQL2K server. I have a scheduled job which invokes a DTS package. One of the tasks in the DTS package invokes an OS command (from a .bat file) to map a network drive to b made available (dynamically, referring to a mapped drive letter, say "Y" or "Z") in successive tasks to refer to a source or destination TEXT file(s). Basically, the DTS package must be able to refer to the TEXT files (source/destination, depending on the task I have) located somewhere in the network. [ I use the NET USE command in the .bat file to map the network drive.)
The DTS package works stand-alone from SQL Enterprise Manager. But, when the package is invoked from a scheduled job, the step containing the DTS package fails - the task which maps the network drive fails - access violation /session error... How do I handle the batch job to overcome this problem? I appreciate tips/solutions from anyone.... Thanks. K.K.Rengan
I have already created package which loads a text file to database.I wanted to execute that package based on the availability of the file using Visual Basic,Perl or VBSCRIPT whichever is easier.Please advise.Thanks
I wrote a stored procedure that calls a DTS package that imports a file into a SQL Server table, then executes some commands on that table. How can I tell that the DTS package has completed, so that I can continue with the next command? Here is my code:
-- Run the DTS package EXEC @Result = msdb..sp_start_job @job_name = 'MyJob' WAITFOR DELAY '00:00:02'
-- Call the table that the package created SELECT * FROM <imported_table> WHERE ...
I had to insert a WAITFOR statement to make this work. Any other suggestions as to how to know when to continue?
Is there a method to either: A) Kick of a DTS package directly from a stored procedure without shelling out to the DTSRun facility. B) Kick off a scheduled job containing a DTS task from a stored procedure?
I need help from you. I am working on SSIS packages for ETL purpose. The version of SQL Server i am using is SQL Server 2005.
In Brief , the working of current ETL is as follows.
In ODS database i have 2 tables i.e Table_A & Table_B which gets loaded from another 2 staging tables A & B. And using this 2 tables data will be loaded into a target table i.e Trg_A.
The ETL packages are executed by stored procedures by creating a job within the stored procedure.
The loading of the trg table is little tricky. Before that loading of Table_A is implemented in a single SSIS package. and loading of Table_B is been implemented in another SSIS package.
In the trg table there are two columns which will be getting updated as and when each table is loaded. so for the first time if i run the package which is resposible for loading Table_A, it loads values into Table_A and once done it will updates (col1) in the target table.
Once after the complete of the execution of Package1. Now i will kick off the second ssis package which loads the data into Table_B and updates the trg table's columns (col2).
Now the actual problem what i am facing is:
For loading Table_A and updating the col1 in Trg table i will be receving more than 5 excel file every month on weekly basis. I cannot even gather all the files and run using a For-Loop counter. So presently i am loading data excel file per week .
Similarly loading of table_B.
For a week if i am executing both the packages which loads the Table_A and updates the Trg(col1) and Table_B and updates Trg(col2), then i am getting a Deadlock Error and the entire ETL is getting messed up.
Now my requirement is , Eventhough the 2 packages are run in parallel , there could certain milli seconds time difference while start of the execution in Job Monitor. I need to implement a Queing Mechanism which takes care of running the packages in a sequential manner rather than in parallel. i .e i need to ensure only one SSIS package is running in Job Monitor. Only after successful execution of either one the package, then only the second package should start its execution.
If we can implement such a queing mechanism , then my problem is solvedl.
I need some suggestions on this regard in implementing the Queing mechanism in a programatic approach using SQL Server Job Related MetaData Tables. or else is there in server parameter or initialization parameters which can be set at Database level which suffice my requirement.
Any suggestions would be greatly appreciated. Looking for sincere comments on this regards.
I have a package that has 12 data pump tasks all executing in parallel.
It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.
Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.
This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.
Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.
I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.
I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
I need a user to pass an input to a package from a VB form, and then want to show progress/errors in the form.
I have been able to use Application. LoadFromSqlServer and I then set pkg.Variables.(MyVariable) to the required value before calling Execute.
Works fine so far.
I dont know how to show progress. The DTSExecResult just returns a Cryptic Success /Failure status message.
Is there a way to log the errors if any to my Windows form? I have seen samples on Console Apps(see below) but I would prefer to show it in my Windows form in a text Box or something, appending each error result to the text.
TIA
Kartik
Code Snippet
Class EventListener Inherits DefaultEvents
Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _ ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _ ByVal helpFile As String, ByVal helpContext As Integer, _ ByVal idofInterfaceWithError As String) As Boolean
I have basic knowledge of SSIS and will appreciate if some can suggest a way (with example if possible), to solve this problem.
I have an SSIS package that requires execution of another package, lets say B. This I am able to do using the 'Execute package' task. Now, what I really want to do is, I wish to check if a perticular table 'A' has rows that satisfy a given condition. In other words, I wish to execute Package 'B' onle if Table 'A' has rows. How can I do this?
I tried to execute the package,which is importing the data from the Flat file(flat file from map network drive on other computer).But Packages are created and executed on Server.While i executing that package it shows error as "Package could not be found.".
hello to every body.. well i have a problem.. well i created a package the task of which is to update thedimensions and the fact tables from the tables of source.
i created a configuration file with all the parameters that are nedded (connections managers; parameters of packages).this config file will include all the paremeters need for the execution of other packages
In that package i call an other package (execution package task). this package normally will take the parameters of the parent package isn't it??(i put the parameters of this package to the config file of the parent package).
i must also note that for the second package called there is also a package...
i must also note that i have 2 DWHs (a and b) for testing.
the problem is that when i execute the parent package to DWH b , the sacond package called do not take the parameters of the parent package. instaed it looks to its XML configuration file(in a different location of the config file of the parent package) which contains the connection parameters to DWH a and it calls strored procedures from the DWH a (instead of calling at SP from DHW b).
I have a problem in getting the info about the execution of a DTS Package.
I have a SQL-2000 Client installation on my Desktop to connect to SQL-2000 Server Database system to do all of my work. i had a DTS package stored in my SQL Server2000 Local Packages. Now i have to schedule the DTS Package from my Desktop. And the DTS package is processing some files from a shared network folder. when i used to run the DTS package manually from my desktop my desktop login have access to that shared folder so there is no problem to run the package.
suppose i scheduled the DTS to execute on daily basis at 10:00 AM. Now my doubt is wher the DTS will be executed after scheduling, which starts DTS execution and is it required my Desktop to be up daily at 10:00 AM, and how the network shared folder is going to be accessed by the DTS, means where to specify the authentication to the shared folder.
Which is the best way to execute SSIS packages? I have no problem to use dtexec command but I want it to run every night. Is this going to be done through the SQL Server (SQL Server Agent under Jobs)? Is the type going to be Operating System (CmdExec) or Transact Sql? How exactly is the command written there?
I am running into some issue that i have found any good clue on this forum... although have seen a few threads dicussions. I have a master package which invokes a dozen of child packages.
1) If I only open master package inside IDE, I am keeping getting the following error when i run the master packages inside Visual Studio 2005 IDE.
The connection type "FLATFILE" specified for connection manager "XXXXX Flat File Destination Connection Manager" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name
2) If I opened up all child packages inside IDE, the master package run fine most of time.
I just deployed my first interface and below is the error message
failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'EUROPExxx123$'.". End Error Error: 2008-05-09 11:01:13.00 Code: 0xC020801C Source: Load New Data Into TransDat... The package execution fa... The step failed.
The interface is just to copy the data from 1 database to another based on a date. I suspect that i could have missed a step during the deployment, can someone tell me if the error is caused me forgetting to configure something during the step up of the job or is it network related
I have 10 or so packages with package scope variables. I would like to create a package that not only runs all the packages but also sets the variables within the packages being executed.
A) Whats the best way to run all the packages from one (package??)?
B) Whats the best way to set the variables in the child packages, without having to change them manually everytime I run it.
Hi all, I am Hazara. I am trying to call a SSIS package from a web service. But package.execute() method is returning 'failure'. Though I am able to execute the same package from a normal .Net project and it is working fine (using the same code that i have used in web service).
I have also tried to execute it through stored procedure for which firstly I created a .dll in c#(which is perfectly working) and then I registered this .dll in sqlserver-2005 using following command.
CREATE ASSEMBLY asmPackageExecuter FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727PackageExecuter.dll' WITH PERMISSION_SET = UNSAFE GO Now on calling the method of .dll (which is responsible to execute the package) I am getting the DTSExecResult as 'success' but data is not getting transfered from one able to other as was expected from the package.
Please help me. I have searched it everywhere on net but didn't get any solution. I want to execute package only through web-service or stored-procedure
I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).
Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?
I have a pacakage wich loads data from a .csv file into my database. When I run the package from Visual Studio there is a Progress/Execution Results tab where I see information about the progress. I would like to insert some of these messages into my database. I tried to handle the OnProgress event with an Execute SQL Task and insert the Progress descriptions, but that doesn't seem to give all the information I see on the above mentioned tab (moreover, I get different numbers of events running the package from VS and running it programmatically from c#, why is that?).
What I really would like to get are the messages from the Progress tab which look like these:
[Agreement File [11773]] Information: The processing of file "D:****files*********.csv" has started.
[Agreement File [11773]] Information: The total number of data rows processed for file "D:****files*********.csv" is 201.
[Agreement File [11773]] Information: The processing of file "D:****files*********.csv" has ended.
[DTS.Pipeline] Information: "component "OLE DB Destination" (12598)" wrote 200 rows.
I am trying to design package which would copy all tables and stored procedures from one server to another. It used to be simple task in SQL 2000, there was wizard. However the new wizard does not work...
I wrote SSIS package, but when I execute I get the following error: "Can not apply value null to property Login..."
Any idea? It seems to me that this new SQl2005 is not that simple to use like 2000. 2000 was a great product. I am considering to rollback to 2000. Very frustrated with the new one...
Because of aleatory problems some of our Analysis Services Tasks fails and sometimes success with not know reasons. You run the task and it fails, the next time it fails, the next time is success, and so on aleatorily.
The solution of this is to put those tasks in a loop (ore someting similar) that only ends if all of the Analysis Services Tasks succeeds. If not, those tasks starts again. Do you know what I mean? How can I build this? I have investigated with checkpoints, with For Loops ... but still I dont find the way to do it.
Does anybody can help me? I suppose it's not so difficult, but I'm stuck in finding the solution...
I have 5 different packages out of which one needs to be run on a weekday basis. I wanted to have some kind of condition or varaiable to be set in a different Package which would determine which out of 5 packages needs to be Run.
I am thinking of setting a variable and then passing a value that would determine the particular package but then I dont know how set that in "Execute Package Task".
Hello! I'm trying to execute DTS package using dtsrun utility from MSDOS Prompt and getting error message "Cannot Open user default database '<ID>'.Using master database instead".
Hi, After executing a dts package to load data from a file to a SQL table. I need to rename the file after the load. Could any one give me a code sample so that I can add that as a task after the execution of the load in the same package
I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.
ActiveX Script 1
Option Explicit
Function Main()
Dim vDate, vCounter, vBranchCode, vPath
vDate="011207"
vCounter=DTSGlobalVariables("gVarCounter").Value
IF vCounter<=3 THEN
IF vCounter=1 THEN vBranchCode="ALB" vPath="D:PROJECTSHRISALB" ELSEIF vCounter=2 THEN vBranchCode="MOA" vPath="D:PROJECTSHRISMOA" ELSEIF vCounter=3 THEN vBranchCode="PSQ" vPath="D:PROJECTSHRISPSQ" END IF
ELSE <This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1> DTSGlobalVariables("gVarCounter").Value=1 <DTS Process should stop execution...how is this?> END IF
End Function
After excel to sql dts ActiveX Script2
Function Main()
IF gVarCounter<=3 then DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1 DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting Main = DTSTaskExecResult_Success END IF
I need help from you. I am working on SSIS packages for ETL purpose. The version of SQL Server i am using is SQL Server 2005.
In Brief , the working of current ETL is as follows.
In ODS database i have 2 tables i.e Table_A & Table_B which gets loaded from another 2 staging tables A & B. And using this 2 tables data will be loaded into a target table i.e Trg_A.
The ETL packages are executed by stored procedures by creating a job within the stored procedure.
The loading of the trg table is little tricky. Before that loading of Table_A is implemented in a single SSIS package. and loading of Table_B is been implemented in another SSIS package.
In the trg table there are two columns which will be getting updated as and when each table is loaded. so for the first time if i run the package which is resposible for loading Table_A, it loads values into Table_A and once done it will updates (col1) in the target table.
Once after the complete of the execution of Package1. Now i will kick off the second ssis package which loads the data into Table_B and updates the trg table's columns (col2).
Now the actual problem what i am facing is:
For loading Table_A and updating the col1 in Trg table i will be receving more than 5 excel file every month on weekly basis. I cannot even gather all the files and run using a For-Loop counter. So presently i am loading data excel file per week .
Similarly loading of table_B.
For a week if i am executing both the packages which loads the Table_A and updates the Trg(col1) and Table_B and updates Trg(col2), then i am getting a Deadlock Error and the entire ETL is getting messed up.
Now my requirement is , Eventhough the 2 packages are run in parallel , there could certain milli seconds time difference while start of the execution in Job Monitor. I need to implement a Queing Mechanism which takes care of running the packages in a sequential manner rather than in parallel. i .e i need to ensure only one SSIS package is running in Job Monitor. Only after successful execution of either one the package, then only the second package should start its execution.
If we can implement such a queing mechanism , then my problem is solvedl.
I need some suggestions on this regard in implementing the Queing mechanism in a programatic approach using SQL Server Job Related MetaData Tables. or else is there in server parameter or initialization parameters which can be set at Database level which suffice my requirement.
Any suggestions would be greatly appreciated. Looking for sincere comments on this regards.
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've searched everywhere for this but can't find the answerI want to run a DTS package that simply executes a SQL statement to geta count of rows in a table, if the resulting number is greater thanzero I want to execute another step in the same package, else justquit.I don't to pass global vars from one package to anotherHow can I do this?Any help would be great
