Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DTS - loading files

Posted on 2006-06-08
40
Medium Priority
?
1,020 Views
Last Modified: 2013-11-30
I am loading a bunch of csv files daily via dts.  Each file has two attributes - Symbol and Weight.  
The table that these are loaded in has four attributes - BasketID, Symbol, Weight, Update

1) Symbol and Weight are a one-to-one in the data transformation.  meaning, Source.Symbol = Destination.Symbol.  and the same for Weight.
2) BasketID is inserted via a global variable which is based upon the name of the file being inserted. If ADRA_ETF_Composition.csv file is loaded, ADRA_basket is loaded into the BasketID field.
3) Update is simply a getdate() default.

In theory, everything works just fine.  My logging lists every file that is processed, and each file is moved to an archival directory when finished.
I have two tables in place.  The Composition table and the Composition2 table.  Compostion2 is the one I am loading via DTS.  
The other table is old and will be dropped/decommissioned once I have confirmed this new table is accurate -- or, being loaded just fine.

I have just now noticed that Symbol and Weight are being carried through every file that is being loaded.  
For example, ADRA_ETF_Composition.csv is the first file loaded, there are 50 Symbols and weights for BasketID ADRA_basket.  
The very same 50 symbols and weights are there for basketID ADRD_basket...and ADRE_basket, and for every basket in the table.  

It appears the only thing I am doing per file is the basketID and the Update (which is simply a getdate default).
Can anybody suggest for me how it is that I am locking myself into the Symbol and Weight values of the first file loaded, and then carrying them through for every subsequent file loaded?
0
Comment
Question by:dbaSQL
  • 24
  • 10
  • 6
40 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16862227
Can you post your code? I assume you're using the ActiveX transform?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16862316
yes, well, understand, i am not a DTS guru at all.  the basketID is done in an activeX transform, yes
but, symbol and weight, i've just got the transform options where Source Columns are col001 and col002, and Destiantion columns are symbol and weight.
so, if you dbl click the transform data task, and go into the Transformations tab, I show col001 and col002 to the left at Source, i show all columns to the right at Destination, there are two distinct arrows from left to right.  the basketID arrow properties will take you right into my ActiveX, the symbol/weight arrow just opens a 'Column Order' dialogue box which lists source and destination columns.
(there is now arrow on update, as that is a getdate() default and handled dynamically upon insertion)

possibly this is my error.  Do i need an activeX transform here for symbol and weight?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16862557
I've just tried to do this with the ActiveX below --- same thing is happening -- every basketID is holding the same symbols and weights.


'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
      DTSDestination("Symbol") = DTSSource("Col001")
      DTSDestination("Weight") = DTSSource("Col002")
      Main = DTSTransformStat_OK
End Function

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16862722
wow, you got me? It looks good to me... there isn't a trigger on the table or something like that?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16862745
am i making sense?  I'm walking through a hundred or so different files -- the first one has 50 records.  I am writing the same 50 records for every file loaded.  I am somehow addressing each file, though, as the basketID is loaded properly for each.  my basketid activeX is below - and, like i said, it's working, because i am getting distinctly different basketID's entered for each file processed.  I am simply carrying the same 50 values for symbol and weight - that are loaded for the first file - i am carrying them through to every other file being loaded.  


'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column

Function Main()

dim sStart
dim iBackSlashReverse
dim sAfterBackSlash
dim sFinal
dim iUnderscore
 
sStart =DTSGlobalVariables("gv_FileFullName").Value
 
iBackSlashReverse = instr(StrReverse(sStart),"\")
sAfterBackSlash = Right( sStart ,  iBackSlashReverse - 1 )
 
iUnderscore = instr(1,sAfterBackSlash,"_")
 
IF iUnderscore  > 0 THEN
 sFinal = LEFT(sAfterBackSlash,iUnderscore -1)
ELSE
 sFinal = sAfterBackSlash
END IF

DTSDestination("BasketID") = sFinal & "_basket"

     Main = DTSTransformStat_OK
End Function
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16862797
no trigger.  the table is pretty norm.  a getdate() default on 'Update' and a unique idx on BasketId, Symbol, Weight,Update
something in the dts package is not bringing the symbol and weight values in for each file.  it's simply re-using over and over what is loaded with the first file.
i don't know how this is happening.

what baffles me is i'm using this in my begin loop:  DTSPackageLog.WriteStringToLog DTSGlobalVariables("gv_FileFullName").Value
so each filename processed is written to the log, and, like i said, the basketID's are written properly for each file.
so what is locking me into the symbol/weight of the first file loaded?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16863232
You are not changing the Datasource of the connection, hence you are in fact importing the same file over and over again.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16863287
I didn't realize I needed to do that.  How would I change the datasource, ac?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16863423
For an example of looping and changing the datasource see the following link:
Looping, Importing and Archiving
http://www.sqldts.com/?246
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16864109
I've got to stop going to lunch...

Just like ac has stated... the looping example at sqldts is a very good example. I've been using a variation for a long time.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16867020
yes, i've gotten quite a bit from sqldts.com, i should have checked there -- it's an excellent resource.
let me see what i can do.  
i will let you know
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16869854
Me too, one of my favorite sites.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16869903
Well, guys, I'm a little puzzled and a bit embarrassed.  My activeX is actually from sqldts.com.  Possibly I'm just overlooking something.  Is it my begin loop or my loop around that I should be focusing on?  Or the gv definition ?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16870161
I thought I recognized the global variable naming convention... :-)

Without seeing the rest of the package it's hard to tell. It sounds like the ActiveX transform that sets the values isn't inside the loop? If that makes any sense. Because its not resetting the values.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16870405
here's my begin loop - would you maybe need to see the loop around or the gv definition?

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

OPTION EXPLICIT

Function Main()

      'Define our variables

      dim pkg
      dim  conTextFile
      dim stpEnterLoop
      dim stpFinished

      'Set their values

      set pkg = DTSGlobalVariables.Parent
      set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
      set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_7")
      set conTextFile = pkg.Connections("Text File (Source)")
      
      'We want to continue with the loop only of there are more than 1 text file in the directory.  If the function ShouldILoop
      'returns true then we disable the step that takes us out of the package and continue processing

      if ShouldILoop = True then
            stpEnterLoop.DisableStep = False
            stpFinished.DisableStep = True
            conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
            stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
            DTSPackageLog.WriteStringToLog DTSGlobalVariables("gv_FileFullName").Value
      else
            stpEnterLoop.DisableStep =True
            stpFinished.DisableStep = False
            stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
      End if

      Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

      dim fso
      dim fil      
      dim fold
      dim pkg
      dim counter
      
      set pkg = DTSGlobalVariables.Parent
      set fso = CREATEOBJECT("Scripting.FileSystemObject")
      
      set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

      counter = fold.files.count

      'So long as there is more than 1 file carry on

      if  counter >= 1  then

      for each fil in fold.Files
            DTSGlobalVariables("gv_FileFullName").Value = fil.path
            ShouldILoop = CBool(True)
      Next

      else
            ShouldILoop = CBool(False)
      End if

End Function
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16872217
any thoughts?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16872787
Are you deleting the file when you are done importing, prior to executing the above code?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16872872
After the file is loaded it is moved to an archived directory.  
when you say 'prior to executing the above code'  which code are you referring to, ac?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16873354
To put it in SQLDTS terms:  The "Loop Around" task must execute prior to the second (and subsequent) "Begin Loop" tasks are executed.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16873436
disconnected edit lists my tasks in this order:

activeX task 1 - check for files
activeX task 2 - yes
activeX task 3 - no
execSQLtask 1 - define table
activeX task4 - define GV's
activeX task 5 - bad directories
activeX task 6 - begin loop
activeX task 7 - finished
activeX task 8 - loop around
datapump task 1 - datapump


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16875108
Ok.  But is the Workflow set up like in the example on SQLDTS?  There example does work as advertised.  We have used it daily in many DTS packages.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16881620
well, it must not be, or I wouldn't be having this problem.  i'm just not finding it and i'm not sure where / what else to check. that's why i opened this inquiry
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16881650
Ok.  Assuming (but feel free to post a link to an image of the package) the workflow is correct, than post your ActiveX code for all the relevant tasks, namely:  begin loop, finished and loop around.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16884309
ac, i really do appreciate your assistance on this.  surely it is just a silly oversight of mine, but i am just not finding it.  i am not sure how to post a link to the image.  i will do a little research and see if i can figure that out real quick.  and i already posted my Begin Loop, here are the Finished and Loop Around.   please do let me know what you think

--Finished
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

OPTION EXPLICIT

Function Main()

      'MSGBOX "Package has Completed."
      Main = DTSTaskExecResult_Success
End Function


--Loop Around
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
OPTION EXPLICIT

Function Main()

      dim pkg
      dim stpbegin
      dim fso
      dim fil
      dim fold

      set pkg = DTSGlobalVariables.Parent
      set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_6")
      
      set fso = CREATEOBJECT("Scripting.FileSystemObject")

      ' The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting

      stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

      ' This is how we process through the other files.  We use FileSystemObject to move all the files to another directory.

      fso.MoveFile  DTSGlobalVariables("gv_FileFullName").Value,DTSGlobalVariables("gv_ArchiveLocation").Value

      Main = DTSTaskExecResult_Success

End Function


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16885177
While there are some cosmetic things that I would change, I am not seeing anything that would cause any problems.  Providing your workflow is set up the same as the SQLDTS example, I cannot think what could be causing the problem you are reporting.  You may have to debug it, by either
1. Placing STOP commands and relying on a good VBScript debugger.  If that is not available then
2. Use MsgBox commands to verify that values are what they are supposed to be.

Sorry I could not be more help.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16885294
oh boy, ac, no reason to apologize.  i am frustrated badly by this one. it just is not making sense.
i am very close to dropping the whole darn pkg and starting over
but again, no reason to apologize.  thank you for taking a look at my activeX, ac
i don't know the vbscript dbug side of things, i'll see if th msgBoxes give me any insight
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16885458
Get an account at Savefile.com (its free).

Upload your package so we can take a look at it first hand.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16885780
oh wonderful, i haven't heard of this savefile.com before.  very good.
here it is.  http://www.savefile.com/files/5196058
please advise if you are able.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16887479
any suggestions at all?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16887659
step 1: clean out directory local to sql server which just does a del /Q via stored procedure
step 2: copies all new .csv files local via bat file which just does a copy \\source\*.csv  \\destination\
step 3: invokes the dts pkg which is supposed to loop through the files just copied local

everything appears to work until i look at the data.  the first file loaded contains 50 records, all subsequent files that are loaded contain the very same 50 records.  the table has 4 fields;
the ID, which is generated based on the name of the file(s) being loaded
the [date], which is based on a getdate() default when the file(s) are loaded
symbol, is col002 in csv file(s)
weight, is col003 in csv file(s)

- the ID is the name of the file being inserted, and it is populated correctly for every file/every record loaded and it is done so via gv
- the symbol and weight fields are not carried through for each file loaded.  they are loaded for the first file and then the same values are carried through for all subsequent insertions.
- date is fine, as it's a getdate() default

I have checked every aspect of my activeX.  The majority of which I got from sqldts.com.  I simply am not finding my error.  Can anybody assist at all?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16890839
One possible reason is that your "Define Table" SQL Task is using a connection that is not shown on your package, namely "SQLServerDestination" and not the obvious "SQL Destination".  "SQLServerDestination" is using a totally different server (SQLSERVERCH03) to the one I suspect you are looking at: SQLSERVEREQ01.  Hence the nothing is happening to the table you are looking at.

You have many other connections that are not used and I would stronly urge you to lose them as they can cause this type of subtle error.  Here is how you delete them:
Removing unwanted or orphan connections from a package
http://www.sqldts.com/default.aspx?253
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16890942
ac, thank you.  i was looking at those extra cxns the other day, but unsure how to remove them.  i will do this now.  thank you
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16893428
well, ac, possibly you've got something there.  i dropped all those extra cxns, tried to run it but it failed.  had to recreate the text file connection.  re-ran it, it worked.  sort of.  i say 'sort of' because it looped through and loaded 38 files successfully.  The right data was loaded for each of the files.  BUT, then it failed with this:

Error creating datafile mapping:  The volume for a file has been externally altered so that the opened file is no longer valid.

I've never seen that before.  Have you?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16893557
aaaah, apparently this is happening if/when dts finds one of the files to be of zero byte size.  i need to maybe mod the activeX to go ahead and process if/when the filesystemobject is found empty, instead of aborting.   can anybody help me w/this?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16894019
Modify your function ShouldLoop as follows (untested):

Function ShouldILoop
Dim fso
Dim fil    
Dim fold

Set fso = CREATEOBJECT("Scripting.FileSystemObject")
Set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

ShouldILoop = False  '  Assume no files
For Each fil in fold.Files
      If fil.Size = 0 Then
            ' Either delete or move the file
            ' fso.DeleteFile fil.Path
            fso.MoveFile  fil.Path, DTSGlobalVariables("gv_ArchiveLocation").Value
      Else
            DTSGlobalVariables("gv_FileFullName").Value = fil.path
            ShouldLoop = True
            Exit For
      End If
Next

Set fso = Nothing

End Function
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16894048
>>i dropped all those extra cxns, tried to run it but it failed.  had to recreate the text file connection. <<
You should not have had to do that.  I can only assume you deleted the text file connection that was in use.

The point that you may have missed was that as it stood previously, all data was going to a totally different server to the one I suspect you intended.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16894131
yes, i did miss that one, ac.  thank you, though, for clarifying for me.
currently this is my shouldloop - how do i put fil.Size in there when i'm already doing the IF on the counter?


Function ShouldILoop

      dim fso
      dim fil      
      dim fold
      dim pkg
      dim counter

      
      set pkg = DTSGlobalVariables.Parent
      set fso = CREATEOBJECT("Scripting.FileSystemObject")
      
      set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

      counter = fold.files.count

      'So long as there is more than 1 file carry on

      if  counter >= 1  then

      for each fil in fold.Files
            DTSGlobalVariables("gv_FileFullName").Value = fil.path
            ShouldILoop = CBool(True)
      
      Next

      else
            ShouldILoop = CBool(False)
      End if
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16895337
ac, anybody?  i have tried several times to add it into my loop, it's failing repeatedly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16895364
shouldloop variable is undefined?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16895465
ok, ac, i think i've got it.  it works, anyway.  my shouldloop undefined was a type-o failure.
thank you so very much for your assistance, ac
one thing i've learned for sure....when all else fails, just re-write the thing
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question