October 12, 2016 at 8:41 am
I am trying to insert some values into destination table called Mytable - In the below code where I need to pass values ?
Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand(“INSERT INTO MyTable(Col1, Col2) ” & _
“VALUES(@Value1, @Value2)“, sqlConn)
sqlParam = New SqlParameter(“@Value1“, SqlDbType.Int)
sqlCmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter(“@Value2“, SqlDbType.Int)
sqlCmd.Parameters.Add(sqlParam)
End Sub
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
With sqlCmd
.Parameters(“@Value1“).Value = Row.ID
.Parameters(“@Value2“).Value = Row.Count
.ExecuteNonQuery()
October 12, 2016 at 9:12 am
you could do it as a stored procedure instead of a hard coded SQL command, but if you don't like that solution how about:
public static SqlDataAdapter InsertSQL()
{
SqlDataAdapter adapter = new SqlDataAdapter();
command = new SqlCommand(
"INSERT INTO MyTable(Col1, Col2) ” +
“VALUES(@Value1, @Value2)“, sqlConn);
// Add the parameters for the InsertCommand.
command.Parameters.Add("@Value1", SqlDbType.Int);
command.Parameters.Add("@Value2", SqlDbType.Int);
adapter.InsertCommand = command;
}
Your code looks like VB, the code I posted above is C#, but both are .NET so it should just be minor syntax changes to get that to work.
failing that, could you post the error you are getting? If you are getting no errors, I'd suggest loading up SQL profiler or extended events (depending on your SQL version) and watching what is happening on the SQL side so you can figure out what is wrong with the code.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2016 at 9:29 am
This looks entirely wrong to me, two instances of a parameter object with the same name in the same scope, using parameters in non-parameterized code, no command is executed. My suggestion is to write a stored procedure with the insert of the two parameters rather than constructing the insert statement in the code.
😎
There are hundreds of examples on the Internet, time to practice the GoogleFoo.
October 12, 2016 at 9:44 am
Eirikur Eiriksson (10/12/2016)
This looks entirely wrong to me, two instances of a parameter object with the same name in the same scope, using parameters in non-parameterized code, no command is executed. My suggestion is to write a stored procedure with the insert of the two parameters rather than constructing the insert statement in the code.😎
There are hundreds of examples on the Internet, time to practice the GoogleFoo.
I noticed my code is a bit wrong too. The 2 things I noticed that I screwed up for sure is that I don't assign the parameters a value and I didn't define "command". But I was trying to point him in the right direction, not completely solve the problem. Especially since he is doing VB and I provided C#.
As a DBA, I personally prefer having no hard-coded SQL in your applications; just have stored procedure calls as it is MUCH easier for a DBA to debug those (index, execution plans, statistics management, code re-use, optimizations, etc) and have it transparent to the application. BUT working with developers who don't like writing out SQL code (more stuff for code verification) and having no standardization on how we do that here, my opinions here at work often get ignored until something breaks. Statistics get out of date and the hard coded SQL starts running horribly slow... then suddenly it is a panic and the DBA (ie me) must fix it. That being said, bad statistics are a DBA problem, but trying to narrow it down to bad statistics when the developer hides all his SQL code in his C# (or VB6!!!) code can be painful to solve. And sometimes it isn't bad statistics but parameter sniffing or HUGE data sets that the software on the client machine cannot handle. Re-running the stored procedure from SSMS to verify the "slowness" of the query vs slowness of the application is much easier with a stored procedure than direct SQL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2016 at 10:18 am
This is for just testing purpose of SSIS package using Script component.
I created an empty table in test database with 2 columns and I drag this script component and trying to pass values from script component to destination table.
So in the query where should I hardcode the values just to see the data in my destination table?
October 12, 2016 at 10:38 am
OH, so you are using SSIS.
why not use a data flow task to copy the data over instead of a script?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2016 at 11:31 am
This is just for testing another area.
SSIS script component used as destination.. to see weather we can load anything entirely from the script to destination.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply