• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

running procedure in SSIS Script component

Hi,



I want to run a procedure in SSIS Script component by taking one input as parameter.

parameter is retrieved from file name .

The file name is  2006-06-07.txt

I want to run a procedure sending this date as parameter.
as
Exec pr_populatedata '2006-06-07'

please suggest me the best possible method to solve this.

I feel that I can retrieve the name from variables in script component.

If script component is better for this task..please tell me how I to connect to sql server in script component.


I am new to SSIS
0
mmonline
Asked:
mmonline
  • 5
  • 4
  • 3
  • +2
2 Solutions
 
nmcdermaidCommented:
You are most likely better off using a execute sql task.
See this link:
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx
Also see this section in the SQL Server Help:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bebb2e8c-0410-43b2-ac2f-6fc80c8f2e9e.htm
 
 
0
 
ThorSG1Commented:
Will the date parameter be changing?  Are you looking to dynamically set the parameter you are passing?
0
 
PedroCGDCommented:
Why you need to do that inside a script component?!
In my blog you have an example of a script source as source
Regards,
Pedro

www.pedrocgd.blogspot.com
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mmonlineAuthor Commented:
Hi Pedro,

I got the answer for this one.. thank you
0
 
PedroCGDCommented:
very good men! :-)
Cheers!
0
 
nmcdermaidCommented:
Do you want to enlighten us so other people can come accross this post and learn themselves?
0
 
mmonlineAuthor Commented:
Hi nmcdermaid,

I did not understand what you are telling me. anyway thank you for help..
0
 
nmcdermaidCommented:
What I am saying is, can you post your solution so that if anyone else comes accross this post in future they can use your solution.
0
 
mmonlineAuthor Commented:
yeah, here is my code

---------------------------


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

namespace ST_5cdb6851e0a848b3a7da89845a780931.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


//        ConnectionManager cm;
        String date;

        public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;

            SqlConnection conn = new SqlConnection("Data Source=VANS-PC\\SQLSERVER;Initial Catalog=BankDW;Integrated Security=SSPI;");
            //cm = Dts.Connections.Add("OLEDB");
            //conn.ConnectionString =
            SqlCommand sqlcmd = new SqlCommand();

            String filename = Dts.Variables["ExtractFederalDataFiles"].Value.ToString();

            String[] names = filename.Split('\\');

            date = names[names.Length - 1].Substring(0, names[names.Length - 1].Length - 4);
           
            //Date = Row.Date.ToString();

            //Row.Date.Trim();

            sqlcmd.Connection = conn;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "[pr_populateDateDimension]";

            SqlParameter p = new SqlParameter();
            p.ParameterName = "@datekey";
            p.SqlDbType = SqlDbType.Date;
            p.Value = date;

            sqlcmd.Parameters.Add(p);
           
            conn.Open();

            int row =   sqlcmd.ExecuteNonQuery();

            if (row > 1)
            { }
            else
            { }
            conn.Close();
        }
    }
}
0
 
PedroCGDCommented:
Just a comment...
You should re-use the connections in connection managers in spite of hardcoded it inside the script...
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
nmcdermaidCommented:
Another comment... you're looking at probably five lines of code inside an execute task component instead of how many you have there.
0
 
mmonlineAuthor Commented:
Hi Pedro, you are correct. Initially , I tried it. later I felt this is easy and I got the answer using this code...
anyway,thanks for help...
0
 
chokkaCommented:

Can you please show - How to place Connectionstring in the Connection manager -

I need to Call a Stored Procedure from SSIS - Script Component ..!

 public void TestSSIS_EE()
        {


            SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]);
            SqlCommand sqlcmd = new SqlCommand();

            sqlcmd.Connection = conn;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "[SSISEMAILALERT]";
            conn.Open();
            sqlcmd.ExecuteNonQuery();        
            conn.Close();
       
        }


0
 
nmcdermaidCommented:
I'll tell you again - use an execute sql task not a scrip task.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now