Assign Variable for FileName of Source File

I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.

I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.

Any ideas or suggestions?

The ForEach loop container can store the name of eac enumerated file into a variable. This article explains how to do that: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

Once its in a variable you can do pretty much anything you want with it and it seems you already know how to do that. If not, just reply here.

-Jamie

|||

Thanks for the link, but I have was able to setup a variable @.[User::FN] to the ConnectionString property associated with the flat file connection, and the looping and loading effort is working fine.

This may seem like a stupid question, and I apologize, but I cannot figure out how to reference that variable and use it in my execute SQL task that follows later and does my insert of the data into my table. What I want to do is something like this:

Insert into Table1 Select a, b, c, @.FN From Table2

The parser requires that I declare the variable and does not seem to recognize the @.[User::FN] variable.

Any suggestions?

|||

Yes, use an expression. This (sort of) shows you how:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

Assembly.Load can't load my custom assembly from the GAC.

Hi there.

I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.

In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:

Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='?' or one of its dependencies. The system cannot find the file specified.

(note: for security I've changed some of the above line).

So I changed the Assembly.Load() to use

System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089

I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).

So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?

Thanks
Jas.

Hi Jas,

In order you load your assembly using Assembly.Load, the assembly already has to be loaded into your database using CREATE ASSEMBLY. SqlClr will only load assemblies directly from the gac that are on the list of supported framework assemblies available here: http://msdn2.microsoft.com/en-us/library/ms403279.aspx. All the assemblies on that list (including System.Data) can be accessed by any assembly in your database, but all other assemblies must be loaded using CREATE ASSEMBLY first.

Steven

aspx vb sql parameter passing

Hello,
Can someone kindly point out what is wrong with the following code file. I'm trying to:
- fill a dropdown from a db on page load (this works!)
- when user selects from the list and hits a button, pass the dropdown value to a second query
- use the second query to make another call to the db and fill a data grid

In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.

Thanks

PS comments about my coding approach are welcome - I'm new to aspx...

<%@. Language="VBScript" Debug="true"%>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.data.SqlClient" %>
<html>
<head>
 <script language="vb" runat="server">
 Dim dbConnection As SqlConnection
 Dim ds_Teams,ds_Agents As DataSet
 Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter
 Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"
 Dim sql_select_teams = "" & _
 "SELECT team_no, team_name " & _
 "FROM dbo.team " & _
 "WHERE (team_status = 'Curr')"
 Dim sql_select_agents = "" & _
 "SELECT last_name + ', ' + first_name AS name, agent_no " & _
 "FROM dbo.users " & _
 "WHERE (team_no = @.Team) AND (NOT (agent_no = '1029')) " & _
 " AND (NOT (last_name IS NULL)) " & _
 " AND (NOT (first_name IS NULL)) " & _
 " AND (NOT (first_name = 'FirstName')) " & _
 "ORDER BY last_name"
 Dim teamList = "teamList"
 Dim agentList = "agentList"
 Sub Page_Load(Sender As Object, E As EventArgs)
 if not (IsPostBack)
 ds_Teams = new DataSet()
 dbConnection = New SqlConnection(dbConn)
 sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection)
 sqlCmd_Teams.Fill(ds_Teams, teamList)
 dbConnection.close()
 dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView
 dropdownlist_Teams.DataBind()
 end if
 End Sub
 sub Get_Agents(Sender As Object, E As EventArgs)
 ds_Agents = new DataSet()
 dbConnection = New SqlConnection(dbConn)
 sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)
 sqlCmd_Agents.SelectCommand.Parameters.Add(new SqlParameter("@.Team", SqlDbType.NVarChar,4))
 sqlCmd_Agents.SelectCommand.Parameters("@.Team").Value = dropdownlist_Teams.DataValueField
 sqlCmd_Agents.Fill(ds_Agents,agentList)
 dbConnection.close()
 datagrid_Agents.DataSource=ds_Agents.Tables(agentList).DefaultView
 datagrid_Agents.DataBind()
 end sub
 </script>
</head>
<body>
<form runat="server">
<asp:DropDownList id="dropdownlist_Teams" runat="server"
 DataTextField="team_name"
 DataValueField="team_no">
</asp:DropDownList
<input type="submit" onserverclick="Get_Agents" value="Get Agents" runat="server"><br /
<ASP:DataGrid id="datagrid_Agents" runat="server"
 Width="500"
 BackColor="#ccccff"
 BorderColor="black"
 ShowFooter="false"
 CellPadding=3
 CellSpacing="0"
 Font-Name="Verdana"
 Font-Size="8pt"
 HeaderStyle-BackColor="#aaaadd"
 EnableViewState="false"
 />
</form>
</body>
</html>

(1) dim a variable as string and then assign the sql stmt to the variable.
xample :

dim str1 as string
str1="Select..."

(2) is the valuefield numeric or string type ? also you might want to move the assigning of the str stmt to the get_events event itself rather than declaring it as a global string.

hth

ASP.NET 2.0 Interface for SSIS

Hi there,

we have several DWH Load processes running in SSIS, however I want to enable the enduser to have more control and information about the status of these processes. Therefore I plan to write a monitoring interface based on ASP.NET 2.0. However I hardly could find good docu or samples on what is available from within ASP.NET 2.0 to read out or control SSIS.

Anybody having me some good pointers to start with ?

To control Ssis packages programmatically start with the Microsoft.SqlServer.ManagedDTS assembly, (Microsoft.SQLServer.ManagedDTS.dll). For the more monitoring type functions, I don’t think there is anything specific, but I’d suggest you look at the logging built into SSIS, set this on in your packages, and query the destination log.

There is also the SSIS service, but not sure if there is a way of communicating with that, other than basic service APIs, and/or WMI.

|||Application class in Microsoft.SqlServer.Dts.Runtime namespace (Microsoft.SQLServer.ManagedDTS.dll assembly) provides some ability to interact with service and monitor/manage running packages. In particular, see
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getrunningpackages.aspx
method.

Together with SSIS logging this should provide the API you need.