ActiveX Script Task

Question

ActiveX Script Task

Emma-239540

SSC Enthusiast

Points: 162
More actions
May 10, 2010 at 1:01 pm

#220900

Hi,
I am converting the ActiveX Script task from DTS 2000 to SSIS 2005.
please look at my StrConn below , if if use the data source as DEV02 the script work fine, however,
if i point the server to the production server (Data Source=PROD02) , the script does not work. and error message is confusing. it says " [ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x047866EC. "
Appreciate your helps. Thanks.
Function Main()
Dim oFSO, oFolder, oFiles, sPath
Dim oPkg, sFoldername, sFilenameOrg, sPkg
Dim oTextSTream, sHP, sType
Dim oConn, StrConn, SQL, rs, NewCompany, NewProduct, sFilename
sHP = "LACARE"
sType = "All"
sPath = "\\FILESERVER\SHARE\finance\"
' StrConn = "Provider=SQLOLEDB;Data Source=DEV02;Initial Catalog=Finance;uid=useraccess;pwd=123" ' work with this strConn
StrConn = "Provider=SQLOLEDB;Data Source=PROD02;Initial Catalog=finance_Staging;uid=useraccess;pwd=123" ' not working with this.
Set oConn = CreateObject("ADODB.Connection")
oConn.Open StrConn
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
Set oFiles = oFolder.Files
If oFiles.Count > 0 Then
For Each File In oFiles
sFilenameOrg = File.Name
sFile = sPath & File.Name
iTotalLine = UBound(Split(oFSO.OpenTextFile(sFile).ReadAll, vbLf)) - 1 ' header line
SQL = " p_FileImportInfos_Insert '" & sHP & "','" & sFilenameOrg & "','" & sType & "'," & iTotalLine
oConn.Execute (SQL)
Next
oConn.Close
Set oConn = Nothing
End If
Main = DTSTaskExecResult_Success
End Function

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

By the look of your code, if you really want to convert properly to SSIS, you can throw all of this code in the bin (metaphorically, of course) and use a couple of connections and a simple dataflow task.

I'm afraid I cannot help with your error - I no longer use ActiveX tasks.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Emma-239540 SSC Enthusiast Points: 162 More actions · Answer 2

hi,

i found out the problem and got it fixed

However, i don't really like this approach.

All i wanted to do is looping through a folder that has some files in it and

count the record of each file , get the file name and

save the info (file name, number of record count of each file)

into a table in SQL server. If you can show me how to do it properly in SSIS not using the ActiveX script task, i would be very appreciative.

Thanks.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Here it is in outline - the detail will take quite a bit of working out as you are new though:

A Foreach loop can read through all of the files in a folder (and include any subfolders, if required). It can also select only a subset of files (eg *.CSV) within the same structure.

You'll need to create a flat file connection. We'll use this connection for each of the files you read (using an Expression to set the connection source). Read every record as a single field, as you are not interested in the data, only the row count.

Create package-level variables to hold the file names and row counts.

A dataflow task will read the lines from the source file, count them and write the count to a SQL Server table. Create a flat file source which uses your (dynamic) file connection and send it to a Row Count transformation. Add the variable to the 'Variable' property of the Row Count transformation - so that the variable will be set for every file which is processed.

From the row count transform, you'll need an OLE DB Command which will perform the INSERT for you.

Job done! 🙂

I recommend that you get it working for a single file first and then add in the FOREACH functionality.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Emma-239540 SSC Enthusiast Points: 162 More actions · Answer 4

Thank So much for the outline. I'll try to do it with a single file first as you suggested.

Emma.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

No problem. Have fun learning and post back with any specific problems you have along the way.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin