Geek asks and answers

SSIS file system error: the type of the value assigned to the variable ... differs from the current type of the variable

OK ... I have a relatively simple SSIS package (DTSDesigner100, Visual Studio 2008 Version 9.0.30729.4462 QFE, .NET Framework 3.5 SP1). At the end of the SSIS control flow for the package is the File System Task , which is the Rename File operation .

The properties of this file system task are as follows:

  • IsDetinationPathVariable: True
  • DestinationVariable: User :: OutputFileName
  • OverwriteDestination: True
  • Description: File System Task
  • Operation: rename file
  • IsSourcePathVariable: False
  • SourceConnection: Assign Copy of Excel Template

There are no specific expressions. As you can see, I have an assignment assigned as a variable User :: OutputFileName. So let's look at this variable ...

  • Name: OutputFileName
  • Scope: Package
  • Data Type (ValueType): String
  • Value:
  • Namespace: User
  • EvaluateAsExpression: True
  • ReadOnly: False
  • Expression: (see below)

.

"\\\\SERVER\\Folder\\" + "MyAwesomeExcelWorkbook_"
+ (DT_WSTR,4)DATEPART("year", GETDATE())+ "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mm", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("dd", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)
+ ".xlsx"

So, I can successfully evaluate this expression. Each time a package starts (and fails), its value is the value of the evaluated expression, as you would expect.

When I run the package, this is what happens ...

  • The previous steps are successful.
  • The file system starts and the file is renamed successfully.
  • However, the package now fails because the file system problem failed:

.

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::OutputFileName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Task failed: Rename Excel Workbook
Error: 0xC0019001 at Rename Excel Workbook: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.

-, . DT_WSTR, 512, , - .

, , ? .

+3
5

, , .
, , .

EDIT: , - ? , , SSIS, Denali CTP. , , hunky-dory , , ? , , . ?

+2

. "Execute SQL Task" , SSIS. "varchar (max)", , "".

, varchar (512) "VOILA"... . ... varchar (8000) nvarchar (4000)... SSIS no likey varchar (max).

+4

, "" SSIS. . "" " ", , - - OutputFileName.

Show All Variables

SSIS, - , , , - , :

Multiple Variables of the same name

, , Andy

0

, , , , - . SQL, .

, .

, SQL, .

. SQL COL2 :

SELECT COL1, COL2 FROM Table WHERE COL2 = ? OR ? = ''

()

SELECT COL1, COL2 FROM Table WHERE COL2 = ? OR ? = N''

:

SELECT COL1, COL2 FROM Table WHERE COL2 = ?

SSIS.

  • It is not possible to use parameters in UDF or any query of significant complexity.
  • Need to look for esoteric parameter codes depending on the driver (OLEDB / ADO.Net / ODBC)
  • From the above error it is seen that using a field for a parameter somehow affects metatada on the field displayed from the request.
0
source

I get the following exception. I solved it. I will explain the question and the steps that I took to solve the problem.

    **Error Message :** The type of the value being assigned to 
variable differs from the current variable type..



              **When :** Executing SSIS package. SSIS package is calling batch script. 
        Batch script is calling Python script. SSIS package is expecting output from 
    batch file. After executing the batch file and python script output 
should be delivered to SSIS package.  But Here I am getting the exception.

    **Python Script writing output into stdout :** 
    //Python code start
    print >>sys.stdout, "output" 
    //Python code End
    So here the output is written to standard out.

        **Issue :**  When I am calling the batch file, I am writing the log 
into one log file.
    Please check the following batch script.
    //Batch File script start
    C:\Python25\python.exe GetValFromPYthonSCript.py > scriptLog.txt  
    //Batch File script End
        So what happend is, the output is written in the log.But again 
I tried to read the output using the following line in SSIS.
    //SSIS coding start
    Dim objProcess As Process
    objProcess = New Process()
    objProcess.StartInfo.FileName = "batchFile location"
    objProcess.start()
    System.Windows.Forms.MessageBox.Show(objProcess.StandardOutput.ReadLine())
    //SSIS coding End

    So when I am trying to display I am getting the above exception.

    **Fix** : Just I have removed the log. I did not write the output into the log file.
    C:\Python25\python.exe GetValFromPYthonSCript.py
    Now I am getting the output properly.
0
source

More articles:


All Articles