SolvedPrivate

Simple C# script in SQL Server 2008 SSIS Package  not writing to Variable

Posted on 2014-01-26
2
56 Views
Last Modified: 2016-02-10
Hi,
I am new to C# scripting and I have written the following script.
The purpose of the script is to write a 1 to a variable if a file is detected. If a file is detected, the Precedence constraint allows the next step ," Write Time to Table", in the process to execute.
This is not happening.
I have put in  some debug steps which show that the path exists but the variable value is not being changed to 1.
 a copy of the script is below,
I have added screengrabs of the the debug outputs ,
the scrpt task editor window,
the precedence constraint,
the  folder where the file is located,

Any guidance appreciated,
Thanks

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_a400c24e782648ea90176536249cd677.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

               public void Main()
        {
            // TODO: Add your code here
              String Filepath = Dts.Variables["User::DirectoryPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
            if (
                File.Exists(Filepath))
                {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            String FileExistsFlag = Dts.Variables["User::FileExistsFlg"].Value.ToString();
            MessageBox.Show(Filepath);//Show the folder path with file name
            MessageBox.Show(FileExistsFlag);//show the flag value, 1 for exists and 0 for not exists
           Dts.TaskResult = (int)ScriptResults.Success;  
        }
          
        }
    }

Open in new window

flow1.jpgflow-2.jpgprecedence-constraint.jpgscript-task-editor.jpgfolder.jpg
0
Comment
Question by:blossompark
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39810686
Your code looks good...

There should not be an issue at all
so, please start looking to the below stuff

1) Ensure that the file exists from the command prompt
     --> i.e. by doing dir c:\test\ProcessCompleted.txt
     --> Reason for doing this test will be sometimes we might encounter a space after the extension, just to rule that out.

2) Try to trim spaces for the FilePath variable in the C# code, before you can File.exists method , you can use trim() function
 after the below statement
String Filepath = Dts.Variables["User::DirectoryPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();

add the below statement
Filepath = Filepath.Trim();

3) If the file is good, then What I ask of you is to create a share folder in the C: or D: drive and give permission to everyone (both read  and write) access and put the file in this location and run the code again and see what's happening now
      --> reason: we want to ensure that permissions are not coming into play here

4) Next one will be, is your SSIS running on a different machine than the one that you are thinking off ..

In order to ensure that this is not the case, add the below statement to your script and check the computer name is same as the one that you are thinking where the file exists

MessageBox.Show(System.Environment.MachineName.ToString());
0
 

Author Closing Comment

by:blossompark
ID: 39811562
Hi Surrendra Ganti,
Thanks for your very informative comment, it is very useful to me as a beginner C# scripter !!
Thank you again

I used the following to resolve my issue.
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_a400c24e782648ea90176536249cd677.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

               public void Main()
        {
            // TODO: Add your code here
                 String[] DEPOSFiles = Directory.GetFiles(Dts.Variables["User::DirectoryPath"].Value.ToString());    

              if (
                  // File.Exists(Filepath))
                   (DEPOSFiles.Length != 0))
              {
                  Dts.Variables["User::FileExistsFlg"].Value = 1;
              }
              else
              {
                  Dts.Variables["User::FileExistsFlg"].Value = 0;
              }
            String FileExistsFlag = Dts.Variables["User::FileExistsFlg"].Value.ToString();
            //MessageBox.Show(Filepath);//Show the folder path with file name
            //MessageBox.Show(FileExistsFlag);//show the flag value, 1 for exists and 0 for not exists
           Dts.TaskResult = (int)ScriptResults.Success;  
        }
         
        }
    }
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now