March 2, 2010 at 10:09 am
A SSIS package that inserts data into a database from flat files needs to be scheduled as a job.The source file is stored on a network drive and it gets updated every 30 mins. The job/package should check if a Trigger file exists along with the data file in that folder. If yes then start processing of Data. Once completed, the job/package should transfer the data file to a different location and save it with the time stamp appended to the file name. Once the entire process is completed, the Trigger file needs to be deleted.
The purpose of the trigger file is that the process which creates the data file on the network drive works only if the folder on the network drive is empty, which means no data is being processed right now. Once the process is done inserting data to the data file, it creates a Trigger file. This will ensure that the process does not overwrite the data file before the job completes or kicks off as the process will not write to the file as long as the trigger file exists.
I created the package already, how do I incorporate the above logic now? Can it be done in package itself or is it a separate job step?
Thanks...
March 2, 2010 at 11:18 am
You can add your "conditional checks" right into your SSIS package, so the SQL Agent job is just a simple "run this package" step.
You can use a Script Task with the System.IO namespace to detect the trigger file, or use the File Properties Task available on CodePlex.
You can use the File System Task to move and rename the data file - use an expression to set the new file name.
You can use the File System Task to delete the trigger file.
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
March 2, 2010 at 7:12 pm
Todd-
That is good to know. I always use master..xp_cmdshell to archive/rename/delete files... I would prefer to do everything from the package...
March 2, 2010 at 8:05 pm
Todd, I think that sounds good.
CEWII
March 3, 2010 at 10:16 am
Todd McDermid -
Your solution is precisely what I wanted. I was able to use the File Properties Task and detect the file and used File System task with variables to move the file with timestamp appended.
Also, I ran into this script which I guess the File Properties Task uses in the background
-- using xp_fileexists
DECLARE @Path varchar(128) ,
@FileName varchar(128)
SET @Path = 'C:\'
SET @FileName = 'FILE_NAME.EXT'
DECLARE @i int
DECLARE @File varchar(1000)
SET @File = @Path + @FileName
EXEC master..xp_fileexist @File, @i out
IF @i = 1
PRINT 'file exists'
ELSE
PRINT 'file does not exists'
Didn't have much luck with the Script Task and the System.IO.Net name space. Even though I used a sample code from BOL, it didn't help.
Any benefits of using Script Task over the File Properties task or the other war around?
Thanks again..
March 3, 2010 at 11:49 am
Benefits, yes, you don't have to install anything..
What was the problem with the script task?
CEWII
March 3, 2010 at 12:10 pm
The File Properties Task doesn't use any T-SQL at all - it uses the System.IO namespace which is .Net code. And no - it's not SQLCLR either. It's important to remember that SSIS isn't tightly coupled with SQL Server. Yes - it's most often a destination for the data that we manipulate with IS, but most operations in IS work best when you try to use SQL Server (or any other persistent storage) as an endpoint only. (There are obviously exceptions to this.)
Using the File Properties Task gets you these advantages:
1. Capability to determine file existence, etc without writing code (T-SQL or .Net)
2. "Transparency" - the UI (or even just the properties exposed in the Properties window) "explain" what the Task is doing to the package designer (you) faster and easier than having to open an editor to read and understand code.
3. Reusability - if you use a Script or T-SQL to do something, you have to copy and paste the code, which opens issues to do with maintaining that code.
4. No dependence on SQL Server. You don't need to manage a connection, and you don't need to use XPs. Even if you "already have" a connection, not using it means your connection isn't dual-purposed with some other Task that you may have to decouple later when "things change".
Disadvantages to using the File Properties Task.
1. It's not Microsoft's or your code - is it trustable and reliable? This applies to any third-party extension listed at the SSIS Community Tasks and Components site. Fortunately in this case, it's open source, so you can dissect it to make sure it's behaving properly, and if a bug crops up, you have the code to repair it.
2. The Task needs to be deployed to your dev, QA, and server boxes. It's not installed as part of SQL Server - you've now got another deployment step.
IMO - custom objects (like custom tasks and components) are the way to go if you can swing it.
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply