Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS ActiveX to SSIS 2008 ActiveX Script Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 4:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 4:37 PM
Points: 33, Visits: 151
Hi,

Im trying to create an MS Access database (.mdb ) on the fly with the following script in SSIS(2008) ActiveX Script task but DTSGlobalVariables.Parent.Connections is not supported need help with changing to an equivalent in SSIS 2008 or something that works. This script works in DTS packages.

Option Explicit

Sub Main()

Dim sFilename
Dim sConnectionString
Dim oCatalog
Dim oConn
Dim oFSO

' Derive new filename - X:\Source_FST\Export_yyyymmdd.mdb
sFilename = DTSGlobalVariables("Source_FST").Value & "Export_" & Year(Date) &_
Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & ".mdb"

' Check for existing instance of file and delete
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFilename) Then
oFSO.DeleteFile sFilename
End If
Set oFSO = Nothing

' Create empty MDB
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilename
Set oCatalog = CreateObject("ADOX.Catalog")
oCatalog.Create sConnectionString
Set oCatalog = Nothing

' Get reference to Access Connection and set new filename
Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Access")
oConn.DataSource = sFileName
Set oConn = Nothing


Main = DTSTaskExecResult_Success
End Sub


Thank you.
Post #1168424
Posted Wednesday, August 31, 2011 4:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
Duplicate post.

http://www.sqlservercentral.com/Forums/Topic1167066-148-1.aspx


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1168427
Posted Wednesday, August 31, 2011 5:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 4:37 PM
Points: 33, Visits: 151
Not really, this issue is something different and I searched all ssis forums for an answer before posting. Thanks.
Post #1168434
Posted Wednesday, August 31, 2011 7:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
harry.sh16 (8/31/2011)
Not really, this issue is something different and I searched all ssis forums for an answer before posting. Thanks.


You should probably post in the SSIS Forum.

Do either of these articles help?

http://www.freevbcode.com/ShowCode.asp?ID=5797

http://www.bigresource.com/MS_SQL-How-to-create-an-Access-database-from-ssis-w4NEiUHv.html


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1168456
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse