Results 1 to 10 of 10

Thread: More DTS Help

  1. #1
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11

    Exclamation Unanswered: More DTS Help

    Hello,
    I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:
    Code:
    Function Main()
    'esemail is the validation function	
    if EsEmail(DTSSource("email")) then
    	DTSDestination("email") = DTSSource("email")
    else
    	'Here i need HELP!!!!!
    end if
    
    Main = DTSTransformStat_OK
    End Function
    So what should I put in the else statement to transfer the email to another destination table???

    Thanks,
    A.

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63

    Re: More DTS Help

    Hello,

    I think you work with the DataPumpTask, aren't you?
    If I'm right, then you have to change the destination table in this task when going through the ELSE-path.

    For details how to change the properties of a DataPumpTask refer to http://www.sqldts.com/default.aspx?213.

    Hope this helps otherwise give me a hint!
    Carsten


    Originally posted by ArgenSQL
    Hello,
    I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:
    Code:
    Function Main()
    'esemail is the validation function	
    if EsEmail(DTSSource("email")) then
    	DTSDestination("email") = DTSSource("email")
    else
    	'Here i need HELP!!!!!
    end if
    
    Main = DTSTransformStat_OK
    End Function
    So what should I put in the else statement to transfer the email to another destination table???

    Thanks,
    A.

  3. #3
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    Tambien te puede convenir hacer dos tareas
    Una para correos validos y la otra para los no.

    Suerte

  4. #4
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    Thanks CarstenK, I read that sample, it help a lot but I'm still having a problem I'm getting an error.
    This is the code I'm using:
    Code:
    Function Main()
    	Dim oPkg, oDataPump
    
    	Set oPkg = DTSGlobalVariables.Parent
    	Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask
    
    	if EsEmail(DTSSource("email")) then
    		
    		oDataPump.DestinationObjectName = "dbo.ValidEmail"
    		DTSDestination("email") = DTSSource("email")
    		
    	else
    		
    		oDataPump.DestinationObjectName = "dbo.NonValidEmail"
    		DTSDestination("email") = DTSSource("email")
    		
    	end if
    
    	Set oDataPump = Nothing
    	Set oPkg = Nothing
    
    	Main = DTSTransformStat_OK
    End Function
    but I'm getting this error "Task 'DTSStep_DTSDataPumpTask_1' not found"

    In the link you gave me they explain how to get the name, I did it but still the same error, and I'm sure the name is correct (copy & paste).

    Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column. I think there is where the script should go...
    well... if anyone can help me with this would be great!


    fhnth: hacer dos tareas no seria practico por que tendria que recorrer la tabla dos veces.

    Bye,
    A.

  5. #5
    Join Date
    Oct 2003
    Posts
    268

    Re: More DTS Help

    Originally posted by ArgenSQL
    Hello,
    I'm using a DTS to make two tables of a csv passed file, I can make the transformations from the file to one table, but not for two, what I do is check if an email is valid and if it is I want to put it in one table, and if it isn't in another. Here is the code I'm using for validating:
    Could you import to a staging table and use T-SQL to move the records into the correct destination table?

  6. #6
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11

    Re: More DTS Help

    Originally posted by RogerWilco
    Could you import to a staging table and use T-SQL to move the records into the correct destination table?
    I could but I don't want to do that, what I want to do is a dts package that do all the work in one (or few) steps. I hope someone with experience on that could help me...
    DTS have all the functionality in the world, I want to learn how to use it so I can do things in the best way and using all the technology I have access to.

    Bye,
    A.

  7. #7
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hello !

    After reading your reply a third time, I think I do know your problem. You said
    Maybe I'm doing something wrong, I'm using this ActiveX code in the "Transform Data Task Properties", "transformations" tab, in the arrow connecting the source column with the destination column.
    I think you should create an ActiveXTask, that contains your code and runs before the actual data import and adjusts the properties.

    I hope that brings you a step further!

    Greetings,
    Carsten

  8. #8
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    I am not sure whether this can be achieved in a single DTS step (one source (a .csv file) and 2 destinations (2 diff. tables).

    You can make a 2 step DTS package>
    1). 1st Step:
    source : .csv file
    dest : 1st table
    ActiveX :
    If email1 = email2
    Main = DTSTransformStat_OK
    else
    Main = DTSTransformStat_SkipRow
    end if

    2). 2nd Step:
    source : .csv file
    dest : 2nd table
    ActiveX :
    If email1 = email2
    Main = DTSTransformStat_SkipRow
    else
    Main = DTSTransformStat_OK
    end if

  9. #9
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi!

    My thoghts are going this way:

    Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.
    By taking this way, you can avoid processing an email twice!

    Carsten

  10. #10
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    Originally posted by CarstenK
    Create an ActiveX-Task which determines whether an email is valid or not and then adjusts the appropriate destination for the following import task.
    I can't make it work, I tryed everyway and still getting different errors, if I put the ActiveX code before the data pump, I don't get an error by detecting the task name, but I get an error because I ask for the email in the DTSsource and that doesn't happen until I get in the data part, so I can assign a different destination table but not commanded by the email type. I can't find a solution for this I search the web a lot but couldn't find a solution without passing two times trought the table, so I'm doing that I don't like it but I have to finish this anyway, if anyone finds the way to do this, please tell me.

    Bye, Thanks all for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •