3

I am currently exporting data from a database into XML files. This works very well. Depending on the amount of data, an XML file consists of only one dataset or hundreds. This process is triggered daily. Now the datasets should have a property like "FileNo" which should be zero at the beginning and then always increased by the value one. Now I have seen that there are foreach-containers and for-containers in SSIS. Unfortunately I am afraid that the use of such a container gives all datasets the same "FileNo" per day and so they are not all are raised correctly. Besides, I do not know if it is possible in SSIS to cache the last value of the variable "FileNo" somewhere, so that the next higher value of "FileNo" can be used on the next day and not to start again with zero...

Do you have an idea how to count such a variable and store it somewhere, to call it on the next day again? So in the end it should look like this:

Thank you for your help.

Hadi
  • 36,233
  • 13
  • 65
  • 124
malue
  • 59
  • 2
  • 10
  • If you can grab the current FileNo in SSIS (sounds like you are that is how you are iterating through it and have it in a variable). Once in the variable at the end of your SSIS package you can save that FileNo off somewhere. To a database, to a flat file/config file. Then when you find it set the FileNo variable in SSIS pre running other files to the one you looked up. Then when at the start of the SSIS look at that database or flat file first (if it doesnt exist just start out at 0) – Brad Feb 25 '19 at 13:23
  • I'd create a logging table and there you can track the execution number (and possibly other useful data like a timestamp and/or a success flag). Then you can insert a new row for each execution and track them. – Jacob H Feb 25 '19 at 14:44
  • What is the fileNo being used for? I doesn't seem to have any value, maybe even confusing the end user of the XML. It seems like a key but it is some random number that doesn't tie back to anything. – KeithL Feb 25 '19 at 14:44

2 Answers2

0

A C# Script Task can be used to retrieve the last FileNo value. Once this is done an SSIS variable can be updated to hold this, and you can store it by either logging it to a tables or another method. Another option is to store in within a table directly in the Script Task. Both of these options are outlined in the following. In the script, the DateTime.Now property is also stored as a timestamp. The example below covers obtaining the last FileNo from both an XML element and attribute using the LastOrDefault method of LINQ. This method returns a default if no elements are found, whereas the Last method will throw an error in such case. The XML path used is of course an example and will need to be changed to your path. 

using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;


string xmlFile = Dts.Variables["User::XMLFilePath"].Value.ToString();  

//load xml document       
XDocument xmlDoc = XDocument.Load(xmlFile);

//get last FileNo value
string lastFileNoElement = xmlDoc.Elements("Root").Elements("Element1").
                    Elements("Element2").Elements("FileNo").LastOrDefault().Value.ToString();
string lastFileNoAttribute = xmlDoc.Elements("Root").Elements("Element1")
                    .Elements("Element2").Attributes("FileNo").LastOrDefault().Value.ToString();

//update ssis variable (element)
Dts.Variables["User::FileNoElement"].Value = lastFileNoElement;
//update ssis variable (attribute)
Dts.Variables["User::FileNoAttribute"].Value = lastFileNoAttribute;

//create connection
string connStr = @"Data Source=YourServer;Initial Catalog=Database;Integrated Security=true";
string cmd = "INSERT INTO Schema.LoggingTable (FileNoAttribute, FileNoElement, ExecutionTime) values(@atName, @eleName, @execTime)";
using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlCommand sql = new SqlCommand(cmd, conn);
    //attribute parameter
    SqlParameter aParam = new SqlParameter("@atName", SqlDbType.VarChar);
    aParam.Size = 50;
    aParam.Direction = ParameterDirection.Input;
    aParam.Value = lastFileNoAttribute;
    //element parameter
    SqlParameter eParam = new SqlParameter("@eleName", SqlDbType.VarChar);
    eParam.Size = 50;
    eParam.Direction = ParameterDirection.Input;
    eParam.Value = lastFileNoElement;

    //timestamp
    SqlParameter dateParam = new SqlParameter("@execTime", SqlDbType.DateTime);
    dateParam.Direction = ParameterDirection.Input;
    dateParam.Value = DateTime.Now;

    sql.Parameters.Add(aParam);
    sql.Parameters.Add(eParam);
    sql.Parameters.Add(dateParam);

    conn.Open();
    //insert data
    sql.ExecuteNonQuery();
}
userfl89
  • 4,610
  • 1
  • 9
  • 17
0

I will try to split your question into two parts:

  1. Incrementing the variable FileNo on each loop
  2. Storing the latest file values

Incrementing the variable FileNo on each loop

To increment the value on each loop the simplest way is to use an Expression Task inside the foreach loop container, with a similar expression:

@[User::FileNo] = @[User::FileNo] + 1

For more information you can refer to the following answer:

Storing the latest file values

Unfortunately, there is no option to store this value inside the SSIS package, but you can store it inside a database or text file and retrieve it at the beginning of the package execution and to update this value at the end.

If you decide to store the value inside a database, then you need to use an Execute SQL Task. If you decide to work with a flat file then you must write a script using a script task to retrieve the value at the beginning and store it at the end.

I will provide some links that may helps to implement this process:

Reading value from a database

Read and storing value from flat file using c#

Hadi
  • 36,233
  • 13
  • 65
  • 124