C# Help : Save DataSet to DB

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,831
Reaction score
3,034
Location
On the toilet
Pretty tired and making stupid mistakes.
Pretty simple in code (at least it should be ) but something seems to be wrong somewhere :o

Some help would really be appreciated.

Code: 
public void Main()
        {
            try
            {
                string conn = Dts.Variables["User::DestinationString"].Value.ToString();
                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                A.Fill(dt, Dts.Variables["User::Variable"].Value);

                CreateTABLE(conn , "Data", dt);
                MessageBox.Show("Success");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:"+ex.Message.ToString());
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public static void CreateTABLE(string connectionString, string tableName, DataTable table)
        {

            string sqlsc;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                SqlCommand sqlCom = null;
                
                sqlsc = "CREATE TABLE " + "[" + tableName + "]"+"(";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    sqlsc += "[" + table.Columns[i].ColumnName + "] ";
                    if (table.Columns[i].DataType.ToString().Contains("System.Int32"))
                        sqlsc += " [int] ";
                    else if (table.Columns[i].DataType.ToString().Contains("System.DateTime"))
                        sqlsc += " [datetime] ";
                    else
                        sqlsc += " nvarchar(500)";
                    sqlsc += ",";
                    
                }
                sqlsc += sqlsc.Substring(0, sqlsc.Length - 1) + ")";
                
                sqlCom = new SqlCommand(sqlsc, connection);
                sqlCom.ExecuteScalar();
                connection.Close();
                
            }
        }
 
My eyes, they burn :cry:

Mixing SSIS and C# like that just doesn't feel right. I assume that this is not in a script component and actually an external system that interacts with SSIS?

So, firstly, do you get an exception? What is it?

Can you put the output of the sqlsc variable in your post? Try and run that sql statement on the DB yourself and see what happens.
 
shauntir said:
My eyes, they burn :cry:

Mixing SSIS and C# like that just doesn't feel right. I assume that this is not in a script component and actually an external system that interacts with SSIS?

So, firstly, do you get an exception? What is it?

Can you put the output of the sqlsc variable in your post? Try and run that sql statement on the DB yourself and see what happens.
Click to expand...

It's in a Script task in a SSIS solution :sick: [-]One of[/-] the only way to accomplish this feat: http://mybroadband.co.za/vb/showthread.php/677093-SSIS-Help-Please
Will post the exception a little later today and whether the statement runs in SQL, trying a different route at the moment.
 
Surely there are nice abstractions that allow you to write code like this without burning your eyes :)
 
Not sure what part is your problem, but it looks to me that all you are doing is creating an empty table in the database(not sure if that even works with that connection string), not populating it. Its all a bit messy so hard to tell. Maybe start by separating the creation of the table from saving the data.
 
_kabal_ said:
Surely there are nice abstractions that allow you to write code like this without burning your eyes :)
Click to expand...
Bit of a POC. Data is needed before 12pm tomorrow so sadly cutting corners :o Will fix it up before it goes to Prod :)
zippy said:
Not sure what part is your problem, but it looks to me that all you are doing is creating an empty table in the database(not sure if that even works with that connection string), not populating it. Its all a bit messy so hard to tell. Maybe start by separating the creation of the table from saving the data.
Click to expand...
:erm:
What do you mean an empty table?
What's wrong with the connection string?

User::Variable -> Object
User:Variable -> String which gets the connection string when the SSIS package runs.

Kinda confused by your statements but you did give me a bit of a hint ;)
 
Holy s-h-i-t dude. Stay away as far as you can from DataSets and DataTables. Utter ****e.
Use List<T> with a POCO and use any of the lightweight ORMS.

Heck I do an insert into a table using my own ORM in 1 line of code. Excluding setting up the DbConnection
 
DataTables have their use (if you were to merge 1000s of rows per second a passing a datatable to a stored proc gives you that with a big performance gain over single inserts). Old school but effective.

Create stored proc with a user defined type as input, set that param to the datatable object, set the type to structured and BOOM...it works.

And the only "orm" you should be using is Dapper :whistle:
/hides
 
Last edited:
I just had a quick look but here's something:

Code: 
sqlsc += sqlsc.Substring(0, sqlsc.Length - 1) + ")";

should be

Code: 
sqlsc = sqlsc.Substring(0, sqlsc.Length - 1) + ")";

because otherwise you are just adding the substring instead of removing the last character.
 
What is the aim of the POC? You seem to be using too much C# in the SSIS package, rather use the SSIS components - makes it more manageable and they can each do something small - K.I.S.S.
 
CheekyC said:
Holy s-h-i-t dude. Stay away as far as you can from DataSets and DataTables. Utter ****e.
Use List<T> with a POCO and use any of the lightweight ORMS.

Heck I do an insert into a table using my own ORM in 1 line of code. Excluding setting up the DbConnection
Click to expand...
Hmm... I'll look into this
Solitude said:
I just had a quick look but here's something:

Code: 
sqlsc += sqlsc.Substring(0, sqlsc.Length - 1) + ")";

should be

Code: 
sqlsc = sqlsc.Substring(0, sqlsc.Length - 1) + ")";

because otherwise you are just adding the substring instead of removing the last character.
Click to expand...
Thanks I'll try it out tomorrow.
Shi said:
What is the aim of the POC? You seem to be using too much C# in the SSIS package, rather use the SSIS components - makes it more manageable and they can each do something small - K.I.S.S.
Click to expand...

This was my aim but lack of documentation on some of the furking components has me pulling my hair out. POC is to pull a little over 1 million rows in SSIS into a table and transform it into the current DB structure we have.
Problem is the stored procedure we needed to use has temp tables which cause issues on SQL 2008 R2, so no dataflow tasks. Was tasked to find a way to get the data in it's original form into a temp DB and we shall work from there.

Current/Tried options are mentioned here : http://mybroadband.co.za/vb/showthread.php/677093-SSIS-Help-Please
 
Stumbled onto something that might help. Will post what I hope is a solution tomorrow.
 
Pho3nix said:
If I showed you some of the stuff we have to migrate over.. You'd whip out your boom-box.
Click to expand...
If I am understanding your intentions correctly... Would it not be easier to use SqlBulkInsert to get your data into a temp table, then perform the transformation?
 
Mike Smit said:
If I am understanding your intentions correctly... Would it not be easier to use SqlBulkInsert to get your data into a temp table, then perform the transformation?
Click to expand...

^ this is great for inserting loads of data. Doesn't help with the table creation though.

Another approach (just for fun... SqlBulkCopy would be better if you are just inserting - and this suggestion likely helps you in no way other than looking at another way to do bulk inserts / updates for another problem you may stumble upon later):

Add a User Defined Table Type in the database that maps to the columns you need, then pass in a DataTable which maps to the User Defined Table Type to the sql statement / stored proc. This way you have the temp table in the sql statement / stored proc to play with.

So something like this:

Step 1: Create User Defined Table Type in database

Step 2: Create Datatable in code that maps the same columns and datatypes as that UDTT in the db.

Step 3: Populate the Datatable

Step 4: Pass the DataTable into the sql command as a Structured Parameter

SqlParameter sqlParameter= new SqlParameter("@TemporaryTable", dataTable);
sqlParameter.SqlDbType = SqlDbType.Structured;
sqlParameter.TypeName = "dbo.TempTable";
command.Parameters.Add(sqlParameter);

Command text would be something like:

INSERT INTO MyTable SELECT * FROM @TemporaryTable

Perhaps not for your scenario, but if you want to do bulk updates and inserts in the same call to the db, you at least have a temporary table in the stored proc to join on, or to use a NOT IN (SELECT Id FROM @TemporaryTable). This way there is one call to the db that does both insert and updates on large amounts of data.


My suggestion obviously ignores the table creation part.
 
Last edited:
Ok, my solution is dirty but works. Will refine it over the next couple of days. Atleast I can show that this schit works.

Code in the script component (Source Type)
Code: 
public override void CreateNewOutputRows()
    {
        DataTable dt = new DataTable();
        OleDbDataAdapter oleda = new OleDbDataAdapter();
        oleda.Fill(dt, this.Variables.Variable);

        foreach (DataRow row in dt.Rows)
        {
            Output0Buffer.AddRow();
            Output0Buffer.Item1= Convert.ToInt32(row.ItemArray[0]);
            Output0Buffer.Item2= row.ItemArray[1].ToString();
            Output0Buffer.Item3= row.ItemArray[2].ToString();
        }
    }

From here I use the OLE DB Destination to pull the values into the Landing table. Filthy but it works.
Pic for the structure I used.

SSIS.PNG

For those who asked, we are using straight SSIS because we were lambasted for using webservice calls to do the same thing in an easier fashion. Requirement is everything gets done in the batch job. Blame the "architect" :p
 
Even with throwaway code, stop using variable names like "dt".
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X