Insert Error using SSIS Script Task

Question

Post reply

Insert Error using SSIS Script Task

SJ-727436

Mr or Mrs. 500

Points: 547
More actions
August 12, 2009 at 2:20 pm

#209405

Hello,

I am using a SSIS Script Task in order to insert some values in the database. My code is like:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

namespace ST_e156ef78fcee40a985a4a559f030968c.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

public void Main()

{

Dts.Variables["User::Insert_Into_TableA"].Value = "Select state, city Into Table B From DBName.dbo." + Dts.Variables["DB_Table_XX"].Value;

//Dts.TaskResult = (int)ScriptResults.Success;

}

}

}

The code builds fine and when I execute the task from the SSIS package it executes successfully too but it does not insert anything into the table. So I am not sure where the error is?

Also, why do I need to store the SQL query in a variable? If I just try to write the SQL query by itself I get errors in the C# code. Should this variable's (Insert_Into_TableA ) value be updated? For me it does not!

Thanks

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

I don't see you execute the T-SQL code anywhere. All I see you doing is creating a variable initializing it with a T-SQL string. At some point you need to connect to the database and execute the T-SQL.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SJ-727436 Mr or Mrs. 500 Points: 547 More actions · Answer 2

Thanks Jack! I got that part right now. Was pretty dumb huh!!!

But now I am struggling with another error. The code is working fine for simple queries like insert and select. But when I am trying to use 2 different connections inside the query its failing. Below is a code sample:

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

namespace P1.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

public void Main()

{

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=DB1;Integrated Security=SSPI;");

string myInsertQuery = "Select Field1, Field2"

+ "INTO DB1.dbo." + Dts.Variables["Dynamic_Table1"].Value

+ "FROM DB2.dbo." + Dts.Variables["Dynamic_Table2"].Value

SqlCommand myCommand = new SqlCommand(myInsertQuery);

myCommand.Connection = myConnection;

myConnection.Open();

myCommand.ExecuteNonQuery();

myCommand.Connection.Close();

}

Please note that I am inserting into DB1 and selecting from DB2. I understand that I do not have the connection string for DB2 but I am not sure how to declare that since I can assign the Query to only 1 variable which in this case is the myConnection variable.Can I connect to two DB's in the same Connection String?

Thanks!

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

TO answer your question. I don't think you can do what you are trying. You could create a linked server on DB1 to DB2, but then you don't need SSIS.

I have to ask why you are doing this with a Script Task and not using a DataFlow with a Source and Destination? All I see is a simple insert so there is no need for scripting, based on what I see you doing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SJ-727436 Mr or Mrs. 500 Points: 547 More actions · Answer 4

Jack,

I cannot use the Execute SQL Task or Data Flow Task for that matter because:

1. If I create a Variable with the dynamic SQL in it it fails coz my query is more than 4000 chars. and it throws a TRUNCATE error.

2. If I use the Direct Input option or Build Query then that fails too because I am using a dynamic table name in the FROM clause and I know now that we cannot do that.

The query is a little more complicated than the sample query I have. I am using a CTE as an input along with other tables in the FROM clause. I dont want to use Linked Server coz in that case I will have to create one in Production too when I deploy.

Is there any other way I can do this?

Thanks!

SJ-727436 Mr or Mrs. 500 Points: 547 More actions · Answer 5

SJ-727436

Mr or Mrs. 500

Points: 547

August 13, 2009 at 10:28 am

#1038825

I got my answer. Following is the link to it : http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/479dd6f9-b929-4276-8b03-204427b21453...if it can helpsomebody in future.

Thanks!

Jack Corbett SSC Guru Points: 184388 More actions · Answer 6

Okay, thanks for the link, but now I have to ask, why use SSIS if the databases are on the same server? Why not straight T-SQL in a job or an SP?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SJ-727436 Mr or Mrs. 500 Points: 547 More actions · Answer 7

Because this is just 1 step of the bigger process. I am doing a lot of processing of the data which requires SSIS. I just couldnt figure out which task to use to perform this particular query. And as I mentioned in my previous post using Execute TSQL Task wasn't an option for me since this query has variables in it and neither was Execute SQL Task as it was more than 4000 chars.

Thanks!

llkjhg833 Valued Member Points: 68 More actions · Answer 8

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

namespace P1.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

public void Main()

{

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=DB1;Integrated Security=SSPI;");

string myInsertQuery = "Select Field1, Field2"

+ "INTO DB1.dbo." + Dts.Variables["Dynamic_Table1"].Value

+ "FROM DB2.dbo." + Dts.Variables["Dynamic_Table2"].Value

SqlCommand myCommand = new SqlCommand(myInsertQuery);

myCommand.Connection = myConnection;

myConnection.Open();

myCommand.ExecuteNonQuery();

myCommand.Connection.Close();

}

Acai Berry [/url]