Category Archives: SSIS

SSIS

SSIS – ForEach looping import files

One of the harder things to learn in SSIS is the looping through files and importing them into the DB to be able to work with them. Almost all integrations produce files as [what I am]-[datetime].csv. For example recruiting-Application_Attachments-20211012 00558 is the manifest file extracting the recruiting application attachments and the time-date at the back let’s us know when.

Hopefully the export can take place using one file but don’t count on it, you have to apply looping throughout the whole process, from whatever is doing the extract, through the transfer and onto the load. So if you have a dozen files like above (or 100, 1000, 10000) you need to loop through and transfer.

This walk through from stack overflow is dead on and should get you started in the right direction.

SSIS

Unable to prepare the SSIS bulk insert for data insertion

Minor point of annoyance to keep in mind, always run BIDS as administrator otherwise you’ll get some jacked up errors, Microsoft wrote an article about it here.

C# SSIS

C# to move files in SSIS script task

Most of the time that I’m creating integration packages, I have a central working directory where all the action is taking place, at the end of the package execution is where I’ll archive files so that should they be needed to troubleshoot an issue I’ve got them in a handy place.  This is a script that I created for that process.  Note that lines 7 through 19 are generated by the Visual Studio Tools for Applications in SSIS when you create the script task.

using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_218640c1eafd4eb9ab909245d34e01c7.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
string strOriginalFileName = @"C:\TCC\20110722-UpdateCandidate\UpdateHireEmpStatus.csv";
string strArchivedFileName;
string strDate;
DateTime dtDate = DateTime.Now;

strDate = String.Format("{0:yyyyMMdd_HH_mm_ss}", dtDate);

strArchivedFileName = @"C:\TCC\20110722-UpdateCandidate\Archive\UpdateHireEmpStatus-" + strDate + ".csv";

File.Move(strOriginalFileName, strArchivedFileName);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}