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 numeric data type in SSIS

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.

the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.

i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero

i hope someone help me coz i need to handle it soon.

best wishes

Maylo

Here is a thought for your Scale 3 situation.

Could you try importing the data into column X as a larger datatype, say (20,5).

Then use two derived value steps to create a new column Y that is the result of conversion from (20,5) to (12,3) and then back to (20,5).

Now compare the value in column X with the value in column Y.

If X is a valid (12,3) value, then it must now have the same value as Y. Otherwise it will be different.

Simulation:

Flat file value: 123456.789

imported to X (20,5): 123456.789

Converted to Y (12,3): 123456.789

Converted back to Y (20,5): 123456.789

(X == Y) = true

Flat file value: 1234.56789

imported to X (20,5): 1234.56789

Converted to Y (12,3): 1234.567

Converted back to Y (20,5): 1234.567

(X == Y) = false

(in my VB days, we would have achieved something like this by going:

y = int(x*1000) / 1000

if x=y then msgbox "All is sweet." else msgbox "Your value has too many decimal places."

|||

thanx SOoooooo much it helps me alot

best wishes

Maylo

|||

The way I normally get round this is to use a script component.

Feed all available output columns from your flat file into the script component.

In the script component add an extra outpt column as a boolean called, for example, blnOK

In the script component's ProcessInputRow Sub add code similar to the following

If IsNumeric(Row.RowToCheck) Then

Row.blnOK = True

Else

Row.blnOK = False

End If

where RowToCheck is the particular row from the flat file you wish to check.

Then use a conditional split transformation to check the value of your new column blnOK. You can then direct your rows accordingly, ie, where blnOK is TRUE rows would go to your default table and where blnOK is FALSE rows could go to and error table.

This is a simple example but you could extend the code by creating a function to check for any data type, string format etc or even create a DLL, which you can re-use for similar situations though this may be a bit over the top.

Hope this helps

|||That RowToCheck in above should refer to the column/field to check NOT a row. Sorrysql

check numeric data type in SSIS

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.

the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.

i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero

i hope someone help me coz i need to handle it soon.

best wishes

Maylo

Here is a thought for your Scale 3 situation.

Could you try importing the data into column X as a larger datatype, say (20,5).

Then use two derived value steps to create a new column Y that is the result of conversion from (20,5) to (12,3) and then back to (20,5).

Now compare the value in column X with the value in column Y.

If X is a valid (12,3) value, then it must now have the same value as Y. Otherwise it will be different.

Simulation:

Flat file value: 123456.789

imported to X (20,5): 123456.789

Converted to Y (12,3): 123456.789

Converted back to Y (20,5): 123456.789

(X == Y) = true

Flat file value: 1234.56789

imported to X (20,5): 1234.56789

Converted to Y (12,3): 1234.567

Converted back to Y (20,5): 1234.567

(X == Y) = false

(in my VB days, we would have achieved something like this by going:

y = int(x*1000) / 1000

if x=y then msgbox "All is sweet." else msgbox "Your value has too many decimal places."

|||

thanx SOoooooo much it helps me alot

best wishes

Maylo

|||

The way I normally get round this is to use a script component.

Feed all available output columns from your flat file into the script component.

In the script component add an extra outpt column as a boolean called, for example, blnOK

In the script component's ProcessInputRow Sub add code similar to the following

If IsNumeric(Row.RowToCheck) Then

Row.blnOK = True

Else

Row.blnOK = False

End If

where RowToCheck is the particular row from the flat file you wish to check.

Then use a conditional split transformation to check the value of your new column blnOK. You can then direct your rows accordingly, ie, where blnOK is TRUE rows would go to your default table and where blnOK is FALSE rows could go to and error table.

This is a simple example but you could extend the code by creating a function to check for any data type, string format etc or even create a DLL, which you can re-use for similar situations though this may be a bit over the top.

Hope this helps

|||That RowToCheck in above should refer to the column/field to check NOT a row. Sorry

Check if file exists

In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:

a) The File Watcher task from www.sqlis.com

This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.

b) The WMI Event Task

There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \\servername\path\file.txt) which I could not get to work.

c) Script Task using the File.Exists() method

I imported the System.IO namespace, and used a File.Exists(\\servername\path\file.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.

I'm open to suggestions or ways to get options a) and b) to work for my needs.

Thanks!

Kory

Most folks use a special folder that only contains files that need to be processed. That way, you can have workflow that processes any files that exist in the folder without concern for whether it is the right type of file etc. because only the correct file types get dropped there.

Then, you can use the file watcher task effectively because you can have two processing sections in the package, a part that picks up whatever files exist in the folder and then another part that waits for new files to appear.

HTH,

Kirk Haselden
Author "SQL Server Integration Services"

|||

Your scenario descibes basically what we do. There is a single folder with all text files, but with extention of ".flg" These files do not actually contain any information (other than date/time/process) but are only for triggering other processes to began. The file names are associated with the names of tables loaded in our DW.

Unfortunately the flag file process we rely on is out of my control, and is managed by another entprise group within my company. I only have read-only access to monitor a single folder that contains about 100 files, each named corresponding to the table that has become available. I need to check this folder starting 3:00am every morning and continue to monitor it until a specific file appears. The folder is emptied at 3:00pm the next afternoon every day. If the file already exists at 3:00am, this means the table was ready earlier than 3:00am so the process can resume as normal.

So, I am still looking for a solution...

-Kory

|||Why not use the script file task to check if it existing and a file watcher to wait if not there. Some simple workflow should allow this scenario.|||

Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method

-Jamie

|||

The File Watcher Task has been updated to now check for an existing file that matches the criteria. This behaviour optional, with the default being to only look for new or changed files, as with previous versions. This can be controlled by the new FindExistingFiles property.

The current release (1.2.4.55) is fully backwardly compatible with previous versions, just uninstall the old version and then install the new version. It will add the new property on any subsequent package save, or you can force an upgrade within the Solution Explorer tool window, by right-clicking and selecting Reload with Upgrade, although this is not necessary.

File Watcher Task
(http://www.sqlis.com/default.aspx?23)

Check if file exists

In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:

a) The File Watcher task from www.sqlis.com

This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.

b) The WMI Event Task

There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \\servername\path\file.txt) which I could not get to work.

c) Script Task using the File.Exists() method

