January 2, 2013 at 2:56 pm
Hello Everyone
I am no longer seeing a SSIS 2008 Category. So I am posting my question here. If you happen to know of the correct location, please let me know.
Ok, now to my question:
I created a SSIS package, lets say a very simple package that does nothing but pump data into the tables from a text file.
I execute the package from a SQL job, the first step read the text file. I need to wait, or pause, and retry a little bit later of the text file does not exist. How can I code this to wait a certain amount of time if the text file does not exist. Not to fail the package, but to wait 10 minutes, try again, if there is no file, wait 10 minutes and try again. If after say, the 5 time of trying, fail the package.
Does that make sense? I do not know how to write the process to wait for a certain number of seconds, for a certain number of attempts.
Thank You in Advance
Andrew SQLDBA
January 2, 2013 at 3:47 pm
Hi Andrew,
This link probably answer your query "http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91665/"
Hope this helps.
Regards,
Kamal
January 2, 2013 at 3:52 pm
I personally use a filewatching script task as step one of the job, and then the main 'package' in step 2. A few reasons. First, not all 'missing' files need a failure notification to the DBA, just an email to poke whoever needs to care that it's missing, and I can control the actual error results from different steps much easier. Secondly, my packages can get complex enough that I don't want a filewatcher task skewing my step results when I'm reviewing optimization possibilities.
The task itself is pretty simple. A script task that goes out via the filesystem object and looks for your file pattern. If it finds it, groovy, succeed and exit, which skips the job to step 3, the real package. If it fails, drop it into a sleep command for x time and increment an internal counter. Counter gets passed/time exceeded, exit with failure and move to step 2 on failure that sends my emails, which then exits the job successfully. I personally usually use 'exit times' via configurations so that I'm directly controlling its expectations so that I can adjust a time without requiring a redeployment via change control and in case our windows change when heavy loads are allowed to be ran.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 2, 2013 at 3:57 pm
kamal_ece (1/2/2013)
Hi Andrew,This link probably answer your query "http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91665/"
Hope this helps.
Regards,
Kamal
that's... wow. Intricate and overkill.
In VB:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim result As String
Dim TimeToFail As String
TimeToFail = CStr(Dts.Variables("TimeToFailRepeatedChecks").Value)
Do While Format(Now(), "HH:mm:ss") < TimeToFail
result = FileSystem.Dir(CStr(Dts.Variables("FlatFilePathName").Value) & "filepattern_*.*")
If result = "" Then
System.Threading.Thread.Sleep(20000)
Else
Dts.TaskResult = Dts.Results.Success
Exit Sub
End If
Loop
' If file never found.
Dts.TaskResult = Dts.Results.Failure
End Sub
End Class
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 3, 2013 at 3:10 pm
Evil Kraig F (1/2/2013)
kamal_ece (1/2/2013)
Hi Andrew,This link probably answer your query "http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91665/"
Hope this helps.
Regards,
Kamal
that's... wow. Intricate and overkill.
In VB:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim result As String
Dim TimeToFail As String
TimeToFail = CStr(Dts.Variables("TimeToFailRepeatedChecks").Value)
Do While Format(Now(), "HH:mm:ss") < TimeToFail
result = FileSystem.Dir(CStr(Dts.Variables("FlatFilePathName").Value) & "filepattern_*.*")
If result = "" Then
System.Threading.Thread.Sleep(20000)
Else
Dts.TaskResult = Dts.Results.Success
Exit Sub
End If
Loop
' If file never found.
Dts.TaskResult = Dts.Results.Failure
End Sub
End Class
Be careful implementing code like this. You are not checking for exclusive access to the file before allowing the SSIS package to proceed in processing it. If someone were copying a file to the drop-directory SSIS could begin processing it before it releases its file handle. This is becomes more problematic the larger the file gets but happens with very small files as well. The code from the article handles this case. I know because I wrote it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 3, 2013 at 3:13 pm
kamal_ece (1/2/2013)
Hi Andrew,This link probably answer your query "http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91665/"
Hope this helps.
Regards,
Kamal
The article focuses on SSIS 2012 and walks you through the entire process of creating the package step-by-step so it may seem a little lengthy. The Script Task code is constructed such that you do not need any .NET programming experience to use it. Just setup the variables listed in the article and copy and paste the C# code into a Script Task, initialize the variable values for your environment and and off you go. It may seem like overkill but it provides protection by waiting for exclusive access to the file, as well other features like "timeout as warning", "timeout as error" and watching for a file in the root as well as all its sub-directories. If you have any questions about the article post them to the article thread.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 3, 2013 at 3:48 pm
opc.three (1/3/2013)
Be careful implementing code like this. You are not checking for exclusive access to the file before allowing the SSIS package to proceed in processing it. If someone were copying a file to the drop-directory SSIS could begin processing it before it releases its file handle. This is becomes more problematic the larger the file gets but happens with very small files as well. The code from the article handles this case. I know because I wrote it.
A very fair point. The code in question literally only detects for existence, as it's separate from anything else. Normal file access as a datasource has this problem, however, unless you explictly check for exclusive ownership anyway, which I don't believe I've ever done even when the package is to assume the file exists. All this code was meant to do was determine if the file landed, then proceed as normal. I use standard SSIS flat file (or excel, etc...) datasources as the rest of the checks.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 4, 2013 at 7:03 am
Evil Kraig F (1/3/2013)
opc.three (1/3/2013)
Be careful implementing code like this. You are not checking for exclusive access to the file before allowing the SSIS package to proceed in processing it. If someone were copying a file to the drop-directory SSIS could begin processing it before it releases its file handle. This is becomes more problematic the larger the file gets but happens with very small files as well. The code from the article handles this case. I know because I wrote it.A very fair point. The code in question literally only detects for existence, as it's separate from anything else. Normal file access as a datasource has this problem, however, unless you explictly check for exclusive ownership anyway, which I don't believe I've ever done even when the package is to assume the file exists.
Agreed. I was not saying the code you showed doesn't have a place, only that it is more prone to having a problem than code that waits for exclusive access. I too do not typically check for exclusive access if constructing a package that is meant to start at a specific time and exit if the file is not there. However what was being presented in the VB code is form of a file system watcher, i.e. the package does not exit if the file is not there, it waits and checks again later, and once a package is setup to do that a lot more concerns enter into the conversation.
All this code was meant to do was determine if the file landed, then proceed as normal. I use standard SSIS flat file (or excel, etc...) datasources as the rest of the checks.
What did you mean by the rest of the checks?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 4, 2013 at 10:21 am
opc.three (1/4/2013)
Evil Kraig F (1/3/2013)
Agreed. I was not saying the code you showed doesn't have a place, only that it is more prone to having a problem than code that waits for exclusive access. I too do not typically check for exclusive access if constructing a package that is meant to start at a specific time and exit if the file is not there. However what was being presented in the VB code is form of a file system watcher, i.e. the package does not exit if the file is not there, it waits and checks again later, and once a package is setup to do that a lot more concerns enter into the conversation.
Orlando, perhaps I'm just confused here, and that'll lead into my answer to your other question; what other concerns are there? SSIS will not attempt to use a file that it cannot get exclusive access to. When a file is being delivered/still being wrtten, it'll be opened exclusively by that software. I've gotten errors on that before.
As to the 'other checks', I was thinking formatting, exclusivity, not empty, things like that. Nothing extravagant.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 4, 2013 at 11:08 am
Evil Kraig F (1/4/2013)
opc.three (1/4/2013)
Evil Kraig F (1/3/2013)
Agreed. I was not saying the code you showed doesn't have a place, only that it is more prone to having a problem than code that waits for exclusive access. I too do not typically check for exclusive access if constructing a package that is meant to start at a specific time and exit if the file is not there. However what was being presented in the VB code is form of a file system watcher, i.e. the package does not exit if the file is not there, it waits and checks again later, and once a package is setup to do that a lot more concerns enter into the conversation.Orlando, perhaps I'm just confused here, and that'll lead into my answer to your other question; what other concerns are there? SSIS will not attempt to use a file that it cannot get exclusive access to. When a file is being delivered/still being wrtten, it'll be opened exclusively by that software. I've gotten errors on that before.
The way I am viewing it is that SSIS will try to use a file regardless of whether it can gain exclusive access to it or not. That is why it is important (moreso and especially with the FileSystemWatcher since it triggers an event as soon as the file hits disk for the first time) to wait for exclusive access, to avoid those types of errors. If we simply let the Script Task end as soon as the file arrives a subsequent File System Task to sweep (move) that file into a working directory would throw an error.
In the case of the loop as you showed it, the exposure is less than when using a FileSystemWatcher but it is present nonetheless.
As to the 'other checks', I was thinking formatting, exclusivity, not empty, things like that. Nothing extravagant.
OK. I was thinking there was something built into the File System Task or other Tasks you knew about that would allow you to configure it to wait for exclusive access.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 4, 2013 at 11:50 am
opc.three (1/4/2013)
The way I am viewing it is that SSIS will try to use a file regardless of whether it can gain exclusive access to it or not. That is why it is important (moreso and especially with the FileSystemWatcher since it triggers an event as soon as the file hits disk for the first time) to wait for exclusive access, to avoid those types of errors. If we simply let the Script Task end as soon as the file arrives a subsequent File System Task to sweep (move) that file into a working directory would throw an error.
Ah hah! That's our primary difference. I don't bother moving files before I use them, I archive after in case for restarting of processes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 4, 2013 at 12:00 pm
Evil Kraig F (1/4/2013)
opc.three (1/4/2013)
The way I am viewing it is that SSIS will try to use a file regardless of whether it can gain exclusive access to it or not. That is why it is important (moreso and especially with the FileSystemWatcher since it triggers an event as soon as the file hits disk for the first time) to wait for exclusive access, to avoid those types of errors. If we simply let the Script Task end as soon as the file arrives a subsequent File System Task to sweep (move) that file into a working directory would throw an error.Ah hah! That's our primary difference. I don't bother moving files before I use them, I archive after in case for restarting of processes.
I sweep from the get-go because I am working from a wide open (to users) drop-directory and I do not want anyone messing with a file after they have posted it and I have already started processing it, so no one gets the wrong idea. In a restart scenario, depending on the process, I could either look for files in the processing directory or require that the file be manually moved back to the drop directory, depending on how much manual intervention I want to provoke after a package failure.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply