I am relatively new to SSIS 2005 and I am looking to add to my current package two things. First, I guess I should explain what my package does now.
1) I have an Execute SQL Task that truncates the table I am inserting data into from a flat file. The Execute SQL Task is connected to a ForEach Loop Container that is discussed in the next #.
2) I have a ForEach Loop Container that has a Data Flow Task in it. In the Data Flow Task I have a Flat File Source, an OLE DB Destination and an ErrorOutput Desitination (Flat File Desitination). I created a variable and use the ForEach Loop Container using the variable to change the Flat File Connection ConnectionString so the package imports the file if it fits the criteria of 2*.txt (I want to change this so file that is selected meets a specific file name, which is discussed below).
3) I have three connection objects a) OLEDB to my database b) Flat File to the extract I am importing c) Flat File for error records.
The file executes fine, but I had to make some changes to my FileShare structure that I didn't want to do to allow the package to work. I also want to perform a check to make sure all of the data was loaded.
So the two things I wan't to add are the following:
1) The weekly file I import always has the monday date of the week in the format like YYYYMMDD.txt. I have a function in Sql Server that calculates the Monday date no matter what day it is of the week and I concatenate .txt to the end of the date so the date is like YYYYMMDD.txt, which is the same format as the file I want to import. I want to populate a variable in my SSIS package from this function using an Execute SQL Task (Unless there is a better object to use).
If possibile, now the variable will have the Monday date value with .txt which is always the name of the flat file I import weekly b.c the name of the flat file is always the monday date in format YYYYMMDD.txt. So I want to loop through all of the files where the Flat File Connection path is and if the variable value equals the file name I want to import that file. How can this be accomplished?
2) I want to have some kind of message that pops with a result of a simple SQL statement : Select Sum(StandardHours) as SH, Sum(OTHours) as OH From Table. The table being the table I am importing the data to.
That way I can verify that the numbers are identical to what the Cognos Extract was and I have my confirmation all data was imported. How can this be accomplished?