November 21, 2012 at 9:56 am
Hi All,
I need to execute 300 .sql scripts from a directory. They are named like:
10001.name1
10002.name2
...
10010.name10
10011.name11
...
and they are listed in this order in the directory.
I run ssis package with For each loop contaner and an Execute Sql task inside the container. It works fine except it does not execute the scripts in the desired order. It first executes 10001.name1 then 10010.name10, then 100101.name101 and when will reach 100199.name199 then it executes 10002.name2
Then I run a .bat file to execute the scripts - and the same order of execution.
Some of the scripts fail because they depend on some others.
Can someone tell me if this is possible to achieve and how? or to propose another approach...
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
November 21, 2012 at 10:36 am
Can someone tell me if this is possible to achieve and how? or to propose another approach...
I think the SSIS approach is fine (although maybe I would've done it in Power Shell instead), you just need to either:
> Capture the file names in the folder using a Script Task, order whichever way you like, then place it in a Variable (or a NodeList) which you can then iterate over using a ForEach Loop Container
> Use a Custom ForEach Loop Container that allows sorting, here is one: http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-sorted.html
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
November 21, 2012 at 11:30 am
Alternately, you can read the entire list of files into a table, stripping out the 10001 portion into a numerical column (with a column containing the entire filename, of course) and reading that table back, in numerical order, to a ForEach container to get the files in numerical order.
I would do it that way simple because I'm going to want to keep track of information about each file anyway: number of lines read, number of lines written, date processed, error code (if any)..etc..that sort of thing.
Erin Ramsay
November 22, 2012 at 2:52 pm
Thank you guys!
Your suggestions helped me
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 22, 2012 at 3:32 pm
And in SQL Server 2012, you can use FileTables which are very cool.
http://msdn.microsoft.com/en-us/library/ff929144.aspx
http://blogs.msdn.com/b/sqljourney/archive/2012/11/10/how-to-sql-2012-filetable-setup-and-usage.aspx
B
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply