check query status

Is it possible to create an SSIS package that checks for a running Query on my SQL db?

I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?

There is nothing specific in SSIS that can give you that info; but perhaps you can put a query that gives you that inside of an execute sql task...|||

I want to use SSIS to create the package and use the SQL Task in the package, But how can I 'ping' the sql server to verify the sql query is still running or not? That's the portion I'm stuck on.

What would that query look like? Can this even be done?

|||Your question is one of a Transact-SQL nature and as such should probably be asked over in that forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Come back here if you need help implementing the resulting query inside SSIS.|||You can use sp_who or sys.sysprocesses to get lists of running processes. sys.sysprocesses would be easier to work with, if you are on 2005.

check if recordset is empy

Hello,

Using Oracle 8.1.7

In the following package how do I determine whether a value for pROW_ID is returned or not (within the package)?

Merry X-mas,
Ronan van Riet

CREATE OR REPLACE PACKAGE VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
);
END VALIDATE_USER_PKG;

/
CREATE OR REPLACE PACKAGE BODY VALIDATE_USER_PKG
AS
PROCEDURE VALIDATEUSER
( pEMAIL_ADDR in S_CONTACT.EMAIL_ADDR%TYPE,
pPWD in S_CONTACT.PWD%TYPE,
pROW_ID out S_CONTACT.ROW_ID%TYPE,
pFST_NAME out S_CONTACT.FST_NAME%TYPE,
pLAST_NAME out S_CONTACT.LAST_NAME%TYPE,
pCOMPANYNAME out S_ORG_EXT.DESC_TEXT%TYPE
)
IS

-- Purpose: Authenticate user with Oracle DB

-- MODIFICATION HISTORY
-- --- -- --------------
-- RVR 03-MAY-2003 Created

BEGIN
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;

--EXCEPTION
-- WHEN exception_name THEN
-- statements ;
END; -- VALIDATEUSER

END VALIDATE_USER_pkg;handle the execption, such as...

BEGIN
your PL/SQL statement

EXCEPTION
WHEN OTHERS THEN
pRowId := NULL;
END;

IF pRowId IS NULL THEN
trapTheError();
END IF;

Where "trapThe Error()" is the built-in that handles the exception,
or simply handle it thru the exception. Your preference.|||Hi,

Use %FOUND or %NOTFOUND or %ROWCOUNT .|||Originally posted by ronanvanriet
SELECT S_CONTACT.ROW_ID, S_CONTACT.FST_NAME, S_CONTACT.LAST_NAME, S_ORG_EXT.DESC_TEXT
INTO pROW_ID, pFST_NAME, pLAST_NAME, pCOMPANYNAME
FROM S_CONTACT, S_ORG_EXT
WHERE S_CONTACT.COMPANYID = S_ORG_EXT.ROW_ID
AND S_CONTACT.EMAIL_ADDR = pEMAIL_ADDR
AND S_CONTACT.PWD = pPWD;

you should not select directly into variables

use a cursor and
EXIT WHEN cursor_name%NOTFOUND;

Check file for being used by some other process when using Flat File Source

I am wondering how easy is to check for file locks and have our SSIS Package to wait until file has been release by the process which is using it.

Also, same question when we're writing to a Flat File (or Flat File Destination).

Thanks,I think best way is to use script task, in while loop check for file info, if it is locked continue if not then break or set some variable stating that file is unlocked now.

check file date and copy file

Hi,

I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:\source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:\dest and then upzip the files. Once this is done, sent an notification email to user@.mydomain.com.

Thanks,

Check out the FileWatcher task on SQLIS.com. It should help with identifying when the file appears. The rest of the tasks mentioned here are included with SSIS. You can use the File System task to copy files, and the Execute Process task to run a commandline utility to unzip them. The Send Mail task is used to send emails.|||

Hi,

I installed the program in the sqlis.com, but when I open the ssis business intelligent console, I can't find the filewatcher task in the toolbox. Can you tell me how to add this task in?

Thanks,

|||

There are instructions on SQLIS.com.

"The component is provided as an MSI file, however to complete the installation, you will have to add the task to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Control Flow Items tab, and then check the File Watcher Task from the list."

Changing variable scope in package templates?

Is there any way to change variable scope while using package templates?

I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.

Appreciate any help...thanks.

You can't change the scope of variables unfortunately.

Just drop it and recreate it instead.

-Jamie

|||

After some experimentation I was able to achieve this by directly editing the package file (.dtsx) and associated configuration (.dtsConfig) file. It is great that these are XML files. The basic steps follow:

Copy "template" .dtsx and .dtsConfig files and rename as desired.

In new files replace all "template" package name with new package name.

Open new package in SSIS - the variables are now in the new package scope.

Make sure you generate a new GUID for the new package within SSIS.

|||

I had created several variables in the wrong scope ..data flow scope that I needed to reference in the control flow.

Change variable scope -

1. created a dummy variable that I could Ctrl F easily in the control flow

2. under view -> Code

3. within the XML, Ctrl F the variable that was in the wrong scope and cut (ctrl X) the XML out of the dataflow scope.

4. paste the xml into the package scope directly underneath my dummy variable.

5. save XML (I saved as a different name to prevent corruption).

Changing variable scope in package templates?

Is there any way to change variable scope while using package templates?

I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.

Appreciate any help...thanks.

You can't change the scope of variables unfortunately.

Just drop it and recreate it instead.

-Jamie

|||

After some experimentation I was able to achieve this by directly editing the package file (.dtsx) and associated configuration (.dtsConfig) file. It is great that these are XML files. The basic steps follow:

Copy "template" .dtsx and .dtsConfig files and rename as desired.

In new files replace all "template" package name with new package name.

Open new package in SSIS - the variables are now in the new package scope.

Make sure you generate a new GUID for the new package within SSIS.

|||

I had created several variables in the wrong scope ..data flow scope that I needed to reference in the control flow.

Change variable scope -

1. created a dummy variable that I could Ctrl F easily in the control flow

2. under view -> Code

3. within the XML, Ctrl F the variable that was in the wrong scope and cut (ctrl X) the XML out of the dataflow scope.

4. paste the xml into the package scope directly underneath my dummy variable.

5. save XML (I saved as a different name to prevent corruption).