DTS Conversion Error

  • Thanks for the reply........... but my problem is that it is not able to identify the file name and location. all paths are correct....but throwing error "Error Retrieving file name for component with error code xyz"

    I even tried hardcoding the values.... but not worked.....

  • Mind sharing your new SSIS scripting task ?

  • '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    Dim fso

    set fso = CREATEOBJECT("Scripting.FileSystemObject")

    'Creating Log File and assigning the log file to gv_LogFileName

    sFilename = DTSGlobalVariables("gv_LogFileLocation") & "d_ccrs_clean_" & Right(Year(Now()), 4)

    If Month(Now()) < 10 Then sFilename = sFilename & _

    "0" & Month(Now()) Else sFilename = sFilename & Month(Now())

    If Day(Now()) < 10 Then sFilename = sFilename & _

    "0" & Day(Now()) Else sFilename = sFilename & Day(Now())

    DTSGlobalVariables("gv_LogFileName").Value =sFilename & ".log"

    Dim conTextFile

    set conTextFile = pkg.Connections("Microsoft Excel 97-2000")

    ' We want to continue with the loop only if there is

    ' one or more Excel 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

    conTextFile.DataSource = DTSGlobalVariables("gv_LogFileName").Value

    Set Ofwrite = fso.OpenTextFile(DTSGlobalVariables("gv_LogFileName"), 8, True)

    Ofwrite.WriteLine("====================================================================================================================================================================================" )

    Ofwrite.WriteLine("Clean Job Started at : " & now() )

    Ofwrite.WriteLine("====================================================================================================================================================================================" )

    Ofwrite.Close

    SET Ofwrite = NOTHING

    SET fso = NOTHING

    Main = DTSTaskExecResult_Success

    End Function

    Even if i am giving

    file = "D:\Folder\test.xls" instead of DTSGlobalVariable(gv_Logilename")

    still not working 🙁

    Please help........ I had spent 2 days in this

  • Lemme get this straight...

    You are using a active script task in SSIS?

    Why not go for a scripting task if you are looking at reworking this into SSIS?

  • They want with minimum changes.............. any way I'll try that also.

    But can u tell me that if My system is not having Microsoft Excel installed...............will any package work which is doing data tranfer from excel file to database table?

    Thanks

  • As long as the driver is installed, it shouldnt be a problem.

    One question, SSIS is a complete revamp of DTS, so minimal changes is an understatement.

    Why not use the execute DTS 2000 process task as an interim measure to buy you some time?

  • yes.......thats a good idea.............

    Hey....... thanks for u'r continously help and reply...............thanks a ton

    🙂

  • Hi All,

    I found the root cause of error, thought to share ..........

    It is like DTS works irrespective of metadata. Column dataset and excel field Dataset mismatch is acceptable. But SSIs is particular about data type. So the Excel which I was using is not compatible with Database.

    Solution is:

    either change data base or file.

    Thanks every one for help........hopefully now I'll also be able to provide solutions about SSIS 😀

    As I had searched almost google complete for this error

  • DTS Object Model is not supported in SSIS ActiveX script Task.

    The best approach is use Precedence constraint with expression to Enable/Disable Task based on certain condition in SSIS.

    If you want to know please check the following links.

    http://www.dtsxchange.com/help/dtsxchange/dtsxchange.faq.asp

    Also download and try DTS xChange to get highest quality Migration with best practices applied to converted package

    Click here to learn more about DTS xChange

    Click here to see differences between MS Wizard and DTS xChange

  • Try to use a freebie\trial tool like DTSXchange by Pragmatic, google it.

    It works well and has lots of logging and profiling.

    GL.

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply