|
This is why we rip the T out of ETL, load the source into a staging table where every field is varchar and use a stored proc to do the transforms. SSIS is not alone in these idiotic inconsistencies, Biztalk and most of the big players in ETL also have them.
It is also way easier to debug and test the transforms using a proc!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I hear ya. This is my first time (eight months now) using SSIS and I don't like it. Previously I always used code. I'm doing a lot more pieces of this SSIS with "Script Tasks" -- C# code.
|
|
|
|
|
I had a class called filehandler, used in c# apps to load csv files, when we first started using SSIS I just hacked that class into the script objects, always intending to move to the SSIS data objects. It never actually happened, they are still using that class and a proc to do the transforms so SSIS is just an execution and scheduling shell.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The problem you are experiencing is a data type issue. The source is varchar, which means SSIS read and mapped the source adaptor to varchar and its output column to varchar. Inserting that varchar into the int caused your problem.
To prevent this issue, in the data flow task -> Right click on the source -> scroll down to “Show Advanced Editor”
Select “Input and Output Properties”
Navigate down to “Output Columns”
Locate the column in question and change the data type to something more appropriate such as “four-byte signed integer [DT_I4]”
SSIS tries to guess what the source data type is and land it in the same data type without knowing everything in advance. SSMS builds the query first with the set of data then does the insert, so it knows to recast the data to the proper type.
You could also change the error handling of the destination object to ignore the error. While this works as well it could lead to other issues and is less than desirable.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
please guide me..This my first project using asp.net2012 and sqlserver 2008...
given bellow details are my project SRS...
Software Requirement Specification
For
DESPATCH
AUTOMATION
Module: Container Planning
Version 1.5
KISL Technology Centre Sdn Bhd
Submitted to:
Submitted on:
Contents
Introduction 3
Purpose 3
Document conventions 3
Intended Audience and reading suggestions 3
Dispatch Automation Module (DAM) – Introduction 4
Assumptions 4
Project Goals 5
1. Container Planning 6
User Requirement 6
Validations 8
General Rules 8
2. Loading Schedule 9
User Requirement 9
Root Cause analysis Report 10
Introduction
Purpose
This document elaborates the functional requirements of the despatch automation module (DAM) of MetTube’s ERP System.
Document conventions
Heading
Font type: Verdana
Font size: 12
Sub Headings and Text
Font type: Verdana
Font size: 10
Requirement Number: ERP / DAM / Process name / Serial Number
Intended Audience and reading suggestions
MetTube’s IT and user team to review the current requirements
Base document for MetTube to address their future requirements
Technical details:
• User Interface: Vb.NET 2010
• Database: SQL Server 2008
• Operating System: Windows Server 2003
• Client Systems: Windows XP Professional
• Additional Software required: Crystal Reports Engine
Dispatch Automation Module (DAM) – Introduction
The main objective of DAM is to automate the process involved in the dispatch process and reduce the finished goods (FG) inventory.
The primary focus areas are as follows:
a. Vessel booking screen for booking the vessel against a mill order or a combination of mill orders.
b. Automated E-mail option for sending the vessel booking information to the shipping agent.
c. Mapping the ROT number information against the vessel booking module for tracking.
d. Providing necessary inputs to logistic Assistant (Malek) for planning the despatch
e. Automated E-mail option to the konsortium for tracking the container pull-in and pull out information
f. Providing all the necessary reports
g. Along with the above, automating the container utilization process
Assumptions
• Dispatch quantity for export order is defined one full container
• In our experience 90% of orders are 40 foot container where target quantity is approximately 18 MT, 10% of orders are 20 foot containers where the target quantity is approximately 9 MT
• Since the order intake system is yet to be qualify by marketing with Preactor ,it is assumed the plant is already confirmed the uniform dispatch available which should be typically around 675 to 700 MT per week assuming incomplete orders (WIP) of 300 MT per month as per historic data
• The Finished Goods (FG) is available from previous month in addition to dispatch quantity produced from the net production of the month which is average at 3000 MT to 3150 MT
• Marketing (Andrew/Azfa) would release 1500 Mt loading always on 20th of prior month of production month
• During lean period this load may be 2000 MT approx
• Marketing should ensure that order intake would match to plant capacity in the ratio of 80 : 15 : 05 % for GW : PW :PP format on weekly basis
• Daily match is practically not possible as customers’ orders are not in 80 : 15 :5 % format
• Preactor planning would give promise date for 1st loading latest by 23rd or within 3 days
• Promise date would match nearest to request date to match sailing data for smooth production – dispatch outflow
• As per this document heading 6 titled Promise date channels .This system would show red channel for mismatch and green channel for matching scenarios .In the event of mismatch FG may sit for days or we may miss OTD(On Time Delivery)
• The plant has confirmed to provide equalize dispatch able quantity by day/week which are match to sailing date
• The plant has confirmed to match the plant production uniformly to maximum possible extend to request date
• The request date are being matched by marketing to sailing date to ensure that FG is not made much before 72 hours and after the 72 hours cycle
• In both case FG may sit for long time in MetTube.
• 7 day advance notice is necessary for container space booking ,ROTs ,container confirmation & container pull in dates
• 7 day plan would help in matching production – container pull-in ,the smooth despatch outflow & minimum FG
•
Project Goals
1. Minimum FG at any point
2. On time delivery to customer
3. Minimum disruptions to supply chain
4. Maximize cost benefits to Mettube
In order to achieve all the above 4 goals it’s important that order intake ,promise date confirmation ,production ,vessel booking ,container pull in ,material loading ,container pull out and sailing is FULLY INTEGRATED & MATCHED
It’s extremely important that all the responsible personnel / departments in the supply chain strictly dear to completing the task on specified
1. Container Planning
User Requirement
• User can select the planning date so that system will list out all the vessel booking numbers which are not planned
• User can select an option to list all the containers with ROT number or they can choose only pending pull out date containers
• If that vessel booking no has more than one container then option will be given to choose containers
• In the above case user can plan for a single container against the vessel booking as per the shipping availability
• Container pull and pull date can be put in the system for tracking
• After the booking is completed, user can send the e-mail to the konsortium line With preview option
• Remarks field will be given to enter the remarks
• According to the data which is entered in this screen and warehouse container status screen, system will generate the report to consortium
• Once the pack list is generated in the erp system, then the vessel will be considered as ready to pull out
• If the konsortium is not sending the container as per plan, then the system will add those containers into a pending list
• The above report will be send to konsortium with preview option
• A column will be added in the report for konsortium confirmation Konsortium personnel can fill the column as ok or not ok and send to the Mettube Sdn Bhd
• If the Pull In & Pull Out events happened as per system suggested dates and ETD is matched then it will be called as GREEN Lane
• If there is a mismatch between system suggested pull in ,pull out dates and actual pull in & pull out then it ‘s called RED Channel
• In case of Red channel route system will provide a report for finding the route cost and effects
Root causes:
The route may be the following factors
a) Wrong promise date
b) Early ready date
c) Container missed ROT
d) Container Pull In Delay
e) Container Pull Out Delay
Effects
a) No of days of FG
b) Missed OTD
c) Cost of FG
d) Business lost
e) Penalty
Validations
• As per the plan date, the system will check all the vessel booking numbers which are not planned for container
• Flexibility will be available to choose the container as per shipping availability
• Pull out date will be greater than pull in date
• Plan date will be greater than or equal to current date
• Container type is not editable, that will come from vessel booking module
• Container pull in date will be calculated from promise date and the date difference will be taken from interval master
• Container pull out date will be calculated from loading date and the date difference will be taken from interval master
• Only AGM –Commercial (Mr.Nathan) can change the system calculated pull in and pull out dates in the system.
General Rules
• Sailing date ,Request date ,Promise dates are entered / calculated in the system is FROZEN dates
• higher authorities can change the dates when it’s needed
2. Loading Schedule
User Requirement
• User selects the mill order when the container is ready to load
• This report will be generated and given to the warehouse personnel for loading
• The MTD dispatch data will be available for reference
• User can plan the loading schedule only for the ROT no available containers
• ROT No will be added in the Loading schedule report
• Container no,Pull in date and Pull Out field will be included in the loading schedule screen
Root Cause analysis Report
In case of mismatch shipment between system generated & actual shipping then for those containers a detailed root cause analysis report will be provided
The delay may be due to the following reasons
1. Container problems (Malek)
2. Loading problems (Ware House)
|
|
|
|
|
SubiyaPalanisamy wrote:
Intended Audience and reading suggestions
MetTube’s IT and user team to review the current requirements
Base document for MetTube to address their future requirements
That did not include CodeProject; this looks like a confidential document. I suggest you edit it a bit, and delete everything that's not part of the question. You can get help with any specific question if you're stuck.
SubiyaPalanisamy wrote: This my first project using asp.net2012 and sqlserver 2008...
If you have had training, then I suggest you start with the use-cases and the functional design. If this is really the first project, then this might be a bit too complex to start with.
By when should this be finished?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
The program I created aims to display values on the seen textboxes. Values to be displayed here are computed via queries which utilize database views. (vw0112, vw0212, etc.) Currently, the program can perform displaying of monthly reports. The queries I’ve used to generate the values to be displayed are cut and stored on string variables because I need some parts of it to be dynamic (i.e. name of the view). For example, if I choose March on the first dropdown box, the value 03 will be generated and stored in a variable. If you choose 2012 in the year dropdown box, the value 12 will be stored in a variable. A formula is created for the program to be able to generate the view name, in our case, vw0312. My problem is that, I want for the program to perform cumulative addition on the values to be displayed on the textboxes based from the months to be chosen by the user. For example, if the user chooses March and June on the two drop boxes respectively, then what the report should display on each of the textboxes should be the sum of the values of the queries from the month of March to the month of June.
static string notdoh = " AND (hospital Not In ('SOUTHERN ISABELA GENERAL HOSPITAL','CAGAYAN VALLEY MEDICAL CENTER','VETERANS REGIONAL HOSPITAL'))";
static string where_pg1 = "WHERE hosp_categ Not In ('S','T') And hosp_class='G' " + notdoh;
static string where_pg2 = " Or hosp_categ=' ' And hosp_class='G' " + notdoh;
static string where_prigov = where_pg1 + where_pg2;
string sel_amt = "SELECT SUM(totalamount)";
string fr_amt_orig = "FROM vw";
string fr_amt = "FROM vw";
string SQLquery_amt = "";
string sel_count = "SELECT COUNT(*)";
string fr_count_orig = "FROM vw";
string fr_count = "FROM vw";
string SQLquery_count = "";
string qm = " ";
string var;
SqlCommand cmd = new SqlCommand();
string SQL_cmd = "";
public void amount(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_amt += (var + ind1_p + sub_p);
SQLquery_amt = sel_amt + qm + fr_amt;
fr_amt = fr_amt_orig;
}
public void count(int slctdind1_p, int ind1_p, string sub_p)
{
if ((slctdind1_p + 1) < 10)
var = "0";
else
var = "";
fr_count += (var + ind1_p + sub_p);
SQLquery_count = sel_count + qm + fr_count;
fr_count = fr_count_orig;
}
public void btnGenerateGov_Click(object sender, EventArgs e)
{
cnn.ConnectionString = Properties.Settings.Default.DBInfo;
string year = cbYearGov.Text;
string sub = year.Substring(2, 2);
int slctdind1 = cbMonthGov.SelectedIndex;
int slctdind2 = cbMonth2Gov.SelectedIndex;
int ind1 = slctdind1 + 1;
int ind2 = slctdind2 + 1;
amount(slctdind1, ind1, sub);
count(slctdind1, ind1, sub);
try
{
cnn.Open();
#region government
SQL_cmd = SQLquery_count + " " + where_prigov;
txtPGClaims.Text = claims(cmd, SQL_cmd);
SQL_cmd = SQLquery_amt + " " + where_prigov;
txtPGAmt.Text = claims(cmd, SQL_cmd);
}
|
|
|
|
|
I think you are using the wrong tool for the job. You have a bunch of views (1 for each month) that you are trying to force into a larger structure (aggregated monthly values) why not ignore the monthly views and go directly to the underlying table/view to service your requirement.
SELECT MthField, SUM(field1), COUNT(Field2)
FROM TransactionView
WHERE filter = ....
GROUP BY MthField
OR
If you are using SSRS (you mention report) then use SSRS aggregating functions to get you totals.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi Guys , i am a bit stuck here
i have this value "0.833333333333333"
and i want the asnwer to be 83 , i tried this
SELECT ROUND(0.833333333333333,2)
but it does not work
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
SELECT TRUNC(0.833333333333333 * 100)
Add .5 prior to multiplying if you actually need to "round" the number.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks
DECLARE @Value FLOAT = 0.833333333333333;
SELECT CAST(@Value * 100 AS INT);
Worked for me
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Declare @X float = 0.833333333333333
SELECT ROUND(@X*100,0)
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
How to convert Physical XML files into Sqlserver Tables?
* Physical XML need to be converted into XML table with respective columns
* XML file may contains multiple tables, in such case multiple tables has to be created in db
Regards,
Murali Kumar Pitchika
http://muralip.f2g.net
|
|
|
|
|
I wasn't aware that XML files were physical. 
What you are trying to do depends on the schema. Can you show an example?
|
|
|
|
|
Member 4006546 wrote: How to convert Physical XML files into Sqlserver Tables?
..documentation[^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
How do you use the AVG api in sql to manage grouped output? For example:
Given a SQL Table “Employee”
Name Salary City
John Doe 15000 Seattle
Jane Doe 30000 Redmond
Tim Wayne 25000 Seattle
Write a SQL query that would return the average salary per city in the format shown below:
City Salary
Seattle 20000
Redmond 30000
|
|
|
|
|
Xarzu wrote: Write a SQL query that would return the average salary per city in the format shown below:
For some reason this sounds like homework. What have you done so far for a query?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Select City, AVG(Salary) from Employee where ... I don't know the rest
Where city == city or something like that?
|
|
|
|
|
You could try:
select city, avg(salary) from Employee group by city
This will give you the average grouped by cities. How to sort the cities in the order you want to order by in your expected result, I'm leaving that as an exercise 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
modified 11-Aug-12 20:19pm.
|
|
|
|
|
Xarzu wrote: How do you use the AVG api function in sql
It's not an "API".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
All Version of Sql server
Select City, AVG(Salary) AS Salary
FROM @t
Group By City
Order By City DESC
This can also be done by treating AVG function as Analytical function which is available since SQL Server 2005
Select City,Salary From
(
Select
Rn = Row_Number() Over(PARTITION BY City Order By (Select 1))
,City
,AVG(Salary) OVER(PARTITION BY City) AS Salary
From @t) X
Where X.Rn = 1
Order By City DESC
The answer for both the cases
City Salary
Seattle 20000
Redmond 30000
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
I have a question that's more of the hypothetical kind.
I have two tables:
Users| userid | username |
|---|
| 1 | John Doe | | 2 | Jane Doe |
email| userid | email |
|---|
| 1 | john.doe@myworkmail.com | | 1 | john.doe@myprivatemail.net | | 1 | john.doe@someothermail.org | | 2 | jane.doe@myworkmail.com | | 2 | jane.doe@myprivatemail.net | | 2 | jane.doe@someothermail.org |
Now I'd like to make one (or zero) email address per user the default address.
On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column.
This would of course not work on certain databases that don't allow more than one null value when having a unique key.
So what other solutions are there? Preferrably not allowing null values.
|
|
|
|
|
How about a bit field (0/1;true/false)? Set the default to 0, not null.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
How would that allow only one emailaddress to be set to default?
|
|
|
|
|
The default is set to true, the rest to false.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
