Need help to set global variable value

Question

Need help to set global variable value

Sagar-636902

SSCommitted

Points: 1651
More actions
November 19, 2009 at 12:32 pm

#228038

Hi All,
I am new in SSIS. i was trying to migrate dts to ssis. in dts there is a script task which set the global variable with that fround in query. i was able to merge the dts tasks along with activex script. only thing i am stuck on was could not set the value in global variable. script is like below.. I have set the package variable for File name and file size as FileName, FileSize. Only the reason i need those are use in another script task so that i can insert the file record and size in database table. Any idea that would be so great Thanks.
sagar
Function Main()
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
sFolder ="C:\Documents and Settings\Sagar\Desktop\dbfl\App\TEST\Input\abc"
sInCompleteFolder ="C:\Documents and Settings\sagar\Desktop\dbfl\App\TEST\Incomplete"
sAppFileName = "abc.txt"
set fsoFolder = fso.GetFolder(sFolder)
Dim sSourceFile
Dim sDestinationFile
If FSO.FileExists(sFolder & sAppFileName) Then
FSO.DeleteFile sFolder & sAppFileName
END if
For Each fsoFile in fsoFolder.Files
' Get filenme
sSourceFile = sFolder & fsoFile.Name
sFileName = fsoFile.Name
'Get filesize
sFileSize = fsoFile.Size
'msgbox "SFileName " &sFileName
'msgbox "SFileSize " &sFileSize
sDestinatiionFile = sFolder & sAppFileName
'Renaming file
If FSO.FileExists(sSourceFile) Then
FSO.MoveFile sSourceFile,sDestinatiionFile
End If
GlobalVariables("FileName").Value = sFileName
GlobalVariables("FileSize").Value = sFileSize
'If the file loading fails,
If FSO.FileExists(sFolder & sAppFileName) Then
FSO.MoveFile sDestinatiionFile, sInCompleteFolder&sFileName
END IF
Next
' Clean Up
Set fso= Nothing
Main = DTSTaskExecResult_Success
End Function

Viewing 13 posts - 1 through 12 (of 12 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Ok, in SSIS the closest thing to global variables is variables defined at the package level, you can tell by looking at the variable list, and if the column Scope is the same as the package name then it is a package level variable. This is to distinguish from variables that can be defined in other containers, like the Sequence Container. With that said it looks like you are still using an ActiveX script to accomplish what you are trying to do.. I would change it over to a script task instead and the code will probably be more maintainable.

Also I have walked through the code and the only thing that the two "global" variables will be set to is the last file name processed.

So, instead of trying to re-invent your wheel I would ask what this task is supposed to do and what you really want to get out of it..

CEWII

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 2

Thank you,

Actually i found the way to get the file name in the folder now i need to find a file size of the file. basically i only need those variables are to log inside the database table for the record every time when it load. what i did now is use for each continer and loop all the file in the folder and get each file and load that in db. i was able to get the file name using file system task. and pass it through the loop now i don't know how to get file size using script task. then i can avoid the activex script task. can i get help on finding that. thanks

sagar

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Probably the easiest would be to use a Script task in the ForEach container that uses the system.io.fileinfo and the Length property.

Does this make sense or do I need to explain further?

CEWII

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 4

i tried but no luck.. i know in activex script task we can us FSO... but no idea i tried to find resource still can't... can you post short of example or something. like how it should be set to the variable. right now i have only one variable maping in for each container.. do i need another varable for size as well or we can pass that file name in some short of script and retrieve the size of that file.. if you could pass some short of script example that could be lotaaa help.

Thanks

again

sagar

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Take a look at this..

Rename it by taking the .txt off the end..

CEWII

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 6

Thanks a lot,

I was able to get both file name and file size but could not able to insert into database table

i was trying to use execute sql task and maping those variables. i got error i was using

INSERT INTO CBSS_Log

(FileName, FileSize, LoadDate)

VALUES (@FileInfo,@FileSize, getdate())

i could not find method to use sctript task. what could be the best method.. i know i sounds like easy task but i am just getting mess.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

In your exec sql task it isn't that easy.. Your query should be:

INSERT INTO CBSS_Log (FileName, FileSize, LoadDate) VALUES ( ?, ?, getdate() )

Then you go to Parameter Mapping and add 2 and set the source variables in the variable name column and set the data type, set parameter name to 0 and 1. Leave parameter size alone. what you are doing here is saying the first ? is to be filled in from the 0 variable name and the second ? is to be filled in from the 1 variable name..

CEWII

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 8

nice... it works with sql task, you know since it did not work then i used another activex script and it was working with that. now seems it works with sql task. i will avoid that activex script task which is more hassel to debug. thanks a lot. i have one question though. in sctipt task why we need to make that precompilescriptintobinarycode to false otherwise it shows error.. is there any reason? should we keep it false or true?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

I think it should always be true. If it is showing an error check the error output window, if it isn't showing then get it showing, it is your friend..

If you had to set it to false then you probably copy/pasted my script task. that is ok, but you need to open the script and then save it then ok out of the script task, when the precompile flag is true then what I just described compiles it..

CEWII

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 10

actually that was hotfix, i search for error and download hotfix now it is good. working like champ

Thanks for the help 🙂

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 11

hi elloit,

i have question if you have any suggestion. i had posted as well about this. when i rename and try to delete using file system it won't delete. what happen when i rename file using file system it looks like made read only. is there any different configuration i need?

every time when i think i am done i am getting stuck with little thing.

thanks

sagar

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

I believe the reason is that the file is still in "use", you may be done with it but the system isn't. I *think* this can be resolved by changing the RetainSameConnection on the connection manager to false. I'm not sure though..

CEWII