April 2, 2008 at 6:27 am
I'm sure this is not all that complicated, but being a newbie to SSIS, I can't seem to get a handle on it. 🙁
Situation - need to traverse all database servers from a central server, find folders that contain DB backup files and verify that the tape system copied them by checking the Archive bit. Each server has a DBA database with configuration tables that will give me all possible folders for backup files.
Need - build a full UNC path to the each folder and use FSO to check the attributes of backup files in each folder.
What I think I need to do - outer loop of server names (this works). Next loop is inside of the server name loop and it queries the DBA database for backup locations on that server (this works). Next loop is inside the BU location loop and would need the path to the folder so it can use File Enumeration (can't figure out how to build the path for this). I would imagine that I don't need to use the 3rd loop and could do this piece in a Script Task in the 2nd loop. It would seem though, that the Foreach loop with a file enumerator should make it easier. As unArchived files are found, the information needs to be written to a text file (Script Task). After all servers are done, the text file will be used as input to an SQL Task to load into a table.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 2, 2008 at 7:15 am
What I think I need to do - outer loop of server names (this works). Next loop is inside of the server name loop and it queries the DBA database for backup locations on that server (this works). Next loop is inside the BU location loop and would need the path to the folder so it can use File Enumeration (can't figure out how to build the path for this). I would imagine that I don't need to use the 3rd loop and could do this piece in a Script Task in the 2nd loop. It would seem though, that the Foreach loop with a file enumerator should make it easier. As unArchived files are found, the information needs to be written to a text file (Script Task). After all servers are done, the text file will be used as input to an SQL Task to load into a table.
I think from the way you describe it, you could do it either way. Easiest for the non coders would be to create the 3rd loop as you say.
In order to do this what you will need to do is concatenate 2 variables. One will be user::FolderPath which will come from your 2nd loop and the next would be user::FileName which will come from your 3rd loop (you could create a 3rd variable (user::FilePath) evaluated as an expression of [FolderPath] + [FileName]).
In the final loop you will want to test the file in your script task for its Archive attributes and set an SSIS variable according to the result. Then add a precedence constraint that checks for success and expression result (say IsArchive == TRUE) and only then writes your new text file.
Hope this helps
Kindest Regards,
Frank Bazan
April 2, 2008 at 7:29 am
Frank - thank you. Will I actually need a connection to the each backup file? I played with the container using a fixed path and it loops over the files (used the watch window) but when I add a script taskt to check attributes, it seems to want a connection.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 2, 2008 at 8:18 am
No you shouldn't need a connection if you're just testing the attributes.
Here's something to get you started (You'll need the system.IO namespace)...
[font="Courier New"] Dim sFilePath As String
sFilePath = "C:\" & Dts.Variables("FileName").Value.ToString 'Change to correctly map to your path
If File.GetAttributes(sFilePath).ToString.ToLower.Contains("archive") = True Then
MsgBox(sFilePath & ": " & File.GetAttributes(sFilePath).ToString) 'Change to perform whatever it is that you want to do on finding an archive file
End If
Dts.TaskResult = Dts.Results.Success[/font]
Kindest Regards,
Frank Bazan
April 2, 2008 at 9:13 am
Most DEFINITELY appreciate your help. This is NOT the most intuitive product!. I needed to add an expression to the inner most loop (over the files) that replaced the default folder with the Directory property. I also needed to replace the colon in the drive letter with a $ for the Admin share.
"\\\\" + @[User::strServerName] +"\\" + replace(@[User::strBULoc],":","$")
I've been struggling with this for 3 days and I went to a training class! I don't do a lot of .Net programming but I 'm pretty sure I could've written this in VB.Net in a couple of hours. The whole point is to learn this product so we can help the people who have never touched it. It can be quite overwhelming.
Once again, thanks for the help 😀
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 2, 2008 at 10:24 am
You're right... I've been using SSIS pretty much since it came out, and I'm often reminded of the struggle I had at the outset. Its probably harder to get your head around if you're used to DTS (that was my background) as you have to approach it from a totally different mindset. It took me a good 6 months of trial and error before I felt confident with it, and even now there's a few areas that I don't get involved in (I've not yet had occasion to use "Term Extraction" or "Percentage Sampling").
And even when you know the work around and the pitfalls to certain tasks it doesn't stop your organisation asking you to push ahead anyways. (I've lost track of the number of times I've recommended against using excel as a datasource, yet my development gets complicated by the fact that users insist on it and at the same time want to be able to change columns at will). Consequently, I spend a LOT of time debugging packages where all the known work arounds don't prevent failure!
I think because this product gives you so many options you tend to focus on the way your organisation does things and so you get used to a particular approach too.
Makes development interesting though eh 😉
Kindest Regards,
Frank Bazan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply