SSIS Package Execution

Question

SSIS Package Execution

sowmyapasala5

SSC-Addicted

Points: 475
More actions
October 14, 2015 at 4:03 pm

#308997

Hi everyone,
I am new to this subject (MSBI-SSIS).I have a question related to SSIS package.
I am trying to execute the SSIS package with the script task, but due to the error I am unable to execute it successfully.
Here is the error:
DTS Script Task has encountered an exception in the user code
Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Can anyone help me to get rid of this error
Thanks,
Sowmya

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

My first thought was that you were trying to call an external DLL. Could you verify that that there aren't external DLLs that it wants to use?

CEWII

sowmyapasala5 SSC-Addicted Points: 475 More actions · Answer 2

Thanks Elliott Whitlow.

Well I am trying to load a folder which contains same structured file (same column count, order and information) in three different format (.xls, .xlsx & .txt). And created a solution (single package) to load these three different format file into single target SQL server table.

So I used a Foreach loop container which has Script task. And I couldn't find solution.

I don't know how to verify the DLL. Can you explain me in detail?

sowmyapasala5 SSC-Addicted Points: 475 More actions · Answer 3

I used this script for declaring the variables:

public void Main()

{

// TODO: Add your code here

string filepath = Dts.Variables["user::var_file_path"].Value.ToString();

int filenamelen = filepath.Length;

string Ext = filepath.Substring(filenamelen - 4, 4);

string T = ".txt";

string X = ".xls";

if (Ext == T)

Dts.Variables["user::var_flag"].Value = 0;

else if (Ext == X)

Dts.Variables["user::var_flag"].Value = 1;

Dts.TaskResult = (int)ScriptResults.Success;

}

Attached is the error_Screenshot for review

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

sowmyapasala5 (10/14/2015)
Thanks Elliott Whitlow.
Well I am trying to load a folder which contains same structured file (same column count, order and information) in three different format (.xls, .xlsx & .txt). And created a solution (single package) to load these three different format file into single target SQL server table.
So I used a Foreach loop container which has Script task. And I couldn't find solution.
I don't know how to verify the DLL. Can you explain me in detail?

The script task which doesn't have a referenced external assembly would be a different issue. So if you didn't add a reference to a DLL then you almost certainly don't have that problem.

I am confused though, you have a folder that contains the same data in the same format in different containers. And you are trying to load them. WHY? What is the end game? Why not choose one and load it in whatever is the easier format? OR do these files contain different data? If so I would probably do it a little differently and only use the script task to figure out the extension so we could send it down a TXT path or an Excel path.

CEWII

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

Ok, just a moment, I didn't have the script and screen shot when I was typing that..

CEWII

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

First, I'm not sure you need "user::" in the variable name. I don't usually include it. I would add a breakpoint at:

string filepath = Dts.Variables["user::var_file_path"].Value.ToString();

And then step through the code till it errored, that way we know which statement is causing the problem.

CEWII

sowmyapasala5 SSC-Addicted Points: 475 More actions · Answer 7

Thanks for the suggestion elliott

I'll try adding breakpoint at string filepath = Dts.Variables["user::var_file_path"].Value.ToString();

I am extracting the data(same or different) and loading it to SQL server table for the sake of practice. I'm just learning the subject and I'm a beginner :laugh:

sirkinghorse SSC-Addicted Points: 457 More actions · Answer 8

Elliott Whitlow - Wednesday, October 14, 2015 5:19 PM
sowmyapasala5 (10/14/2015)
Thanks Elliott Whitlow.Well I am trying to load a folder which contains same structured file (same column count, order and information) in three different format (.xls, .xlsx & .txt). And created a solution (single package) to load these three different format file into single target SQL server table.So I used a Foreach loop container which has Script task. And I couldn't find solution.I don't know how to verify the DLL. Can you explain me in detail?
The script task which doesn't have a referenced external assembly would be a different issue. So if you didn't add a reference to a DLL then you almost certainly don't have that problem.I am confused though, you have a folder that contains the same data in the same format in different containers. And you are trying to load them. WHY? What is the end game? Why not choose one and load it in whatever is the easier format? OR do these files contain different data? If so I would probably do it a little differently and only use the script task to figure out the extension so we could send it down a TXT path or an Excel path.CEWII

Hi,

How do we add a reference to a DLL, becuase I'm having the same error