August 31, 2011 at 4:36 pm
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.
August 31, 2011 at 4:46 pm
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/
August 31, 2011 at 5:05 pm
Not really, this issue is something different and I searched all ssis forums for an answer before posting. Thanks.
August 31, 2011 at 7:02 pm
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply