May 19, 2016 at 7:27 am
Hi,
I have a SSIS package and I want to check if the flat file exists and Rowcount > 0 then proceed to next task.
I need to get a Rowcount in the Control Flow Tab .
I am using two variables in the script task .
Var_DoesFileExist , DataType = Boolean, Value = False
Var_File_Path , Data Type = String, Value = C:\IntegrationBCPData\Assign.csv
In the Script Task :
ReadOnlyVariables = User::Var_File_Path
ReadWriteVariables = User::Var_DoesFileExist
--Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
namespace ST_273e8ad9bf8949549a839652be083a6b
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
// TODO: Add your code here
string filepath;
filepath = Dts.Variables["User::Var_File_Path"].Value.ToString();
Dts.Variables["User::Var_DoesFileExist"].Value = File.Exists(filepath);
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
TAsks in order in SSIS :
1. BCP data into flat file
2. Script Task to check if File exists from step 1
3. Truncate table if File exists from 2
I want to add another condition : Rowcount of Flat File > 0 along with @Var_DoesFileExist == TRUE which I already have
How do I know the Rowcount of the flat file ?
Thanks,
PSB
May 19, 2016 at 9:52 am
You could probably do this in your script task by opening the file and counting the rows.
It'd probably be easier though to add a data flow after your script to check for the file, with your flat file as the source and a rowcount transformation as the destination. The rowcount transformation will let you assign the count of rows to a variable.
May 20, 2016 at 1:55 am
Another way of doing this is to use a For Each Loop Container. Set the enumerator to Foreach File, Folder and Files as appropriate.
If the file exists, the container will execute the tasks within it. To get the row count you create a data flow task just to read the file contents into a package variable.
Why do you need to know how many rows there are? If you have a data flow to load the file into a staging table then if there are no rows then nothing gets loaded and if there are rows then the data is loaded so you only read the file once.
Jez
May 20, 2016 at 6:48 am
I generally use a ForEach Loop for file detection for the simplicity, but there is at least one situation where it may not be appropriate. The ForEach Loop will run for every file in the specified folder, so if you are expecting one and only one file and want to verify that there is only one file, then you might want to use a script task instead.
May 20, 2016 at 6:52 am
If the file name is unique then you can put that specific file name in the Files text box and then it only checks for that file.
What it doesn't allow for is if the file is still be written to and not available for reading e.g. during a file copy of FTP process.
Jez
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply