SSIS Package for flat files
Hello All, I have a requirement like this. I will be getting everyday customer file with the name customer_2011_12_01 and I need to import that file into a customer table in sql server.Right now I have created a package and scheduled it in a sql agent and everything is working well. But if the file for today(customer_2011_12_1) is not there and if it is available tommorow the package should import that file tommorow. Since I have given my connection string as "\\\\servername\\esg_data_source\\"Customer_" + (DT_WSTR,4)(YEAR(GETDATE())) + "-" + (DT_WSTR,2)(MONTH(GETDATE())) + "-" + (DT_WSTR,2)(DAY(GETDATE())) + ".csv" The output of this is customer_2011-12-1.csv. Since I have scheduled this package it will go to this path and if file exists it will execute, if not it throws an error.But if the same file is available tommorow that package should first import customer_2011-12-1 and then it should import customer_2011-12-2. Please guide me how to achive this. Thanks
December 1st, 2011 11:19am

You can do little improvement to your package. 1--When you load your files into table, save file name and date part in log table. 2--use for each loop and loop through files, check the file name and date part against your log table. If file is not loaded already , load the file and insert entry in log table. If file is already loaded then delete the file. by doing this you will have better control on loading missing and new files.The logic you are building might not working if two days source files missing.. Thanks Aamirhttp://sqlage.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 11:44am

But for this I think I dont need a for each loop.Because everyday I need to import only one file into a table.If I miss the file today then there will be a problem.This does not occur frequently. Yes I am maintaining a log table for each file.
December 1st, 2011 12:13pm

You will have to make your process data driven. I recently implemented very similar functionality that met the requirement of processing any files that had not yet been processed regardless of the current date. The two key pieces are a Foreach Loop Container and a persisted LastSuccessfulFileDate value in a text file or database. The process would do the following. Execute a SQL Task with a query that returns all dates from the LastSuccessfulFileDate (that you are storing in a table) to Today or Today - 1 (if you want through yesterday). The results of this query need to be stored in an SSIS variable. Loop through the result set of the query in step 1 using a Foreach Loop with a Foreach ADO Enumerator that uses the variable in step 1. The Foreach loop needs to be configured to set an SSIS variable named something like LoopDate to the date in the current row of the ADO enumerator. This is done on the variable mappings tab of the Foreach Loop task. You're Connection Manager's connection string should be set using an expression that uses the new LoopDate variable. In the Foreach Loop you will execute your Data Flow or whatever it is that your doing to import the file into your table. This sounds more complicated than it is. If you know your way around the SSIS Foreach Loop task and you know how to use variables and expressions this shouldn't take more than an hour. I have additional screen shots I can send that depict the creation of the two variables, the configuration of the Execute SQL Task, as well as the configuration of the Foreach Loop. Keepin it simple
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 12:15pm

Sure that would be of great help.If you have additional screen shots please send it across. This is really a great solution.
December 1st, 2011 12:34pm

I have a .docx with all the screen shots. What's the best way to get it to you?Keepin it simple
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 12:43pm

You can email me at srik.kotte@gmail.com. Thanks
December 1st, 2011 1:57pm

I have an other issue with the same package. My package should run daily.everyday I will be having a new file. My source is a flat file.I am using the connection string as "\\\\servername\\esg_data_source\\"Customer_" + (DT_WSTR,4)(YEAR(GETDATE())) + "-" + (DT_WSTR,2)(MONTH(GETDATE())) + "-" + (DT_WSTR,2)(DAY(GETDATE())) + ".csv" when it comes today my source file is customer_2011_12_01...but the file from my connection string is customer_2011_12_1.It should be 01 for the day.I dont want to edit my package evry month from 1st to 9th of every month. Is there any way to achieve this...Right now I am manually editing the expression just by appending "0" for the days from 1st to 9th as "\\\\servername\\esg_data_source\\"Customer_" + (DT_WSTR,4)(YEAR(GETDATE())) + "-" + (DT_WSTR,2)(MONTH(GETDATE())) + "-" +"0"+ (DT_WSTR,2)(DAY(GETDATE())) + ".csv" Please suggest any solutions for this... Thanks
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 2:06pm

RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) will do the trickArthur My Blog
December 1st, 2011 2:49pm

Yup.That worked.Thanks Arthur :).
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 3:17pm

I misunderstood the problem. I thought you need to import a file for each day. So, if the system was down yesterday, then today you would need to load both yesterday's file and today's file. If all you need is to load the current day's file and what you were looking for was an expression to pad the day and month part of your file name than what I provided was a solution to a different problem. Below is the full expression for a date format of YYYY-MM-DD. RIGHT( "0000" + (DT_STR, 4 , 1252) DATEPART( "year" , GETDATE() ) , 4 ) + "-" + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "month" , GETDATE() ) , 2 ) + "-" + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "day" , GETDATE() ) , 2 ) Keepin' it clean & simple...
December 1st, 2011 3:24pm

No David.You understood correct. My Package is running perfect if I have everyday one file.It fails when it doesnt find today's file. I would have explained my issue more clearly..Here is my task we have a shared folder where we will get the customer file everyday from ftp site.So there will be a new customer file everyday.I need to take that new customer file from shared folder and import into a customer table.For this I have created a package and scheduled that package in SQL Agent. The package will execute daily and everyday it will take the new customer file and load into the table.My current issue is if file for today is not available then the package will fail. For example yesterday's file was missed and package got failed when it ran yesterday.Today I got the yesterdays file and todays file.Today My package has to import yesterdays data and todays data. Hope you understood the issue. Let me know if you got confused. Thanks
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 3:48pm

You'll have to decide if you want to process files for days after a date that is missing. For example, if you didn't have a file on 11/28 but you do have a file on 11/29, do you want to process 11/29 even though you still don't have a file for 11/28? The requirement I had when I developed the solution I referred to above, was that the files had to be processed in order. That meant that I couldn't process any files until I had the earliest missing file. So if it was 12/5 and I had all but the 11/28 file then I still wouldn't process anything because I had to wait until the 11/28 file came in. It really comes down to whether or not you want your Foreach Loop to fail if a file is missing. If you want it to fail and cease processing (the scenario where files must be processed in order) then you don't really have to do anything. If the file doesn't exist the Foreach Loop will fail and exit and the package will then cease executing as well. However, if you want to process all files that exist (and haven't yet been processed) then all you have to do is set up the package and Foreach Loop to continue looping when a file is not found. I wrote a blog post a while back on how to do this. http://sqlserverselect.blogspot.com/2010/12/ssis-foreach-loop-container-continue-on.html Hope this helps. Keepin' it clean & simple...
December 1st, 2011 4:33pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics