October 3, 2007 at 10:04 pm
I converted sql 2000 package to ssis. I had flat file to table and i had some activex script for formatting of columns in the dts. hence when i migrated to sql 2005 ssis those steps are converted to execute sql 2000 package task and when i try to execute the package from vba all other steps execute except these steps.
Can anybody can tell how to make this step run from VB or how to run the dtexec from VB.
thanks
Bhu
October 4, 2007 at 8:56 am
October 4, 2007 at 9:12 am
also confused - are you saying you're having trouble because the SSIS isn't executing those steps, or the SSIS step needs to call another SSIS package?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2007 at 12:34 pm
I did migrate from SQL 2000 DTS to SQL 2005 SSIS and the flat file to table (DTS part) got converted to Execute Sql 2000 package task.
I dont get any error. all other steps which has sql statement are executing but execept execute sql 2000 package task is alone not executing.
I am trying to execute from Visual Basic 6.0
Dim oPKG As dtslib.Application
Dim pkg As dtslib.Package
Set oPKG = New dtslib.Application
'oPKG.LoadPackage
Set pkg = New dtslib.Package
If oPKG.ExistsOnDtsServer("MSDB\test\SSIS2005VBA", "Test") Then
Set pkg = oPKG.LoadFromDtsServer("MSDB\test\SSIS2005VBA", "test", False, Nothing)
End If
pkg.Execute
October 4, 2007 at 2:05 pm
You may want to try to actually do those steps in SSIS instead of having SSIS call the dts package. This might solve the problem.
While SSIS will run SQL 2000 dts packages, it does not always behave exactly as you think it should.
October 5, 2007 at 8:18 am
carla
It runs fine when i execute from BIDS but when i trying call from visual basic those steps are not executing.
Can you tell me how to execute ssis package from visual basic where the package contains transformation from flat file to table?
October 5, 2007 at 12:07 pm
Sorry, I have worked in vba and in Viasual Studio (current version), but not in Visual Basic 6.0. However, the commands are the same as those that I used in vba.
What if you set it up as a job and then just started that job from vb instead of trying to run the package from vb? (I'm just brainstorming here...not throwing out tried and tested solutions).
There is a huge difference between SSIS packages and dts packages. DTS packages could run on any machine as long as you had dtsrun (etc) isntalled. SSIS packages (dtsx files) will not run on a machine that does not have BIDS installed. I don't know if you have BIDS installed on your SQL server where you are trying to execute the package.
Can you execute any SSIS package, perhaps one that does not have a call to execute a dts package?
October 5, 2007 at 1:01 pm
thanks for the information. I have BIDS and the package runs in BIDS. But when i call from VB or VBA data transformation (flat file to table) that step is alone not executing.
October 5, 2007 at 1:13 pm
October 8, 2007 at 7:50 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply