SSIS Job

I am not sure how or if I can but can I cnvert this to an SSIS job?

The code below is VBScript

If I could could someone tell me what tools I should be using in SQL 2008..
'
' GL8 - SO_SalesOrderDeatil.m4t into SQL
'
' build a one SQL file from multiple MAS90 files 
' create the database and table, delete existing rows, insert rows
'
' Data From: EAGLE SO_SalesOrderDeatil import into MAS90            

'---------------Varibales--------------------------------------------------
' MAS90
    company  =  "ETG"                        'Uses SQL database MAS_###, data from MAS90
    table    =  "SO_SalesOrderDeatil"
    tmptable =  "importSO_SalesOrderDeatil"
    mDSN     =  "DSN=ESOTAMAS90"
    EagleDSN =  "DSN=EagleTrading"
    AccessDSN=  "DSN=AccessData"
    Server   =  "GSSRV011"
    DataBase =  "EagleTrading"
    Database2=  "AccessData"
    ET       =  "E"  ' Interchange Between E or T for Eagle Order or Test Order 

    nDays = 1
    sql_Delete = "Delete from " & tmptable
'--------------------------------------------------------------------------    

'--------------------------------------------------------------------------
'Set Input & Output variables
Dim StdIn, StdOut
Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

'--------------------------------------------------------------------------


'Eagle Connection here
    set Econnectiontodatabase = CreateObject("ADODB.Connection")
    Econnectiontodatabase.connectiontimeout=60  
    EConnectiontodatabase.open EagleDSN '(sConnectstring)

'AccessData connection here
    set Aconnectiontodatabase2 = CreateObject("ADODB.Connection")
    Aconnectiontodatabase2.connectiontimeout=60
    AConnectiontodatabase2.open AccessDSN 'Access Database
 
   wscript.echo "Using data from MAS90/200 Company " & company

'Get MAS90 data  MAS90 Connection String                                                              
 
    Set m90=CreateObject("ADODB.Connection")
    m90.ConnectionTimeout=60
    m90.Open mDSN

Econnectiontodatabase.execute(sql_Delete)

StdOut.WriteLine "Please Number of Orders to Export"
OrderNumber = StdIn.ReadLine
OrderNumber = cint(OrderNumber)

ReDim Preserve Order(OrderNumber)
ReDim Preserve whouse(OrderNumber)

i=0
 Do while i < OrderNumber

  StdOut.WriteLine "Please Enter CustomerPO " & i+1 & " of " & OrderNumber
  Order(i) = StdIn.ReadLine

  StdOut.WriteLine "Please Enter Warehouse for " & Order(i) & " (Please Enter Last 2 Digits only 3 is = 03)"
  whouse(i) = StdIn.ReadLine


  i= i+1 
Loop

j=0

Do while j < OrderNumber

     xcommand ="Select PO2_PurchaseOrderEntryLine.PurchaseOrderNumber,PO2_PurchaseOrderEntryLine.LineIndex,PO2_PurchaseOrderEntryLine.ItemNumber, " &_
               "PO2_PurchaseOrderEntryLine.UnitCost,PO2_PurchaseOrderEntryLine.QtyOrdered, " &_          
               "PO1_PurchaseOrderEntryHeader.PurchaseOrderDate, PO1_PurchaseOrderEntryHeader.RequiredExpireDate " &_ 
   	       "from PO2_PurchaseOrderEntryLine , PO1_PurchaseOrderEntryHeader " &_
               "where  PO2_PurchaseOrderEntryLine.PurchaseOrderNumber = PO1_PurchaseOrderEntryHeader.PurchaseOrderNumber " &_
	       "and PO2_PurchaseOrderEntryLine.PurchaseOrderNumber in ('000"& Order(j) &"')"	 

                                                           ' Wscript.echo xcommand 'debug
     Set gl8 = m90.Execute(xcommand)

  if gl8.BOF = True then 
  	if gl8.EOF = True then
  	        wscript.echo "No New Records to Update in last hour for "& table & VbCrLf 
  		wscript.quit		
  	end if	
  end if
     

  '  add data to table
wscript.echo "Inserting "& j+1 &" of "& i &" Selected Order(s) into Temp table "&tmptable&" then into Access Database from company " & company

    'SQL columns
    
    fields =          "PurchaseOrderNumber,"
    fields = fields & "LineIndex,"
    fields = fields & "EItemNumber,"
    fields = fields & "UnitCost,"
    fields = fields & "QtyOrdered,"
    fields = fields & "PurchaseOrderDate,"
    fields = fields & "RequiredExpireDate,"
    fields = fields & "ItemCode,"
    fields = fields & "WarehouseCode,"
    fields = fields & "TW_Cost"
  
  cnt = 0

  TW_Cost = 0.000
  
  gl8.MoveFirst
    do while not gl8.eof
     
       Item = left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5)
       xcommand2 = "Select Top 1 ItemNumber, Quantity, Case_Weight  from Product P inner join Product_Detail PD on " &_
                   "P.Product_ID=PD.Product_ID where CaseUPC in ('"& Item &"') " &_   
                   "group by ItemNumber, Quantity, P_End_Date, Case_Weight  Order by P_End_Date Desc " 
       
      
      Set ar1 = Aconnectiontodatabase2.execute(xcommand2)
      	IF ar1.EOF = True then 
      		
      		wscript.echo "Item "& left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5) &" was Skipped Check Order" 
      		gl8.MoveNext
      			Item = left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5)
      			xcommand2 = "Select Top 1 ItemNumber, Quantity, Case_Weight  from Product P inner join Product_Detail PD on " &_
		                "P.Product_ID=PD.Product_ID where CaseUPC in ('"& Item &"') " &_   
                   		"group by ItemNumber, Quantity, P_End_Date, Case_Weight  Order by P_End_Date Desc "
      		
      		Set ar1 = Aconnectiontodatabase2.execute(xcommand2)
      	End IF
      	
      
                     
' Data for SQL

       values =           "'" & gl8("PurchaseOrderNumber") & "'"
       values = values & ",'" & gl8("LineIndex") & "'"
       values = values & ",'" & Trim(gl8("ItemNumber")) & "'"
       values = values & ",'" & gl8("UnitCost") & "'"
       values = values & ",'" & gl8("QtyOrdered") & "'"
       values = values & ",'" & gl8("PurchaseOrderDate") & "'"
       values = values & ",'" & gl8("RequiredExpireDate") & "'"
       values = values & ",'" & ar1.Fields(0) & "'"
       Values = Values & ",'" & "0"& whouse(j) & "'"


OQty = CDbl(gl8.Fields(4))
PQty = CDbl(ar1.Fields(1))
Pwieght= CDbl(ar1.Fields(2))
wcost = CInt(whouse(j))

Select Case (wcost)
Case 12
TW_Cost = ((Pwieght*OQty) / 100) * 1.75       ' Jacob Fleishman
Case 14
If (cnt = 0) Then                       'add Query to count records in this Order check for 0
TW_Cost = 300 + (9 * (OQty/PQty))   'Ocasa Logistics
Else
    TW_Cost = (9 * (OQty/PQty) )
End If
Case 11
If (cnt = 0) Then
TW_Cost = 50 + 10 * (OQty/PQty)     ' Americold $50 per load as 5/4/09
Else
    TW_Cost = 10 * (OQty/PQty)
End If
Case 15
If (cnt = 0) Then
TW_Cost = 50 + 10 * (OQty/PQty)     ' Americold $50 per load as 5/4/09
Else
    TW_Cost = 10 * (OQty/PQty)
End If
Case 5
TW_Cost = (19 * (OQty/PQty))  + 3.15  ' US Cold Storage 3.15 cost of item retrival at ware house
Case 2
TW_Cost = 12.5 * (OQty/PQty)          ' US Cold Storage 30 days
Case 9
TW_Cost = 18.5 * (OQty/PQty)          ' Colombia Cold & Dry Storage
Case 10
TW_Cost = 11.6 * (OQty/PQty)          ' Tyler Distribution Center
Case 3
TW_Cost = 16 *  (OQty/PQty)           ' Integrity WareHouse
                                        
Case 16
TW_Cost = 13 * (OQty/PQty) 

Case 17                                 ' L.G Lables mostly lableing purposes $60 in & $60 out
If (cnt = 0) Then
TW_Cost = 120
End If

End Select

cnt = cnt +1
      

       Values = Values & "," & TW_Cost

'wscript.echo "STOP" for Debugging 
    	
            sql_insert = "Insert into " & tmptable & " (" & fields & ") values" & " (" & values & ")"
	  
             Econnectiontodatabase.execute(sql_insert)

         gl8.MoveNext
    loop
j=j+1    
Loop

'Insert into Header Query
xcommand3 = "Insert into AccessData.dbo.SalesOrder (SalesOrder.SO_Number,SalesOrder.Customer_ID, " &_
            "SalesOrder.Order_Date,SalesOrder.Delivery_Date, SalesOrder.WareHouseCode,SalesOrder.EntryDate) " &_
            "Select distinct '"& ET &"' + Right(PurchaseOrderNumber,7),'EAGLE', PurchaseOrderDate, RequiredExpireDate, WarehouseCode,  " &_
            "DATEADD(dd, 0, DATEDIFF(dd, 0,Getdate())) from dbo.importSO_SalesOrderDeatil" 


'Insert into Details
  xcommand4 = "; with Detail as (" &_ 
  "select ItemNumber, ProductNumber, P_END_Date, VendorNo, Case_Weight, Case_CF,Quantity, Description1, List, Contract, ROW_NUMBER() OVER( PARTITION BY P1.PRODUCT_ID Order by P3.Cdate desc) rn " &_
  "from AccessData.dbo.Product_Detail p2 inner Join AccessData.dbo.Product p1 on p1.Product_ID = p2.Product_ID inner join AccessData.dbo.Pricing p3 on p2.Product_Detail_ID = p3.Product_Detail_ID )/*and p3.Cdate = DATEADD(Month, DATEDIFF(Month, 0,getDate()), 0) )*/ " &_
  "insert into AccessData.dbo.SalesOrderDetail(SO_Number,Item_Number,Product_Number,Vendor_Number,Quantity,Sales_Price,Profit,T_List_Cost,T_Con_Cost,NN_Cost,Adj_Cost,Spoils_Allowance,Term_Disc,T_SaleAmt,T_Weight,T_CubicFeet,Pallet_Count,Description,OrderList,OrderContract,OrderUnit,TW_Cost) " &_
  "select '"& ET &"' + Right(ip.PurchaseOrderNumber,7), " &_
          "ip.ItemCode, " &_
          "Detail.ProductNumber, " &_
          "Detail.VendorNo, " &_
          "ip.QtyOrdered, " &_
          "Round(ip.UnitCost,2) as Sales_Price, " &_
          "Round(((ip.UnitCost * ip.QtyOrdered)-(ip.QtyOrdered * Detail.Contract)*.98+.01)/(.01+(Round(ip.UnitCost,2) * ip.QtyOrdered)),4) as Profit, " &_
          "Round((ip.QtyOrdered * Detail.List),2) as  T_List_Cost, " &_
          "Round((ip.QtyOrdered * Detail.Contract),2) as T_Contract_Cost, " &_
          "Round((ip.QtyOrdered * Detail.Contract)*.98,2) as NNCost, " &_
          "Round((ip.QtyOrdered * Detail.List)-(ip.QtyOrdered * Detail.Contract),2) as Adj_Cost, " &_
          "Case Left(Itemcode,2) " &_
              "when '37' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "when '44' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "when '45' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "else Null " &_
              "END as Spoils_Allowance, " &_
          "Round((ip.QtyOrdered * Detail.List)*.02,2) as Term_Disc, " &_
          "Round((Round(ip.UnitCost,2) * ip.QtyOrdered),2) as T_SaleAmt, " &_
          "(Detail.Case_Weight * ip.QtyOrdered) as T_weight, " &_
          "(Detail.Case_CF * ip.QtyOrdered)as T_CubicFeet, " &_
          "Round(((ip.QtyOrdered +.01)/( Detail.Quantity +.01)),1) as Pallet_Count, " &_
          "Detail.Description1, " &_
          "Detail.List, " &_
          "Detail.Contract, " &_
          "Detail.Quantity as OrderUnit,  " &_
          "ip.TW_Cost " &_
  "from dbo.importSO_SalesOrderDeatil ip inner join AccessData.dbo.Product P on ip.ItemCode = p.ItemNumber inner join Detail on ip.ItemCode = Detail.ItemNumber " &_
  "inner join AccessData.dbo.Product_Detail pd on P.PRODUCT_ID = pd.PRODUCT_ID " &_
  "where Detail.rn=1 " &_
  "and pd.P_END_Date > Getdate()" &_
  "Order by LineIndex "
    
  Econnectiontodatabase.execute(xcommand3)  
  Econnectiontodatabase.execute(xcommand4)
      
    EConnectiontodatabase.Close()
    set EConnectiontodatabase = nothing
    
    AConnectiontodatabase2.Close()
    set AConnectiontodatabase2 = nothing
        

    wscript.echo "Complete. Please check Access for the data. " & j & " Record(s) were entered into " & table & VbCrLf

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

carsRSTCommented:
Few options...

1.  You can re-write your code to VB.NET under script task (in 2005 SSIS you can use this exact code in ActiveX script)
2.  Create with SSIS tasks
     a.  create the connections to your "From" and "To" databases
     b.  Add a data flow task
     c.  under data flow task (double click it), create a data flow source and data flow destination
     d.  Source is the data you're pulling from and th destination will be where it's going to.
   
0
Leo TorresSQL DeveloperAuthor Commented:

Step 1
You mean inside the Script task with in SSIS??

Step 2

If you look at the xcommand4 it has a lot of transformation and logic what tool do I use to replicate that effect

An also this process is entering Header information to one table so there's a valid entry into the detail table on that order
How would I account for that process??
0
carsRSTCommented:
This is sort of hard to explain a complete solution for your code.

Under the "Data Flow"...

In between your Source and Destination, you can use "Data Flow Transformations" to do all the tasks in your code.   You'll just have to break the logic down in to pieces and re-create the steps.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Leo TorresSQL DeveloperAuthor Commented:
OK, So would this be 1 or 2 separate jobs

My biggest 2 hurdles are doing the 2 tables header and Detail table

then doing the xcommand4 insert that has many calculations

Yes I know I can use the data flow and I can connect a source and deliver it to an other destination...

my thing I am not sure which modules i need to use in between to get my desired result.

This may help....
Program logic :
1. Connect to Data Sources with the connection strings

2. Ask user to enter desired orders to be retrieved from source (would not know what module to use for user inter action)

3. Ask the user for a warehouse location (again needs user input)

4. Collect data and keep in memory (Line 79)

5. Convert Item number into our Item Number (this may be a lookup) (line 120)

6.  Now insert into SQL table and add Warehouse (code starts line 142)

7. Calculate Warehouse cost based on where house, count number of pallets in the order, calculate the weight of the Order (Starts line 154)

8. Determine the cost of warehouse based on warehouse entered by user(line 159) What module would do this

9. Insert data from temp table into header -> xcomand3 (Line 218)

10 Insert data and calculations into detail table ->xcommand4 (Starts Line 225)

This is what the program does what modules can I used to match these processes...

A good start will be if I knew what modules would help me achieve these task...

Thanks for your help!!
0
carsRSTCommented:
1. Connect to Data Sources with the connection strings
USE CONNECTION MANAGERS



2. Ask user to enter desired orders to be retrieved from source (would not know what module to use for user inter action)
-I USE VARIABLES IF I NEED SOMETHING DYNANIC.  RIGHT CLICK IN YOUR CONTROL FLOW AND SELECT "VARIABLES" (YOU PUT SCOPE AND TYPES)
-THEN YOU CAN PULL IT FROM ANOTHER LOCATION (MAYBE ANOTHER TABLE) - USE THE "EXECUTE SQL" TASK, CHANGE THE RESULTSET, AND THEN MAP THE RESULT SET TO A VARIABLE.
-SOME VARIABLES MAP IN USING "EXPRESSIONS"


3. Ask the user for a warehouse location (again needs user input)
USE VARIABLES (SEE ABOVE)


4. Collect data and keep in memory (Line 79)
USE A DATAFLOW TO PULL THE DATA

5. Convert Item number into our Item Number (this may be a lookup) (line 120)
LOOKUP IS A GOOD CHOICE

6.  Now insert into SQL table and add Warehouse (code starts line 142)
IN DATAFLOW PUT TO DESTINATION

7. Calculate Warehouse cost based on where house, count number of pallets in the order, calculate the weight of the Order (Starts line 154)
-USE AGGREGATE AND ROW COUNT "DATA FLOW TRANFORMATION" TOOLS

8. Determine the cost of warehouse based on warehouse entered by user(line 159) What module would do this
-VARIABLES (FOR USER INPUT)
-MAYBE DO A LOOKUP TO GET THE COST OF THE WH ENTERED.


9. Insert data from temp table into header -> xcomand3 (Line 218)
-DESTINATION FLOW TASK


10 Insert data and calculations into detail table ->xcommand4 (Starts Line 225)
-MAYBE ADD ANOTHER DATA FLOW TASK.
-STARTING POINT WOULD BE WHERE YOU LEFT OFF ABOVE
-DESTINATION IS DETAIL TABLE
0
Leo TorresSQL DeveloperAuthor Commented:

2. There is no logic in place for system to just pick warehouse based on anything this is a user input..

3. Same
User needs interaction in Order to determine warehouse

8. is an issue because it depends on 3 but the calculation logic is in place once the where house is know. Still dont know what module will work here

10. Where would you have the next data flow in the same SSIS job or separate. If separate would I have to call it from the first job if so how??
 
0
Leo TorresSQL DeveloperAuthor Commented:
I dont see a variables option in the control flow. Are you sure its its in BIDS 2008.. I dont see it on the toolbox on the left hand side..
0
carsRSTCommented:
To get to the variables, just right click in the control flow area.  On the menu that pops up, you should see "Variables" in the menu options.
0
Leo TorresSQL DeveloperAuthor Commented:
I tried that when I right click on the control flow I see this.. I just get a message pop-up
as you can see on the pane on the left I Dont see variables..
SSIS.JPG
0
carsRSTCommented:
The other way to do it is to use the top menu:

SSIS-->Variables
0
Leo TorresSQL DeveloperAuthor Commented:
I a bit green on this.. I created a variable.. Dont have the slightest idea how to assign values to it.. any suggestions on your part or easy online tutorials that may help. Struggling to create something that would take user input.. Thanks!!
0
carsRSTCommented:
Do you have the ability to allow users to store inputs in an Excel sheet?

If so, they could modify the parameters in there.  You could then upload that file and use its values.

0
Leo TorresSQL DeveloperAuthor Commented:
Really?? Sound great

Does this excel file have to be in a Row x Column format

or

Could I have it in a form format and extract particular cells that contains values??

I would prefer second but this sounds good either way I go.. The first one would require a more advance user. I would think if they have to enter it in a row column format..
0
carsRSTCommented:
Either option is doable.

however, i would, for now, make it the row/column format.  Your second option would get in to scripting.

So just make a basic sheet with your values.  Then you would great a data flow and then add an Excel source.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
OK do you mean scripting inside or outside SSIS..

I have been able to do some outside inside different story!! Let me see if I can create one today!
0
carsRSTCommented:
Yeah, inside SSIS.  Just add the "script task"  - SSIS 2005 just uses VB.NET.  If you have 2008, you can use c# or VB.NET.  And version 2005 allows VBS type scripting under activeX script.
0
Leo TorresSQL DeveloperAuthor Commented:
Dont have time to test now. But I like the idea.. I will post once I try to actually implement
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.