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 is already open

Hi,

I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.

So I want to include a code that checks if file "file.xls" is open on user's machine.

If file is open, then message "file "File.xls" is already open. Generating File_1.xls"

Run the code but create the file with file name "file_1.xls"

If file doesn't exist, then run code and create file with file name "File.xls"

So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.

Here's the code I've created for generating the file:

Public Function getrmpricing()
Dim queryoption As String
Dim ans, Msg As String
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String

On Error Resume Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"

If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True

Dim xl As New Excel.Application
xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"

DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True

Dim xs As New Excel.Application
xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate
Select Case Forms!rmpricingdataform!BU

Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED

'fs.DeleteFile e_TemplateFile & "crm proposal input.XLT", True - NEWLY COMMENTED OUT
Set fs = Nothing

DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")
End If

End Function

Please advise.

Not the right forum, try this one

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=Excel&dg=&cat=en_US_d02fc761-3f6b-402c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

sql

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 if a file exists using sql

Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
ThanksTry this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'|||Originally posted by snail
Try this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'

I found another method to do this. I used the xp_fileexist command and it works well. The idea above works as well

Thanks for the input.sql

Check FTP file date

First I want to thank everyone that has given help to me and everyone else with the issues involving migrating to 2005... Thanks alot..

Now for the problem. I am looking for (an not finding anything of help) to check the date of a file on an ftp server. A file always exists but once a month the day changes. I would just download the file and check it locally but the files are several hundred megs in size so that would be inefficient.

So is there anyway to do that?

On another note, can anyone point me to a good resource for learning the scripting language that SSIS uses?

This trhread sounds similar to your problem. See if it helps you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=993578&SiteID=1

|||

This is shooting straight from the hip, so I don't know how close this is for you:

The easiest way to get a file's timestamp is through the System.IO.FileInfo class. I can easily read lots of valuable information from files on our LAN using code like this:

Dim fi As System.IO.FileInfo
Dim CreationTime As Date

fi = New System.IO.FileInfo(<Path_to_file>)
CreationTime = fi.LastWriteTime

The .NET base classes are quite insensitive to "paths", so I can use UNC paths (like \\MyServer\MyShare\MyFolder\MyFile.txt) just as easily as referencing paths on my local machine ("C:\MyFolder\MyFile.txt").

FTP sites are usually protected by passwords and things, and your credentials are passed to the FTP server for processing before you can get at the files. If your files happen to be on your company's LAN, you may be able to use the FIleInfo class to get the info you need. If it's on someone else's network, or only accessible across the Internet, you'll probably need something a bit more heavy-duty.

It looks as though the SSIS FTP task is solely intended to grab files and bring 'em down to a network. I don't see any ability to pass an FTP command (like "LS" or "CWD") to an FTP server and read back a response. Maybe in a future version of SSIS, eh?

|||

the ftp connection (which does all the work for the FtpTask) is written in c++ using winInet call.

the managed System.Net.FtpWebRequest class could be used in a script task to send make a call to get the file info from the server, you would need to grab the connection info from the ftp connection manager and make your own connection though.

I've never use the class, perhaps someone out there has?

|||

Here is some code I found at: http://www.devasp.net/net/articles/display/246.html

The Code works to access an FTP site and write the detail file information (date, size, file/directory name) into a StreamReader (unfortunately, I am not knowledgeable enough to figure out how to get the info out of the StreamReader and do something with it!) I created the message box so I could see what the StreamReader returned...

It gets the information the OP wanted (date of file) from an FTP server. Some of the other guru's can probably assist with how to use the resulting stream (or tell us a better way to store the output from the ListDirectoryDetails method, beyond my capabilities right now)...

' Had to set Option Strict Off to allow:
' fwr = FtpWebRequest.Create(ftp://xxx.xxx.x.x)
' I don' t know how to change this statement to allow it to work with Option Strict On

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
' Must have System.Net to use FtpWebRequest
Imports System.Net
' Must have System.IO to use StreamReader
Imports System.IO

Public Class ScriptMain
Public Sub Main()

Dim fwr As FtpWebRequest
fwr = FtpWebRequest.Create(ftp://xxx.xxx.x.x) ' or ftp://ftp.somewhere.com
fwr.Credentials = New NetworkCredential("userid", "password")
fwr.Method = WebRequestMethods.Ftp.ListDirectoryDetails

Dim sr As New StreamReader(fwr.GetResponse().GetResponseStream())
Dim str As String = sr.ReadLine()
While Not str Is Nothing
'Console.WriteLine(str)
MsgBox(str)
str = sr.ReadLine()
End While

sr.Close()
sr = Nothing
fwr = Nothing

Dts.TaskResult = Dts.Results.Success

End Sub
End Class

|||

Change the type or cast it to allow Option Strict On, which I highly recommend.

Dim fwr As WebRequest

fwr = FtpWebRequest.Create("ftp://xxx.xxx.x.x") ' or

Dim ftp As FtpWebRequest

ftp = CType(FtpWebRequest.Create("ftp://xxx.xxx.x.x"), FtpWebRequest)

Check for a file before executing DTS

Thanks in advance for any help offered!

I am having a text file sent from another location on a daily basis. Suppposely @. 2:00 am. I created a DTS to bring the data into a local table (5:00AM). The data I am bringing in replaces the old data. So what happens in the DTS package is all data is dropped from the table and then the new data is inserted.

I then have another job run that runs later in the day before the next incoming data arrives (7:00PM). This job deletes the old text file so that it is not appended the next time the text file is sent to me.

The problem is that for the last two days, the server sending the text file did not send the files before my local jobs run (up to 9:00AM and 7:00AM). Thus my 7:00PM job has deleted the old text file. Then later my job that calls the DTS runs (5:00AM); it then drops all data and then tries to load new data that is not there because my 7:00PM job deleted the text file and the their 2:00AM has not delevered the new text file.

My question is; what is the best way to script a job that checks to see if the text file exist before dropping the existing table?

I know that the whole process could be handled better if both the supplier of the data and me the end user could be more flexible. The problem is that the sender is going to send the data in the manner (time and method) that requires the least amount of work for them. I just need to deal with it.
Thanks,
LeeWhat type of text file ? How are you processing it now in the your dts script ? What steps are currently being used in your dts script ?|||hi

I had the same problem with a daily based data import DTS I created. Here's the way I solved it:

Open your DTS in design view. Add an ActiveX component, written in vb script:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Set MyFile = CreateObject("Scripting.FileSystemObject")

If MyFile.FileExists(local_server_path) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
___________________________________________
___________________________________________

'local_server_path' is your file path on the server.

Use the 'onSuccess' event to start the rest of your DTS.

Hope it helps.

Check File size before importing

I have a DTS procedure that runs 4 times a day. It has worked well until
now. It is reading infromation from a text file. Today the text file was
empty and DTS failed giving me this error; "The volume for a file has been
externally altered so that the opened file is no longer valid.". I believe
this is happening because the file size is 0 K bytes (This file is
constantly overwriitten). Is there a way before importing a text file to
check and verify that the file is not empty?
Use FSO (File System Object) to check the size first in an ActiveX script,
you can find an exampl on sqldts.com
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
>I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
> believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?
|||Even confirming that the file size is > 0 may not indicate reliably that the
source data transfer has completed. I have a similar situation where files
are being FTP'd from the mainframe and can take 1/2 or more to complete
(assuming they do complete). My solution was to ask the data processing
staff to download the file with an extention of .TMP and then rename it to
..DAT after the download is complete.
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
> I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?

Check File size before importing

I have a DTS procedure that runs 4 times a day. It has worked well until
now. It is reading infromation from a text file. Today the text file was
empty and DTS failed giving me this error; "The volume for a file has been
externally altered so that the opened file is no longer valid.". I believe
this is happening because the file size is 0 K bytes (This file is
constantly overwriitten). Is there a way before importing a text file to
check and verify that the file is not empty?Use FSO (File System Object) to check the size first in an ActiveX script,
you can find an exampl on sqldts.com
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
>I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
> believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?|||Even confirming that the file size is > 0 may not indicate reliably that the
source data transfer has completed. I have a similar situation where files
are being FTP'd from the mainframe and can take 1/2 or more to complete
(assuming they do complete). My solution was to ask the data processing
staff to download the file with an extention of .TMP and then rename it to
.DAT after the download is complete.
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
> I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?

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 script

SQL Server file system objects
Posted: 08-18-2004 06:23 PM
Hello World!...
Here is the scenerio..
SQL Server Database
VB FrontEnd App.
App loads XML file data into database.
Changes are often made to the XML files. When changes are made,
the files must be reloaded into the database via the application. Changes
have been missed causing loss of production and jobs being processed
incorrectly.
I am looking for a script that will check the filedate of the
xml file or the filedate tag in the xml against a 'DesignFileDate' field in
the database. If a discrepancy in the filedates is identified, I want to
load the XML data to a temporary table [ExchangeTable] into the database.
The developer will provide a process to either load the data
into the production database or delete the data. This will be processed from
the Exchange table.
Having no background in programming languages and fairly new to
SQL this is quite a challenge.
Any assistance is greatly appreciated..
Regards
Hi steve,
I will recommend the .NET framework for your application and SQL Job Agent
for your monitoring purposes.
Regards,
Jon
|||Look in "Books on Line" (BOL) that comes with SQL Server as it is a great
resource for SQL Server questions of all types.
Using T-SQL you can read values out of an XML doc and compare them to
variables in a stored proc. Look in BOL and if you have any more questions
feel free to ask.
"Steve T." wrote:

> SQL Server file system objects
> Posted: 08-18-2004 06:23 PM
> Hello World!...
> Here is the scenerio..
> SQL Server Database
> VB FrontEnd App.
> App loads XML file data into database.
> Changes are often made to the XML files. When changes are made,
> the files must be reloaded into the database via the application. Changes
> have been missed causing loss of production and jobs being processed
> incorrectly.
> I am looking for a script that will check the filedate of the
> xml file or the filedate tag in the xml against a 'DesignFileDate' field in
> the database. If a discrepancy in the filedates is identified, I want to
> load the XML data to a temporary table [ExchangeTable] into the database.
> The developer will provide a process to either load the data
> into the production database or delete the data. This will be processed from
> the Exchange table.
> Having no background in programming languages and fairly new to
> SQL this is quite a challenge.
> Any assistance is greatly appreciated..
> Regards
>
>
>

check file date script

SQL Server file system objects
Posted: 08-18-2004 06:23 PM
Hello World!...
Here is the scenerio..
SQL Server Database
VB FrontEnd App.
App loads XML file data into database.
Changes are often made to the XML files. When changes are made,
the files must be reloaded into the database via the application. Changes
have been missed causing loss of production and jobs being processed
incorrectly.
I am looking for a script that will check the filedate of the
xml file or the filedate tag in the xml against a 'DesignFileDate' field in
the database. If a discrepancy in the filedates is identified, I want to
load the XML data to a temporary table [ExchangeTable] into the database
.
The developer will provide a process to either load the data
into the production database or delete the data. This will be processed from
the Exchange table.
Having no background in programming languages and fairly new to
SQL this is quite a challenge.
Any assistance is greatly appreciated..
RegardsHi steve,
I will recommend the .NET framework for your application and SQL Job Agent
for your monitoring purposes.
Regards,
Jon|||Look in "Books on Line" (BOL) that comes with SQL Server as it is a great
resource for SQL Server questions of all types.
Using T-SQL you can read values out of an XML doc and compare them to
variables in a stored proc. Look in BOL and if you have any more questions
feel free to ask.
"Steve T." wrote:

> SQL Server file system objects
> Posted: 08-18-2004 06:23 PM
> Hello World!...
> Here is the scenerio..
> SQL Server Database
> VB FrontEnd App.
> App loads XML file data into database.
> Changes are often made to the XML files. When changes are made
,
> the files must be reloaded into the database via the application. Changes
> have been missed causing loss of production and jobs being processed
> incorrectly.
> I am looking for a script that will check the filedate of the
> xml file or the filedate tag in the xml against a 'DesignFileDate' field i
n
> the database. If a discrepancy in the filedates is identified, I want to
> load the XML data to a temporary table [ExchangeTable] into the databa
se.
> The developer will provide a process to either load the data
> into the production database or delete the data. This will be processed fr
om
> the Exchange table.
> Having no background in programming languages and fairly new t
o
> SQL this is quite a challenge.
> Any assistance is greatly appreciated..
> Regards
>
>
>

check file date script

SQL Server file system objects
Posted: 08-18-2004 06:23 PM
Hello World!...
Here is the scenerio..
SQL Server Database
VB FrontEnd App.
App loads XML file data into database.
Changes are often made to the XML files. When changes are made,
the files must be reloaded into the database via the application. Changes
have been missed causing loss of production and jobs being processed
incorrectly.
I am looking for a script that will check the filedate of the
xml file or the filedate tag in the xml against a 'DesignFileDate' field in
the database. If a discrepancy in the filedates is identified, I want to
load the XML data to a temporary table [ExchangeTable] into the database.
The developer will provide a process to either load the data
into the production database or delete the data. This will be processed from
the Exchange table.
Having no background in programming languages and fairly new to
SQL this is quite a challenge.
Any assistance is greatly appreciated..
RegardsHi steve,
I will recommend the .NET framework for your application and SQL Job Agent
for your monitoring purposes.
Regards,
Jon|||Look in "Books on Line" (BOL) that comes with SQL Server as it is a great
resource for SQL Server questions of all types.
Using T-SQL you can read values out of an XML doc and compare them to
variables in a stored proc. Look in BOL and if you have any more questions
feel free to ask.
"Steve T." wrote:
> SQL Server file system objects
> Posted: 08-18-2004 06:23 PM
> Hello World!...
> Here is the scenerio..
> SQL Server Database
> VB FrontEnd App.
> App loads XML file data into database.
> Changes are often made to the XML files. When changes are made,
> the files must be reloaded into the database via the application. Changes
> have been missed causing loss of production and jobs being processed
> incorrectly.
> I am looking for a script that will check the filedate of the
> xml file or the filedate tag in the xml against a 'DesignFileDate' field in
> the database. If a discrepancy in the filedates is identified, I want to
> load the XML data to a temporary table [ExchangeTable] into the database.
> The developer will provide a process to either load the data
> into the production database or delete the data. This will be processed from
> the Exchange table.
> Having no background in programming languages and fairly new to
> SQL this is quite a challenge.
> Any assistance is greatly appreciated..
> Regards
>
>
>

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."

check file date

SQL Server file system objects
Posted: 08-18-2004 06:23 PM
Hello World!...
Here is the scenerio..
SQL Server Database
VB FrontEnd App.
App loads XML file data into database.
Changes are often made to the XML files. When changes are made,
the files must be reloaded into the database via the application. Changes
have been missed causing loss of production and jobs being processed
incorrectly.
I am looking for a script that will check the filedate of the
xml file or the filedate tag in the xml against a 'DesignFileDate' field in
the database. If a discrepancy in the filedates is identified, I want to
load the XML data to a temporary table [ExchangeTable] into the database.
The developer will provide a process to either load the data
into the production database or delete the data. This will be processed from
the Exchange table.
Having no background in programming languages and fairly new to
SQL this is quite a challenge.
Any assistance is greatly appreciated..
Regards
I would recommend that you start with the SQL Server documentation (aka
Books OnLine) and the XML chapter there.
HTH
Michael
"Steve T." <stornari@.nvrinc.com> wrote in message
news:O$y%23aFjhEHA.712@.tk2msftngp13.phx.gbl...
> SQL Server file system objects
> Posted: 08-18-2004 06:23 PM
> Hello World!...
> Here is the scenerio..
> SQL Server Database
> VB FrontEnd App.
> App loads XML file data into database.
> Changes are often made to the XML files. When changes are made,
> the files must be reloaded into the database via the application. Changes
> have been missed causing loss of production and jobs being processed
> incorrectly.
> I am looking for a script that will check the filedate of the
> xml file or the filedate tag in the xml against a 'DesignFileDate' field
> in
> the database. If a discrepancy in the filedates is identified, I want to
> load the XML data to a temporary table [ExchangeTable] into the database.
> The developer will provide a process to either load the data
> into the production database or delete the data. This will be processed
> from
> the Exchange table.
> Having no background in programming languages and fairly new to
> SQL this is quite a challenge.
> Any assistance is greatly appreciated..
> Regards
>
>

Check Connection Exist

Lets say I have created an excel connection to an excel file.
During/Before runtime, I have deleted the file.

Is there any way for me to detect if the connection exists or not, and if the connection does not exist, skip a particular data flow process?Hi there,

You have a couple of options:
1. Use a script task to call out to System.IO.File.Exists to confirm the file exists. This will mean you'll have to get the file name from the connection string or use a variable that the connection manager's connection string uses itself.
2. Use a script task to call AcquireConnection on the connection to see if that succeeds.

Either way, put the result of the check into a variable and use that variable as a condition on the precedence constraint that goes from the script task to the data flow task.

Regards,
ash

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select * from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select * from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192 from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.

I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?

Anyone?

Check all databases for file space used

Hi There

As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.

However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.

So i want to write a job that checks the percentage space used for all databases.

My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @.DBNAME' and execute it the cursor context stays local you do not actually change database context.

So how do i loop though databases, i have found

sp_msforeachdb, however this is undocumented in BOL.

Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.

Obviously i would rather use documented methods.

So bottom line what tsql could i use to check the percentage of file space use for all databases?

Thanx

In sql server 2005 you can do this:

select*from master.dbo.sysdatabases

returns the list of databases.

for each database you can do this:

select*from [database_name].dbo.sysfiles

This gives you the list of files used by the database. It has a "size" field that represents the number of 8k pages in use- so the total size in bytes of the database would be:

select sum ( size ) * 8192from [database_name].dbo.sysfiles

The downside is that this is not documented and not recommended by microsoft as the database names may change.

I'm not sure if you can do this in sql2000 - you'd have to try.

|||

If this is sql server 2005 then you can use something like

select name , (size/max_size) [PERCENT] from sys.master_files

to get the file space used percentage for all the database files. You need to special case for some specific values of max_size like (0,-1). Look up the documentation for sys.master_files at http://msdn2.microsoft.com/en-us/library/ms186782.aspx for sql server 2005. Let me know if that works for you.Let me know if that works for you.

|||

Here you go; try this...

dbcc sqlperf(logspace)

|||

HI Guys

Ok i have tried you rsuggestions but this does not work, neither solution works because ia m interested in % file space used. In my case maxsize in both tables is -1 because they have unrestricted file growth, therefore i cannot calculate percentage used.

I want to monitor the file percentage used so that i can grow filegroups at specified times i do not want data files to auto grow during production hours. I would imagine many DBA's would want to monitor this, but how ? Like i said DBCC showfilestats works perfectly but is is undocumented? I also dont know how it works since sys.master_files and [database].dbo.sysfiles do not have an accurate maxsize(-1) so i cannot calculate it ?

Anyone?

Chart Resolution in PDF

When I export reports with charts to PDF, the server really takes a beating
and the resulting PDF file can get quite large. We would like to reduce the
resolution of the chart images that are being embedded in the PDF and/or
reduce the resolution of the PDF itself.
The help topic "Designing for PDF Output"
(ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
mentions that the resolution of the PDF can be configured in device settings,
but the PDF Device Settings help topic does not mention any setting like this
(nor does the Image Device Settings help).
Any input is appreciated.
ScottI have been tracking down an answer to this question as well.
It looks like in older versions you could set the DpiX and DpiY. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_8bld.asp
But this is not an option for SQL 2005. See
http://msdn2.microsoft.com/en-us/library/ms154682(SQL.90).aspx
If you find anything else that will prove this wrong, please post. Thanks!
"ScottB" wrote:
> When I export reports with charts to PDF, the server really takes a beating
> and the resulting PDF file can get quite large. We would like to reduce the
> resolution of the chart images that are being embedded in the PDF and/or
> reduce the resolution of the PDF itself.
> The help topic "Designing for PDF Output"
> (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
> mentions that the resolution of the PDF can be configured in device settings,
> but the PDF Device Settings help topic does not mention any setting like this
> (nor does the Image Device Settings help).
> Any input is appreciated.
> Scott|||We believe the problem is caused by defects in the 64-bit version of SQL
2005. When we moved back to 32-bit, this (and other) anomalies disappeared.
Scott
"shelley" wrote:
> I have been tracking down an answer to this question as well.
> It looks like in older versions you could set the DpiX and DpiY. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_8bld.asp
> But this is not an option for SQL 2005. See
> http://msdn2.microsoft.com/en-us/library/ms154682(SQL.90).aspx
> If you find anything else that will prove this wrong, please post. Thanks!
> "ScottB" wrote:
> > When I export reports with charts to PDF, the server really takes a beating
> > and the resulting PDF file can get quite large. We would like to reduce the
> > resolution of the chart images that are being embedded in the PDF and/or
> > reduce the resolution of the PDF itself.
> >
> > The help topic "Designing for PDF Output"
> > (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
> > mentions that the resolution of the PDF can be configured in device settings,
> > but the PDF Device Settings help topic does not mention any setting like this
> > (nor does the Image Device Settings help).
> >
> > Any input is appreciated.
> >
> > Scott