C# – SSIS Dynamic package loading

C# – SSIS Dynamic package loading. Load a SSIS package using .net c#

 

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_43cbf51a82014806a9fa9aeec94bdb08
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

class MyEventListener : DefaultEvents
{
public override bool OnError(DtsObject source, int errorCode, string subComponent,
string description, string helpFile, int helpContext, string idofInterfaceWithError)
{
// Add application-specific diagnostics here.
Console.WriteLine(“Error test in {0}/{1} : {2}”, source, subComponent, description);
return false;
}
}
public void Main()
{
string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;

MyEventListener eventListener = new MyEventListener();

pkgLocation = Dts.Variables[“User::package_directory”].Value.ToString() + Dts.Variables[“User::package_name”].Value.ToString();

app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);

foreach (ConnectionManager conn in pkg.Connections)
{
Dts.Variables[“User::package_connection_string”].Value = conn.ConnectionString.ToString();
}
}
}
}

Advertisements

Excel Lookup against the sql table

My working excel vba function:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)
On Error GoTo Error:
SQLookup = “Error”
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

Dim connString As String
connString = “driver={SQL Server};” & _
“server=server_name;Trusted Connect=Yes;database=database_name”
Conn.ConnectionString = connString
Conn.Open

‘ If Conn.State = adStateOpen Then
‘ MsgBox “Welcome to Pubs!”
‘ Else
‘ MsgBox “Sorry. No Pubs today.”
‘ End If

Set RecSet = New Recordset

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” = ‘” & LkUpValue & “‘”, connString

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit Function
Error: SQLookup = Err.Number & “|” & Err.Source & “|” & Err.Description
End Function

My working calling the excel vba function:

=SQLookup(B2 (ExcelLookupFieldName), “LookupSqlColumnName”, “dbo.TableName”, “ReturnSqlColumnName”,$E$3)

Note: $E$3 is the connection string is not being used from the cell E3, it is embeded in the funciton.

Lookup against External Database

At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells.  I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.

While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.

External data in Excel

To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.

The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).

For this function to work, a reference to “Microsoft ActiveX Data Objects x.x Library ” must be made in VBA (under Tools Menu/References).

Macro References

The code for the function is as follows:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

SQLookup = “Error”

On Error GoTo Exit_Func

SQLookup = “No Conn”
Conn.Open ConnStr

Set RecSet = New Recordset

SQLookup = “Error”

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” like ‘” & LkUpValue & “‘”, ConnStr, , , adCmdText

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit_Func:

End Function

As an illustration of this function, let us assume that we have an SQL database named FRUITS containing a table tblFRUIT with the following entries:

Fruit Table

An example of the connection string to this database would be as follows:

“DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits”

A good reference for conneciton strings is this site.

To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP(“Grapes”,”Fruit”,”tblFRUIT”,”Quantity”,A1). The cell A1 contains the connection string.

This function looks for the field “Grapes” under the field “Fruit” from the database table and returns the corresponding value under the field “Quantity”, which is 31.

Like all Excel functions, the various parameters can come from other cells, see example below.

SQLookup Example

Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).

A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.

SSIS: Package using SQL Authentication and DontSaveSensitive as ProtectionLevel

In this post i am trying to look into steps to follow while running SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel.

I created a simple SSIS package using a connection manager going to SQL Server using SQL authentication, I have my ProtectionLevel as EncryptSensitiveWithUserKey

clip_image001

clip_image003

My package has an OLEDB Source (using connection manager above) which queries a table in database and dumps few rows in flat file.

clip_image005

clip_image006

With above settings ,when I execute my package, Its works like a charm.

clip_image007

Now, I changed the ProtectionLevel  to DontSaveSensitive

clip_image008

And package failed

clip_image009

With following error

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at Package, Connection manager “runeet2k8.sa”: An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0x80040E4D  Description: “Login failed for user ‘sa’.”.

Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager “runeet2k8.sa” failed with error code 0xC0202009.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component “OLE DB Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

What we see above is expected as we have sensitive information in our package (password for SQL account) and have chosen DontSaveSensitive as protection level. The affect is Password being cleared from our connection manager hence login failure.

For this situation, We would have to save this password somewhere outside the package. I chose to save it in configuration file as shown below. I used XML Configuration file

clip_image011

clip_image012

I had to chose correct connection manager, Its called runeet2k8.sa in my case

clip_image013

This is the XML configuration file that BIDS wrote for me

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”Domain\UserName” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

 

I have to go in and type password value.

 

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”FAREAST\runeetv” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

  <ConfiguredValue>Password</ConfiguredValue>

</Configuration>

</DTSConfiguration>

Now i have a package with ProtectionLevel as DontSaveSensitive and password value for connection manager saved in a configuration file outside the package. Package executed absolutely fine after this.

clip_image014

I went ahead and set this package as Integration Services Job under SQL Agent.

clip_image015

Executed the job and it ran successfully.

clip_image016

One important thing to note is that if you are moving your package from your development machine to say test server for execution. You would have to move your configuration file as well and put it at same path on target machine where its placed on your development box.

For example, on my box, the config file PackageConfig.dtsconfig  is placed at D:\Cases\Integration Services Project1\PackageConfig.dtsconfig and if I am moving my package from my development machine to somewhere else, I would have to move my config file as well and place it at location D:\Cases\Integration Services Project1\ on that machine.

If you don’t have dtsconfig at expected location, Job would fail.

If you done want to create same complex folder path for dtsconfig on target machine as its on your dev box (eg: D:\Cases\Integration Services Project1\) You could place this dtsconfig file anywhere you want and give the pointer of this path from Configuration tab  under you SSIS job step.

clip_image001[4]

SQL Query: How to retrieve the Table columns from a stored procedure

SELECT
name AS ColumnName
,system_type_name AS DataType
,CASE
WHEN is_nullable=0 THEN ‘NOT NULL’
WHEN is_nullable=1 THEN ‘NULL’
END AS IS_NULLABLE
,column_ordinal AS OrdinalPosition
,(‘Col_’ + CAST(column_ordinal AS varchar(4))) AS Alias
FROM sys.dm_exec_describe_first_result_set
(N'[dbo.Registration]’, null, 0) ;

 

SQL SERVER – How to retrieve the metadata of a stored procedure

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

1
2
3
4
5
USE AdventureWorks2012;
GO
SELECT FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
 --OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO
SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Regex: query examples and website links

source link:

  1. debuggex.com
  2. regex101.com

OS_PostR_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

OS_Rec_A_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.ctrl
OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.txt
OS_PostRec_June 2017_EMMain_07L_170820163023_0ab98771e1_T2O_20170904140654_20170904_141835_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

 

2017_M04_Postrec_July_2017_Basildon & Brentwood_99E_allprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_ALLprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_nt2048_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_rddRF4_20171003_AiC.zip

 

 

Regular expression to find month names:

(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))

 

(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})

 

Regular expression tofind Month Year:

((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))

 

 

 

RecOrPostRec:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])

 

CCGCode:

(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])

 

FileType (EMMain/EPMain):

(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?:(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_]))

Save:

(?<RecOrPostRec>_Rec_)|(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))|(?<Year>[0-9]{4}_)|(?<FileType>_EMMain_)|(?<CCGCode>_[0-9][0-9a-zA-Z]{2}_)

Full regex – never change or delete:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>(?:FFP)|(?:T2O))

Full regex – v2:

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z]{2,15}_)?[a-z]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)

AiC Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

 

AiC Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

Current version:

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)(?<Extra>\S*)(?<FileExtension>[.]+[a-z]+)

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart1>[a-zA-Z0-9_]*))_(?<T2O>T2O)?_(?:(?<UniquePart2>[a-zA-Z0-9_]*))_(?<FFP>FFP)(?<FileExtension>[.]+[a-z]+)

 

Regular expression to select archive name:

(?<FinancialYear>[0-9]{4})_(?<FinancialMonthName>[a-z]+[0-9]{2})_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))_(?<CalendarYear>[0-9]{4})_(?<CCGName>(?:[a-z& _]+))_(?<CCGCode>[0-9][0-9a-z]{2})_(?<ExtractType>[a-z0-9]+)_(?<Date>[0-9]{8})_(?<ProcessorType>[a-z]{3})(?<FileExtension>[.]+zip)

 

Regular expression to select RECT files:

(OS)_((Post)([a-zA-Z])|(Rec)(_[A-Z]))_RECT_([a-zA-z0-9_])*FFP.csv

(?<ExtractSetType>OS)_((?<ConcilationPostRec>Post)([a-z])*|(?<ConcilationRec>Rec)(_[a-z])*)_(?<FileType>RECT)_([a-z0-9-_])*(?<ProcessorTypeRECT>COMPLETE_NP_FFP).(?<FileExtension>csv)

SSIS C#: Read two variables, join them in a two dimensional array, assign to an object variable

SSIS C#: Read two variables, join them in a two-dimensional array, assign to an object variable.

–write DataTable to an object variable

Part 1 plan:

  1. I will receive value in project parameter. For example: SSIS Project variable CCGCode=0A;1D;2J, SSIS Project variable PCTCode=0A;1D;2J
  2. Read the multiple values from the project parameter separated by semicolon;
  3. Split the values by semicolon then insert into a DataTable
  4. Assign the DataTable to an Object Variable

Part 2 plan:

  1. Foreach ADO Enumerator
    1. Collection: Read from the Object Variable
    2. Map the values to a string variable
  2. Use the values to in the task

public void Main()
{

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
dt.Columns.Add(“CCGCode”, typeof(string));
dt.Columns.Add(“PCTCode”, typeof(string));

string[] ccgCodes, pctCodes;
ccgCodes = Dts.Variables[“$Project::CCGCode”].Value.ToString().Split(‘;’);
pctCodes = Dts.Variables[“$Project::PCTCode”].Value.ToString().Split(‘;’);
for (int i = 0; i < ccgCodes.Length; i++)
{
DataRow dr = dt.NewRow();

//without using the string.IsNullOrEmpty() check it fails, even the input does not have any null values.
dr[“CCGCode”] = string.IsNullOrEmpty(ccgCodes[i])? “NUL” : ccgCodes[i];
dr[“PCTCode”] = string.IsNullOrEmpty(pctCodes[i]) ? “NUL” : pctCodes[i];
dt.Rows.Add(dr);
//MessageBox.Show(dr[“CCGCode”].ToString());
//MessageBox.Show(dr[“PCTCode”].ToString());
//codes.Add(new CCGPCTCodes(ccgCode[i], pctCode[i]));
}

try
{
Dts.Variables[“User::objCodes”].Value = dt;
}
catch (Exception e)
{
Dts.Events.FireError(0, null, e.Message, null, 0);
}
}