September 11, 2009 at 7:10 am
Hi All
I have a problem 2 problems when migrating DTS2000 package with ActiveX script task to SSIS.
My first problem is the migration. My package contains 2 ActiveX script tasks:
1.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim oPackage
DTSGlobalVariables("path").value = "\\sql2000\Data\Files"
Set fso = CreateObject("Scripting.FileSystemObject")
set DTSGlobalVariables("fso").value = fso
Set f = fso.GetFolder(DTSGlobalVariables("path").value)
Set fc = f.Files
set oPackage = DTSGlobalVariables.parent
For Each f1 in fc
if mid(f1.name, 13, 3) = "003" then
oPackage.connections("text1").datasource = DTSGlobalVariables("pateka").value + "\" + f1.name 'set the connection
DTSGlobalVariables("fn").value = right(oPackage.connections("text1").datasource, 19)
'exit
end if
next
oPackage.connections("SQL1").datasource = "sql2000"
set fc = nothing
set f = nothing
set fso = nothing
if len(DTSGlobalVariables("fn").value) >0 then
Main = DTSTaskExecResult_Success
else
main = 8448
end if
End Function
2.
Function Main()
DTSGlobalVariables("fso").value.deleteFile(DTSGlobalVariables("path")+"\"+ DTSGlobalVariables("fn"))
Main = DTSTaskExecResult_Success
End Function
Also, depending from the exception i'm sending different emails. The messages are:
1.The data are inserted in the database
2.The file is in wrong format (wrong delimiter)
3.There is now any new file
4.The file cannot be deleted
The design of the package is in the attachment.
My second problem is how to call this SSIS from a stored procedure. Now the stored procedure looks like:
CREATE PROCEDURE [dbo].[sCDHV_IRHV] AS
--run the package on every 6-th in the month.
declare @d int
select @d = day(getdate())
if @d = 6
begin
declare @hr int, @olePKG int, @st varchar(100), @GlobalV_count1 varchar(100)
DECLARE @source varchar(8000)
DECLARE @description varchar(8000)
--once per month
EXEC @hr = sp_OACreate 'DTS.Package', @olePKG OUT
--sifrarnik za sopstvenost na nerezidenti
EXEC @hr = sp_OAMethod @olePKG, 'LoadFromSQLServer("SQL2000", , , 256, , , , "pkgMigration")', NULL --trusted connection
exec @hr = sp_OAMethod @olePkg, 'Execute'
EXEC @hr = sp_OADestroy @olePKG
end
GO
Can anyone help me, please?
Thank you
I'm new in SSIS, this is my first package
September 21, 2009 at 7:56 pm
Although SSIS will run an ActiveX script, the underlying data model has changed dramatically, so it won't be able to interpret things like the value of a DTS 2000 global variable. That sort of thing in a script must be re-written. Also, SSIS offers features built-in to do things like iterate through a list or invoke file system tasks to copy, move or delete files, things that had to be manually constructed in DTS. So, you may find that you don't even need a script task in SSIS. Also, although SSIS in SQL Server 2005 can run an ActiveX script, the 2008 version drops that ability altogether.
You'll want to read up on the DTS-SSIS migration wizard and the tools that MS provides for estimating probable success in using that automation to migrate a package. You may determine that it would be better to let the wizard run and then manually patch up what's needed to make the SSIS output work, or you may decide that it would be preferable to simply re-build in SSIS from the top.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply