Code change to • Integration Services package (dtsx) built on SQL Server 2008 Standard
€8-30 EUR
Paid on delivery
Description Current Process
The package currently takes a 'flat file' ( CSV) from a specific network folder address ( for example T:\02output\) and transforms this flat file into a XML file which it then saves to another network folder address ( for example T:\03xmlexact).
The XML files is a Sales Order file for an ERP Package
Contained in the script code is some logic that adds 'freight lines' to the Sales Order, the relevant excerpt of this code is below
// adding freight
if (quantity > 0)
{
sql = @"SELECT [url removed, login to view]
FROM staffl st
INNER JOIN cicmpy c ON c.cmp_wwn=[url removed, login to view]
WHERE [url removed, login to view] ='FREIGHT'
and LTRIM(RTRIM([url removed, login to view]))='" + [url removed, login to view]("'", "''") + "' and '" +
orderDate2 + @"' between [url removed, login to view] and [url removed, login to view]
order by [url removed, login to view]
";
cmd = new OleDbCommand(sql, cnn);
OleDbDataReader dr = [url removed, login to view]();
int i = 2; // line no
while ([url removed, login to view]())
{
string artcode = [url removed, login to view](0).Trim();
el = (XmlElement)[url removed, login to view]("//eExact/Orders/Order/OrderLine").Clone();
[url removed, login to view]["lineNo"].Value = [url removed, login to view]();
[url removed, login to view]("Item").Attributes["code"].Value = artcode;
if (artcode == "FRT") [url removed, login to view]("Quantity").InnerText = "1";
[url removed, login to view]("//eExact/Orders/Order").AppendChild(el);
i++;
}
}
Requirement
We need to add some further logic to the code which determines when freight lines should be added. Currently freight lines are always applied if (quantity > 0) in the code extract above
We wish this to be changed so that in addition to the condition that ' if (quantity > 0)' that freight will only be applied where the haulier and debtor fields in the inputted 'flat file' meet the additional conditions below
Haulier in ('A002', 'B002','C001','M002') or
the Haulier = 'A001' and Debtor <> '1027' or
Haulier = 'D002' and Debtor not in ('1049','1119') or
Haulier = 'R001' and Debtor <> '1027' or
Haulier = 'R101' and Debtor not in ('1003','1165','1119') or
Haulier = 'S001' and Debtor <> '1027'
The Microsoft Visual C# 2008 code of the attached package should be changed for these extra conditions
Project ID: #5936103
About the project
Awarded to:
Hi keanebrendan, The script should not be too hard to modify, will I be able to have the file to test in the SSIS ? Thanks, John
11 freelancers are bidding on average €56 for this job
Hello, Will you provide csv files and DB backup or remote access to your computer for a test? Best regards, Elena
Hi, I have excellent work exp in sql server . I can fix this sql query for you. Lets discuss more about the project. Thanks
Hi! I', a SQL Server expert and developer, experience in SSIS and .NET. I can modify your dtsx and add the condition that it needs. Thanks in advance!
Hello Keanebrendan, I am IT engineer with European diploma since 2005 Certified as Technical specialist in Microsoft SQL Server databases BI, i have more than 8 years in IT projects with 5 years working around data More
Hi, I have gone through your descriptions and Attachments,I understand it thoroughly,I am 4 + years experienced ASP.NET,C# developer at CMM5 level company.I can do your job easily before your deadline . I can give you More
Good morning, I'm an experienced SQL Server developer and I can do that changes for you. Feel free to ask any questions.