I imported the System.IO namespace, and used a File.Exists(\\servername\path\file.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.

I'm open to suggestions or ways to get options a) and b) to work for my needs.

Thanks!

Kory

Most folks use a special folder that only contains files that need to be processed. That way, you can have workflow that processes any files that exist in the folder without concern for whether it is the right type of file etc. because only the correct file types get dropped there.

Then, you can use the file watcher task effectively because you can have two processing sections in the package, a part that picks up whatever files exist in the folder and then another part that waits for new files to appear.

HTH,

Kirk Haselden
Author "SQL Server Integration Services"

|||

Your scenario descibes basically what we do. There is a single folder with all text files, but with extention of ".flg" These files do not actually contain any information (other than date/time/process) but are only for triggering other processes to began. The file names are associated with the names of tables loaded in our DW.

Unfortunately the flag file process we rely on is out of my control, and is managed by another entprise group within my company. I only have read-only access to monitor a single folder that contains about 100 files, each named corresponding to the table that has become available. I need to check this folder starting 3:00am every morning and continue to monitor it until a specific file appears. The folder is emptied at 3:00pm the next afternoon every day. If the file already exists at 3:00am, this means the table was ready earlier than 3:00am so the process can resume as normal.

So, I am still looking for a solution...

-Kory

|||Why not use the script file task to check if it existing and a file watcher to wait if not there. Some simple workflow should allow this scenario.|||

Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method

-Jamie

|||

The File Watcher Task has been updated to now check for an existing file that matches the criteria. This behaviour optional, with the default being to only look for new or changed files, as with previous versions. This can be controlled by the new FindExistingFiles property.

The current release (1.2.4.55) is fully backwardly compatible with previous versions, just uninstall the old version and then install the new version. It will add the new property on any subsequent package save, or you can force an upgrade within the Solution Explorer tool window, by right-clicking and selecting Reload with Upgrade, although this is not necessary.

File Watcher Task
(http://www.sqlis.com/default.aspx?23)

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 data before insert

I need help thinking about this problem :-)

I have an SSIS pkg that automatically downloads financial extracts from an ftp site. Once the files are downloaded, I load the extract to a table. The table is first deleted before the insert, so that each time the table has "fresh" data (whatever was in the extract for that day).

Once the extract data is in the table, I load the data into yet another table that combines data from many tables. Simple enough.

Problem is, sometimes when I download the extract, it hasn't been updated yet, so I'm downloading an OLD extract. This old data then gets loaded into the first table. That's ok, because it doesn't really hurt anything. I can always delete the table and reload it if necessary.

The problem occurs when the old data goes from this table into the OTHER table. We don't want old data in this other table!

I need a way to check that I'm not loading the same data 2 days in a row into the OTHER table.

I realize that I might be able to solve this problem without using SSIS, but the solutions I've come up with so far aren't 100% satisfactory. I can use a query to check dates and that sort of thing, but it isn't foolproof, and would create problems if I need to manually force the process though, that is, if I need to override the date logic.

Anyways, I'm wondering if there's an SSIS approach to this problem... I can't rely on timestamps on the data files either. They're not accurate.

This is has been very perplexing to me.

Thanks

You have to determine a way to programatically decide if the data is "new" or not. Without that, I don't know how we can solve anything for you.|||

Yes, I have that.

Once I load the table, I check a "date entered" field against the data's timestamp column.

That is, if I load the data on 8/15, the data timestamp should be 8/14, or the day before.

If it's 8/13, it's out of date.

But what happens if the data load on 8/14 (with a data timestamp of 8/13) didn't run... and I need to re-load the data from 8/14 on 8/15?

Now, date entered is 8/15, but the data has a data timestamp of 8/13, which in this case IS correct, but my logic will reject the data because it isn't from the day before.

That's the problem, as I see it. If I hardcode logic into my stored proc, then I can't easily override it if I need to.

I was thinking maybe, instead, I could put the date logic in a separate SQL function, then call this function from an Execute SQL task in SSIS. Then have the function return a 0 (failure) or 1 (success). If it returns a 0, then do not perform any more steps in the package. If it returns a 1, then finish running the steps in the package.

Is this possible to do? If so, that could solve the problem.

Thanks

|||My idea being, if I need to override the logic, I can simply disable the Execute SQL task that contains the function. Then run the package manually.|||You could store the last successful date loaded and then read that in at the beginning of your package and then compare it to your data coming in. If the data coming in is greater than that date, then process it.|||

I would suggest the above mention of saving the successful date. Alternately, if you have a decent audit trail, you can use the date and time of the last run and do a comparison of the files timestamp

'

' Add your code here

'

Dim vars As Variables

Dim var As Variable

Dts.VariableDispenser.LockForRead("FileName")

Dts.VariableDispenser.GetVariables(vars)

Dim path As String = vars.Item(0).Value.ToString

If File.Exists(path) = False Then

' File does not exist, no attributes to read

Else

vars = Nothing

Dts.VariableDispenser.LockForWrite("FileModifiedDate")

Dts.VariableDispenser.LockForWrite("FileCreationDate")

Dts.VariableDispenser.GetVariables(vars)

vars("FileModifiedDate").Value = File.GetLastWriteTime(path)

vars("FileCreationDate").Value = File.GetCreationTime(path)

vars.Unlock()

End If

Dts.TaskResult = Dts.Results.Success

|||

I'm not sure if I've made my problem very clear :-)

As I mentioned in a previous post, I already have a way to determine if data is out of date or not on any given day.

The problem is that if I want to be able to override this logic if I need to. Please see my previous post that clearly explains what the problem is.

Q.) I am wondering if I can call a function in an Execute SQL task to have it return a value to the package. Depending on the value returned, I want to halt execution of the package.

Thanks

|||

Never mind... I realize my logic won't work anyways.

I just need to check the timestamps in the file names.........

check Access table exists from SSIS

I am writing my first SSIS task. I have an MS Access database and I'm moving the contents of each table into a like-named SQL Server database. The task is working properly except for one step.

The source Access database may or may not have one of the tables in it. It will always have TableA, TableB, and TableC, but may or may not have TableD. I need to write my SSIS package in such a way that it will detect the presence/absence of TableD and either run or skip the import step for this table accordingly.

Can someone assist with the step that detects the table presence/absence in the MSAccess database?

thanks,

matt tag

easy.

I never work with Access/Excel, but can you run an Execute SQL task against it and populate an Integer variable with the result set?

This is the query. You'll need to map a parameter, or simply hard code the table name in place of the "?" below. If the output is greater than zero (which you can test in a precedence constraint) then the table exists, if not then the table does not exist:

SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = ?|||

Matt,

The post of the Phil is excellent, but you must do it inside the control flow. Although you have a control error for the OLE DB Source that you can redirect to do something in the case of the source does not exist...

Regards,

Pedro