Changeing Dataflow Tasks Propeties By Coding

Mar 27, 2007

Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
Cheers

View 6 Replies


ADVERTISEMENT

BIG SSIS PACKAGE (200 DATAFLOW TASKS)

Mar 29, 2008

Thanks in advance in reading this thread.



I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).

The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.

When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...

I have attached the an lousing .jpeg.

It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.


Thanks in Advance.

View 4 Replies View Related

Can We Format The Data In Dataflow Tasks?

Dec 27, 2007



hi all;
i have a doubt;
can we modify the data / format the data in a dataflow task?
Requirement

1. Excel file source
2. Convert data "Dec-07"(Mon-YY format) into "Dec-2007"(Mon-YYYY)
3. OLEDB Destination

how to do the second step?

View 8 Replies View Related

SSIS PACKAGE + 200 DataFlow Tasks && T-SQL Validations

Mar 30, 2008

Thanks in advance in reading this thread.



I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).

The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.

When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...

I have attached the an lousing .jpeg.

It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.


Thanks in Advance.

View 5 Replies View Related

Setup 5 Dataflow Tasks In Series Or Parallel?

Jan 19, 2008

I've create a package that currently uses 5 DataFlow tasks connected in series to get data from 5 different files and place that information into 5 different temp tables. Each Dataflow task contains only a OLE Source, a row count and a OLE destination. My question is - Is it normal practise to keep each of these separate, or should I put them all into a single DataFlow? The package should only continue if all five dataflow task complete successfully.

View 7 Replies View Related

Special Error Handle In A Dataflow Transformation Tasks

Jan 22, 2008

Hello,


How would you do a log in a massive rows loading, I'm having problems because every row error(because of casting, format, lookup) in a transformation task is redirected to a text file as a log, this is ok when only exist one error by row, but in the case when I have two errors in the same row detected by diferents transformation tasks only the first one is reported to the text file, I have to wait to the second information load, after I correct the first error, to find the second one, I need to validate as many errors exists by row in the same load...

which component or which strategy can I use in a SSIS Packge to achieve this?

thanks

View 1 Replies View Related

Programatically Creating Dataflow Tasks Require Assembly Reference

Jan 17, 2007

Hi,

I am creating dataflow tasj using following

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:Program FilesMicrosoft SQL Server90SDKAssemblies.

How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?

Thanks

Mohit

View 4 Replies View Related

Changeing Remote Ports

May 30, 2007

I have two instances of SQl 2005 running on a server. One I'm going to allow outside remote access to. But I don't want to do it on the default port. though I have it allowing remote and that seems to be working. I can seem to find where to change the default listening port.

and I scimmed the help and topics I saw. But didn't see one relevant to this question.

View 1 Replies View Related

Changeing Authentication Mode After Installation

Dec 20, 2007

Is it possible to change authentication mode after sql express has allready been installed?

I have admin rights on the computer and i want to change authentication mode to "sql server and windowns authentications mode".
Is this possible somehow?
Maybe by logging in with a trusted conncection?

I need to do it unattended on several computers after they have been installed.

View 4 Replies View Related

Execute A Query Inside Dataflow And Use The Fields Returned To Continue Dataflow... How?

Apr 17, 2007

Dear Friends,

I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...

I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(

How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?

Thanks!!

View 38 Replies View Related

Changeing The Table Name While Using Transfer Sql Server Object

Mar 30, 2007

Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :

I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks

View 7 Replies View Related

Help In Coding

Jun 23, 2006

 Dim objConn As New SqlConnection        Dim objCmd As New SqlCommand        Dim Value As String = EventCmb.SelectedItem.ToString()        objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"        Try            objConn.Open()            objCmd.Connection = objConn            objCmd.CommandType = CommandType.Text            objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter            objCmd.Parameters.AddWithValue("@Value", Value)    ' Add the @value withthe actual value that should be put. This makes it securer            Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.            If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then                ContactLbl.Text = RetVal.ToString()            Else                ' noting was returned            End If        Catch ex As Exception            Throw ex        Finally            objConn.Close()        End Try
There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "
Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."
It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!

View 1 Replies View Related

SQL Coding

Jul 2, 2002

I'm attempting to copy data from an Epicore server with multiple company
databases to another server. All this data will be placed in a single
table on the receiving server with the addition of an database identifier,
so that the receiving department will know, to which company the data
belongs. I have code that will dynamically create the SQL and then
execute it using EXEC (@SQL), and this works just fine acrosss multiple
databases. I've read in my Transact-SQL Programming book that using
EXEC() is not limited to 255 characters, but it still appears that my
dynamically created SQL is being shortened. The insert statement and the
select statement list each column, and here is that code:

SELECT @CMD = "INSERT ##APMaster(Company, timestamp, vendor_code,
pay_to_code, address_name,
short_name, addr1, addr2, addr3, addr4, addr5, addr6, addr_sort1,
addr_sort2, addr_sort3, address_type,
status_type, attention_name, attention_phone, contact_name, contact_phone,
tlx_twx, phone_1, phone_2,
tax_code, terms_code, fob_code, posting_code, location_code,
orig_zone_code, customer_code,
affiliated_vend_code, alt_vendor_code, comment_code, vend_class_code,
branch_code, pay_to_hist_flag,
item_hist_flag, credit_limit_flag, credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag,
restock_chg, prc_flag, vend_acct, tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time,
doc_ctrl_num, one_check_flag, dup_voucher_flag, dup_amt_flag, code_1099,
user_trx_type_code, payment_code,
limit_by_home, rate_type_home, rate_type_oper, nat_cur_code,
one_cur_vendor, cash_acct_code, city, state,
postal_code, country, freight_code, url, note) SELECT '" + @DBName + "' AS
Company, timestamp, vendor_code,
pay_to_code, address_name, short_name addr1, addr2, addr3, addr4, addr5,
addr6,
addr_sort1, addr_sort2, addr_sort3, address_type, status_type,
attention_name, attention_phone, contact_name,
contact_phone, tlx_twx, phone_1, phone_2, tax_code, terms_code, fob_code,
posting_code, location_code,
orig_zone_code, customer_code, affiliated_vend_code, alt_vendor_code,
comment_code, vend_class_code,
branch_code, pay_to_hist_flag, item_hist_flag, credit_limit_flag,
credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag, restock_chg, prc_flag, vend_acct,
tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time, doc_ctrl_num, one_check_flag, dup_voucher_flag,
dup_amt_flag, code_1099,
user_trx_type_code, payment_code, limit_by_home, rate_type_home,
rate_type_oper, nat_cur_code, one_cur_vendor,
cash_acct_code, city, state, postal_code, country, freight_code, url, note
FROM " + @DBName + "..APMaster"

Can anyone provide any insight to my problem?

TIA,
Chris

View 4 Replies View Related

Coding Help

Oct 31, 2001

Hello,

I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.

Thanks in advance,
Anne

begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date)
select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date
from TEAM3B_PULL_TOTAL_TST A
where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST)
and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST)
and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST)
and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE)
end

View 1 Replies View Related

Need Help With Right Sql Coding.

Apr 28, 2003

Hi im an newbee so im not the best but try to get there some day..



CREATE TABLE table_members

(id int(11) NOT NULL auto_increment,
fornamn char(60) default NULL,
efternamn char(60) default NULL,
address char(60) default NULL,
personnr INT(11) default 0,

smeknamn char(60) default NULL,

epost char(60) default NULL, i must be a @ value in it! any ides?

kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!

losenord char(60) default NULL, verlosenord char(60) default NULL,
How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!

PRIMARY KEY (id)
UNIQUE ('smeknamn');

Any ides are good

View 1 Replies View Related

I Need ASP.Net C# Coding

Mar 12, 2007

Help me...

I need C# Language ASP.Net coding...

i've two column... One for msg another for msgLastposted


in label control view msg(last visitor)....... that msg stored in my db.

Example Table Data
---------------------------------------------
MSg|MsgLastPosed

How edit forum |2007-03-12 10:50:25.747
How to Value Change|2007-03-12 10:56:36.373
Sql Command|2007-03-12 11:00:25.047
User Control|2007-03-12 11:02:10.793
How I can uninstall|2007-03-12 13:07:51.233
-----------------------------------------------

In table have many record..

label control display msg based on last visitor time.. after that again msg
changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..

View 1 Replies View Related

SQL CODING

Aug 28, 2007

Hi all,


I want codeing for to find (subtotal & total)the following

Input

productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal
gh 10 10
ab 10 10
Total


output

productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal 15 10
gh 10 10
ab 10 10
Total 20 20

View 3 Replies View Related

Coding Question

Jul 25, 2006

:This is a segement of VBscript for checking lastest Backup in SQLServer 2000:
'temporary variables to useDim sTemp1Dim i, sTemp2
'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2  end If
If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
 

View 3 Replies View Related

Confused. Need Some Help About SQL Coding

Jun 14, 2007

hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.


selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')



selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')


also is it wise to use Group by and having in sub-queries?

View 2 Replies View Related

Coding In Standard SQL ?

Jul 20, 2005

Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve

View 8 Replies View Related

Coding Question

May 16, 2007

I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.



Thanks

Andrew

View 2 Replies View Related

Stored Procedure Coding

Nov 2, 2004

Hello,

I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:

select * from Table where ...
and Official in (... problem area ...)

What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.

Any ideas? I don't want to use a dynamic query. Thanks,

Brian

View 5 Replies View Related

A Coding Challenge From The Blindman.

May 5, 2006

Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.

Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)

insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'

drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.

Who's up for the challenge?

View 14 Replies View Related

Beginner's Question To Coding

Oct 10, 2006

ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:

What is the total weight of different colored parts?

ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:

Select sum pweight
from parts
where distinct pcolor

looks horribly wrong so any help is appreciated. here is another one:

What colored part has total weight greater than 8 units?

select pcolor
from parts
where sum pweight > 8

???
i dunno lol.

there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?

View 2 Replies View Related

Grant Permissions Through Coding

Nov 14, 2006

Hello

Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.

Is it possible to grant permissions without specifying the username,passwd

Thanks In Advance
Poornima

View 16 Replies View Related

Confusing SQL Date Coding

Jun 22, 2007

I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:

DECODE(to_char(M.My_Table, 'MM'),
'04', 'Apr', '05', 'May', '06', 'Jun', '07',
'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct',
'11', 'Nov', '12', 'Dec', '01', 'Jan', '02',
'Feb', '03', 'Mar'),
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Q1', '05', 'Q1', '06', 'Q1',
'07', 'Q2', '08', 'Q2', '09', 'Q2',
'10', 'Q3', '11', 'Q3', '12', 'Q3',
'01', 'Q4', '02', 'Q4', '03', 'Q4'),

How does one convert something like this in MS SQL?

View 15 Replies View Related

TSQL Coding Style - An Example

Jun 11, 2007

Hi!If you like to spend some moments on my code examples,please look at it - usage is free (-:http://www.codeproject.com/useritems/TSQL_coding.aspFeedback welcome.GreetingsBjorn

View 1 Replies View Related

SQL - How To - I Need A Query Solution, No Coding....

Nov 1, 2007

Hi. I'm looking at a problem and I can't find any solution short ofcoding.I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any ofthe 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's saythat-bucket A has 100 PINS-bucket B has 300 PINS-bucket C has 600 PINS-bucket D has 200 PINS-bucket E has 500 PINS-bucket F has 350 PINS-bucket G has 700 PINSI need to know, for each PIN, the number of buckets (tables) itbelongs to, and which ones, i.e:- PIN 1 belongs to A, C, D, so it belongs to 3 buckets- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets- PIN 3 belongs to A, so it belongs to 1 bucket- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets- PIN 5 belongs to ..., so it belongs to 0 bucketsetc, etcWhat would be the simplest way to achieve that, please ?Thank you very muchAlex.

View 5 Replies View Related

UTF-8 Coding Of MSSQL 2005 DB

Mar 26, 2008

Hi List,I am searching franticly for a solution (or the procedure) to settingthe coding of a new DB to UTF-8. I can find no setting in the ServerManager, during creation of the DB, to influence this. Can someoneplease show me the way? Thanks--Shawn

View 3 Replies View Related

SSIS Versus Coding.

Jun 25, 2007

This question probably overlaps a few different topic areas.



As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).



Therefore I am having to look at alternatives.



With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.



I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.



Is there any scope in using data tables or have the read on one thread and write on another.



Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).



In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.



Any ideas on this area will be gratefully accepted.

View 2 Replies View Related

Is There Any Way To Insert Image Into DB Without Coding ??

May 25, 2007

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

View 3 Replies View Related

Is There Any Way To Insert Image Into DB Without Coding ??

May 25, 2007

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

View 1 Replies View Related

Can Somone Help Me Tell What Is Wrong With This Coding?

May 9, 2007

GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID

GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID

GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID

GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID

GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID

GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID

GLOBAL STRING result
GLOBAL STRING result2

FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3

result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");

IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");

IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END

SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END


FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");

IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END

SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");

IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END

IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);

END

IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END

SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END

INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END

STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END

STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END

INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END

INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END

STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END

STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END

INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END

INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END

STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END

STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END

FUNCTION dbTest()

INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");

IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

STRING FUNCTION getresult()
RETURN result
END

STRING FUNCTION getresult2()
RETURN result2
END

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved