C# or BIML code for inserting records into db

Question

I want to insert values into database when the biml code is ran and the package has completed expansion is this possible using BIML or c#?

I have a table called BIML expansion created in my DB and I have test.biml which loads the package test.dtsx whenever the BIML expansion is completed a record should be inserted into my table that expansion has been completed.

Let me know if you have any questions or needs any additional info.

From comments

I tried your code

string connectionString = "Data Source=hq-dev-sqldw01;Initial Catalog=IM_Stage;Integrated Security=SSPI;Provider=SQLNCLI11.1"; 
string SrcTablequery=@"INSERT INTO BIML_audit (audit_id,Package,audit_Logtime) VALUES (@audit_id, @Package,@audit_Logtime)"; 
DataTable dt = ExternalDataAccess.GetDataTable(connectionString,SrcTablequery);

It has an error below must declare the scalar variable audit_id can you let me know the issue behind it?

Answer 1

In it's simplest form, you'd have content like this in your Biml script

// Define the connection string to our database 
string connectionStringSource = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";

// Define the query to be run after *ish* expansion
string SrcTableQuery = @"INSERT INTO dbo.MyTable (BuildDate) SELECT GETDATE()";

// Run our query, nothing populates the data table
DataTable dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery);

Plenty of different ways to do this - you could have spun up your own OLE/ADO connection manager and used the class methods. You could have pulled the connection string from the Biml Connections collection (depending on the tier this is executed in), etc.

Caveats

Depending on the product (BimlStudio vs BimlExpress), there may be a background process compiling your BimlScript to ensure all the metadata is ready for intellisense to pick it up. You might need to stash that logic into a very high tiered Biml file to ensure it's only called when you're ready for it. e.g.

<#@ template tier="999" #>
<#
// Define the connection string to our database 
string connectionStringSource = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";

// Define the query to be run after *ish* expansion
string SrcTableQuery = @"INSERT INTO dbo.MyTable (BuildDate) SELECT GETDATE()";
// Run our query, nothing populates the data table
DataTable dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery);
#>

Is that the problem you're trying to solve?

Addressing comment/questions

Given the query of

string SrcTablequery=@"INSERT INTO BIML_audit (audit_id,Package,audit_Logtime) VALUES (@audit_id, @Package,@audit_Logtime)"; 

it errors out due to @audit_id not being specified. Which makes sense - this query specifies it will provide three variables and none are provided.

Option 1 - the lazy way

The quickest resolution would be to redefine your query in a manner like this

string SrcTablequery=string.Format(@"INSERT INTO BIML_audit (audit_id,Package,audit_Logtime) VALUES ({0}, '{1}', '{2})'", 123, "MyPackageName", DateTime.Now); 

I use the string library's Format method to inject the actual values into the placeholders. I assume that audit_id is a number and the other two are strings thus the tick marks surrounding 1 and 2 there. You'd need to define a value for your audit id but I stubbed in 123 as an example. If I were generating packages, I'd likely have a variable for my packageName so I'd reference that in my statement as well.

Option 2 - the better way

Replace the third line with .NET library usage much as you see in heikofritz on using parameters inserting data into access database.

1) Create a database Connection 2) Open connection 3) Create a command object and associate with the connection 4) Specify your statement (use ? as your ordinal marker instead of named parameters since this is oledb) 5) Create an Parameter list and associate with values

Many, many examples out there beyond the referenced but it was the first hit. Just ignore the Access connection string and use your original value.