I’m doing an interesting project in work where I am “scraping” data off of word documents, and may do a few blogs on it. Probably not the best way to handle this sort of thing, but it works well without the cost of a third-party tool that transforms data to XML, and the code is fairly straight forward. In my task, the data from a series of .doc and .docx templated files will be moved to a .csv, imported to SQL Server, cleaned, moved to a dimension table, and finally, fed to a cube in Analysis Services. Pretty cool eh? I think so. One thing about doing this is that you’ll have to have either 1) Microsoft Office installed on the server, or 2) the Primary Interop Assemblies Redistributable, or PIA, installed on the server, both so that you can take advantage of the Office (in my case Word) methods to fetch the data off of the documents if you plan on scheduling and running from the server itself. Most servers normally don’t have Microsoft Office installed – there’s no reason – and to avoid burning a copy of office that costs money, you can add these assemblies…the download is free. It reminds me back in the day when we used SQL Mail, going back to 1998 and SQL 6.5 and then SQL 7. You had to install the entire Outlook client on the server, and then a smart DBA would go and randomly delete the GUI so nobody could open the client. Just a random thought…didn’t SQL Mail really suck back then, and now? For those of you who started as a SQL practitioner beginning with SQL Server 2005 and only know database mail, you really missed a lot of fun trying to figure out why that abomination never worked correctly. Oh well. Anyway, get the PIARedist.exe, and start doing some slammin’ Office Automation. Happy Primary Interopping! Lee Everest -------------------------------- http://www.microsoft.com/download/en/details.aspx?id=3508
da7d0a11-f174-4c21-a52e-87eff91dcc40|4|4.0
I had a post earlier for counting files in a folder, but it was DOS and could be called from a process task...that's really not the way that I'd do it I guess, so don't use that example. I see plenty of hits for folks searching and looking at that page, so I offer up this as an alternate. If I were tasked to do this in SSIS I'd probably use something like the following.
Crack open SSIS, add a Script Task to the Control Flow, create two SSIS variables, one called Folder of type string, and another named Count of type integer, and then add this code to the script task:
using System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;using System.IO;
namespace CountFilesInFolder{ [System.AddIn.AddIn("ScriptMain", Version="1.0",Publisher="",Description="")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { string foldername; int count; foldername = Dts.Variables["Folder"].Value.ToString(); count = Directory.GetFiles(foldername).Length; MessageBox.Show(count.ToString()); Dts.Variables["Count"].Value = count; Dts.TaskResult = (int)ScriptResults.Success; } }}
Directory.GetFiles(foldername).Length will count files for you in .Net/c# probably as good as any method, so use this and not the garbage that I posted in the other blog. Remember, I geek here, so take this stuff (including this post) at face-value.
Thanks,Lee
----------------------------
MELINDA - Well, I just thought I'd give you them. I liked walkin' with you. I got a blister the size of a quarter on one heel. Well, I'll see you sometime, I guess. (She walks to the door and stops as if she expects Karl to say something.)
KARL - A blister shore can hurt.
Posted in:
.Net ,
SSIS Tags:
d4edab7e-8e1f-4038-867a-39cf2a27c419|0|.0
Someone just asked me if there's a built-in function in TSQL that takes a string and changes the first letter of each word to upper case. I told them hell I don't know but I think not; here's a way to do it without a bunch of char functions...use a CLR UDF. One line and all works pretty well:
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Globalization;
public partial class UserDefinedFunctions
{ [Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UpperFirst(string value) {
return new SqlString(CultureInfo.CurrentCulture.TextInfo.ToTitleCase(value));
}};
sp_configure 'clr enable',1GORECONFIGUREGOSELECT dbo.UpperFirst('TSQL upper case first letter of each word')GO
Posted in:
.Net ,
Beginner ,
TSQL Tags:
3f2f2e00-057b-4ade-8943-a7e629fa9c8f|0|.0
Back a few months ago I was doing some cool office automation. Well, at least I thought it was cool. Anyway, I wanted to add to a couple of other blogs that I did and share the following code. If you want to send data from Microsoft Excel to SQL Server, this would be one way to do it. Now, lots of folks send data to Excel, that’s no biggie; going the other direction is a bit more of a challenge. While on the project I tried a half-dozen ways, including (believe it or not) calling an SSIS package from a stored procedure called from Excel. Try this one…works well: Application.ScreenUpdating = False
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConn As String
Dim p1 As ADODB.Parameter
Dim p2 As ADODB.Parameter
Dim p3 As ADODB.Parameter
Dim p4 As ADODB.Parameter
Dim p5 As ADODB.Parameter
Dim p6 As ADODB.Parameter
Dim wks As Worksheet
Dim cnt As Integer, myrange As Range
Set cmd = New ADODB.Command
Set cn = New ADODB.Connection
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=servername;INITIAL CATALOG=databasename;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn
Set wks = Worksheets("Accts")
cmd.ActiveConnection = strConn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_myMergeProcedure"
Set myrange = Columns("b:b")
cnt = Application.WorksheetFunction.CountA(myrange)
Set p1 = cmd.CreateParameter("cola", adInteger, adParamInput)
Set p2 = cmd.CreateParameter("colb", adInteger, adParamInput)
Set p3 = cmd.CreateParameter("colc", adVarWChar, adParamInput, 255)
Set p4 = cmd.CreateParameter("cold", adVarWChar, adParamInput, 255)
Set p5 = cmd.CreateParameter("cole", adVarWChar, adParamInput, 255)
Set p6 = cmd.CreateParameter("colf", adInteger, adParamInput)
cmd.Parameters.Append p1
cmd.Parameters.Append p2
cmd.Parameters.Append p3
cmd.Parameters.Append p4
cmd.Parameters.Append p5
cmd.Parameters.Append p6
For introw = 2# To cnt
p1.value = IsNullFloat(wks.Cells(introw, 1))
p2.value = IsNullFloat(wks.Cells(introw, 2))
p3.value = IsNullChar(wks.Cells(introw, 3))
p4.value = IsNullChar(wks.Cells(introw, 4))
p5.value = IsNullChar(wks.Cells(introw, 5))
p6.value = IsNullFloat(wks.Cells(introw, 6))
cmd.Execute
Next introw
Set wks = Nothing
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Thanks for reading,
Lee
-------------------------------
a06e8650-f0a2-4558-a586-c5ec5fc1deb9|0|.0
I’ve been tasked to generate a bunch of files at work for an actuarial application to consume; without getting into the details of what the app does with the files (because I have no idea, frankly), I need to query SQL Server, generate a file for each “key” whereby each corresponding foreign key has approximately forty records, and one column of type decimal. Data looks like so in a SQL Server table: 1 1.119 1 2.225 1 3.330 37 more times … 2 10.3 2 1.1 2 4.5 37 more times I’ve tried eight or ten methods up to now, and I’m going to cut to the chase and skip right to the fastest method that I have found, and then go back and jot down the others in subsequent blogs. Check the first post to get an idea of some of the ideas that I have brainstormed to accomplish this task. The method to generate files the fastest up to this point? A c# console application. I could have written it in c++ or c as well of course, but c# seems just as good, so I chose this language. Also, because I may try to take this code and put in an SSIS script task as additional method, I’d be set since you can use c# and vb.net there as well. Anyway, here’s the code. I’m still tweaking this so I’ll be working on it as well for improvements. And, if you’re thinking that it could be much better than below, by all means send your thoughts to me while keeping the hate mail to yourself, even if you are 1) the “de-facto” expert among us on c# I/O, 2) you buddies with the guy that wrote the c# compiler 3) I happen to be treading on someone’s “sacred turf”, or 4) you just don’t like me or my blog. Fair enough? I think we’re gold then. This thing generates approximately 25-35 files per second; the fastest SSIS package that I have built does about 12 files/second, and I have yet to find anything to beat this time, including bcp. Come up with your own method and share with us if you like. Thanks for reading! Lee Everest, MS -------------------------- He was wearing my Harvard tie. Can you believe it? My Harvard tie. Like oh, sure he went to Harvard using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
public class Program
{
static StringBuilder sb = new StringBuilder();
public static void Main() {
SqlConnection sqlConnection = null;
int maxvalue=151000;
string dbconn = "Application Name=SqlTest;Data Source=SQLVM1;Trusted_Connection=yes; database=tempdb";
using(sqlConnection = new SqlConnection(dbconn))
{
sqlConnection.Open();
SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 2);
SqlDataReader reader;
using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
{
param.Value = 1;
cmd.CommandType = CommandType.Text;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
while ((int)param.Value<maxvalue)
{
cmd.CommandText = "SELECT id FROM deleteTablexus WHERE idx = @id";
reader = cmd.ExecuteReader();
while (reader.Read())
sb.Append(reader[0].ToString() + "\r\n");
SaveToFile(param.Value.ToString());
reader.Close();
param.Value = (int)param.Value + 1;
}
}
}
}
public static void SaveToFile(string fileName)
{
System.IO.TextWriter w = new System.IO.StreamWriter(@"c:\temp\files\"+fileName+".dat");
w.Write(sb.ToString());
w.Flush();
w.Close();
}
}
945f8986-1d82-4443-869a-89b59880f910|0|.0
Data Tier Applications in VS 2010 seem to have taken on some nice features as you use it with SQL Server 2008 R2. One thing that I like is the ability to do quite a bit inside of Visual Studio rather than in SQL Server Management Studio (SSMS). You can do all of your development inside of VS 2010 – even easier than in VS 2008 since there was no Data-Tier Application project as there is now. So, what you can do is create all of your objects inside of VS 2010 as a part of an application, and then bundle-up all of the objects and deploy to a database server from that wacked-out .dacpac extension that they’ve created. Very nice! Has me thinking…I wonder if someday all dev work, whether it be c, c#, vb.net, tsql, etc. will be based out of one tool? Seems they’re going that direction, and have finally gotten close. Let’s take a look: VS 2008 VS 2010 I read somewhere that you could add data for, say, lookup values, but I forgot to bookmark it, so I wasn’t sure what the correct method for doing this. I checked Books Online and didn’t find it readily; what I did find, however, was the Pre-Deployment and Post-Deployment folders in the project. This is super-handy for adding just what I need – a place to automatically add domain, or lookup, or static, values to my database tables where necessary. Check out these screenshots. Note that I’ve added a script to my Post-Deployment folder – it looks by default in the project folder in the Script.PostDeployment.sql file, in our case, since we want to do a post deploy operation by inserting my values, so we add our script file (StaticValues.sql) in the same location as well: My script for this demo has something super simple. /*
Scipt: StaticValues.sql
Reason: Lookup Values using Post-Deployment
Database: DAC
Date: 12/3/2010
*/
USE DAC
GO
INSERT INTO [DAC].[dbo].[Table1]
([column_1]
,[column_2])
VALUES
(1
,100)
GO
INSERT INTO [DAC].[dbo].[Table1]
([column_1]
,[column_2])
VALUES
(2
,200)
GO
INSERT INTO [DAC].[dbo].[Table1]
([column_1]
,[column_2])
VALUES
(3
,300)
GO
When I build the project, I get the following:
Here’s the deploy – note that VS 2010 runs my .sql file using sqlcmd syntax:
------ Build started: Project: DAC, Configuration: Debug Any CPU ------
DAC -> C:\Documents and Settings\Lee\my documents\visual studio 2010\Projects\DAC\DAC\sql\debug\DAC.dacpac
------ Deploy started: Project: DAC, Configuration: Debug Any CPU ------
Pre-Deploy: Starting script execution…
:
(1 row(s) affected)
:
(1 row(s) affected)
:
(1 row(s) affected)
Pre-Deploy: Finished script execution.
Validating upgrade
Preparing DAC metadata in the SQL Server instance 'S03777\SQL2K8'
Preparing deployment script
Creating database 'DAC_1_0_0_0__129358633652284695'
Creating schema objects in database 'DAC_1_0_0_0__129358633652284695'
Registering the DAC in DAC metadata.
Setting database 'DAC' to read-only mode
Disconnecting users from database 'DAC'
Preparing scripts to copy data from database 'DAC' to database 'DAC_1_0_0_0__129358633652284695'
Disabling constraints on database 'DAC_1_0_0_0__129358633652284695' before populating data
Inserting data from database 'DAC' to database 'DAC_1_0_0_0__129358633652284695'
Enabling constraints on database 'DAC_1_0_0_0__129358633652284695'
Setting database 'DAC' to read-write
Renaming database 'DAC' to 'DAC_1_0_0_0__129358633654000310'
Renaming database 'DAC_1_0_0_0__129358633652284695' to 'DAC'
Upgrading DAC metadata to reflect the new DAC version
Previous copy of database DAC is available as DAC_1_0_0_0__129358633654000310.
Post-Deploy: Starting script execution…
Post-Deploy: Finished script execution.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
Thanks for reading,
Lee
--------------------------------
“Deck the harrs with boughs of horry, fa ra ra ra ra, ra ra ra ra”
http://msdn.microsoft.com/en-us/library/ee210546.aspx
Posted in:
.Net ,
SQL Server 2008 Tags:
5124e5ba-a0e2-4b04-9370-213330d1c586|0|.0
I haven't had to call a web service from an SSIS package, but did call a package from a web service once that I blogged about, and today ran across a cool blog that outlines this. Pretty slick! This one is nice because he shows the subtleties of calling a particular type of web service, one that is created from WCF (.svc, not a .asmx service, the old kind). Remember that WCF is the framework that devs use to allow clients or consumers to hit all types of data sources via a service-oriented architecture. There are a ton of things on this, just do a Google! search and go create one! Check out this post, I think you'll like it as I did. Good job! Lee ----------------------- I'm going to pay you today for this coming week... so you have some walking-around money. You need to buy some toothpaste and cleaning up supplies to have back there. Get you some magazines and some hard candy... something to keep you busy at night. Link: http://en.wikipedia.org/wiki/Windows_Communication_Foundation Flickr Tags: ssis package calling web service
Posted in:
SQL Server 2008 ,
.Net Tags:
98b0d5a4-8923-4cc2-924a-a283da80ca39|0|.0
I’m about to do several blogs on SSIS Custom Components; these are the tasks that you code and add to the SSIS toolbox to include some sort of transformation or connection functionality that Microsoft might not have delivered. Very cool and a lot of fun to write! While working on these I wanted to debug one in Visual Studio, and the first time that I tried I got the following: Figure 1. My debug is jacked! Debugging in Visual Studio (VS) isn’t that difficult. I’ve debugged web apps and web services before so thought I’d give one of these Custom Components a go. Had some problems, though. I kept getting the following: The breakpoint will not be hit. No symbols have been loaded for this document. If you Google! this you’ll see 850 million hits come back with folks having a similar problem, clear back to 2004 in some cases. I’m not sure what those problems were for them, but for me it was because the .dll that I compiled did not match the .dll that was being debugged, and this is not good. If you click on the above picture you will see that the breakpoint that I set isn’t a solid color, and has a little warning icon next to it, a clear indication that your debug project is doomed for failure. You will immediately see this when you “Attach to Process…” (You can right-click and choose location and mark it OK to look in a different location, but don’t do that because your code might have changed). What I did here was simply go to Project>>Properties and find out exactly where the .dll is compiling. I thought that mine was in the debug folder. It was sort of…the Object>>Debug folder and not the bin>>Debug folder. Another way to make sure is to simply check the date of the .dll. If I had looked at this I could have found the problem sooner than I did. Remember that if you recompile your class, you’ll have to remove your custom component and add it back again. It might not hurt to close BIDS and reopen it again as well and, obviously, you will want to delete your custom component and completely remove it from the toolbox and add back via “Choose Items”. Figure 2. We’re gold! Look for my series on SSIS custom components, and thanks for visiting my blog. Lee ------------------------------ “Like chill out, Scooby-Doo, stop shaking.” Flickr Tags: SSIS Custom Components,debug in SSIS
Posted in:
SSIS ,
.Net Tags:
de404df2-246a-456b-ad33-1e3d9644a0bf|0|.0
About 6 months ago, SQL Server magazine (SQLMag.com) came out with a list of free downloads for SQL Server; it included both totally free as well as trial downloads, all of which were lots of neat third party add-ins for SQL Server. I thought it would be cool to review a bunch of them to see which ones might be really great to have in my toolbox, so I'm going to start a little blog series on them. SQL Mag listed them, but really didn't talk much about them, other than a brief comment. I'll go further than SQLMag by looking into them a little more in depth, and actually investigate some of them. I hope that you find this an interesting series. And, thank goodness as well for my Hyper-V setup at home. I am loading all of this stuff up on a .vhd that I can trash at any given time with the help of the delete key; I advise that you set the freebies up on a dev box so that if you don't like any of them, you don't have to worry about them not uninstalling cleanly. Leaving registry keys, files, folders, and a trail of dead bodies behind is never any good. Ok, let's get going! The first one that I chose at random is SQLsharp[1] (SQL#), and downloaded at http://www.sqlsharp.com/ is a set of precompiled CLR functions, including scalar, table-valued, and aggregate, for the SQL DBA or Dev looking to expand his or her toolset but either doesn't know c# or vb.net to write them, or finds value in them being already written. I write CLR stuff for sure, but regardless, I'd definitely use some of these - why reinvent the wheel when the author has done a good job writing them for you? The first thing to do is go look at the well written .pdf manual [2] that they have created. This will give you a list of the objects and how they are used, so you can get a feel of which ones might be of value to you. Some that I fired up were some of the Util table-valued functions such as GenerateDateTimes and GenerateInts, some of the scalar-valued functions including DaysLeftInYear, Date_Age, IsValidIPAddress, and several of the string and aggregate functions. I found them to be accurate and easy to use. The documentation is also clear well written. A nice to have, though, would be a help file rather than a .pdf as a suggestion for the future. Because there are so many handy functions, I can't review each, but one that I wanted to mention one in particular from the user-defined types group - NVarcharArray. Because we don't an array in TSQL, I found it really cool. Ken Henderson, in one of his early books, The Guru's Guide to Transact-SQL if memory serves me, wrote one in an xproc (extended stored procedure). Ken's was very cool, and the one written by the SQL# is nice as well. It was probably a considerable effort to write, with a bunch of code and logic behind it (if it's anything like Ken's). Great job on this one! Here's a script to see what some of the methods were for this array data type. I'm going to use columns in a table as my data, so if you want to follow along create the following: CREATE TABLE [dbo].[STAGE_ITM](
[LINETYPE] [varchar](50) NULL,
[LINENUM] [varchar](50) NULL,
[STORENUM] [varchar](50) NULL,
[INVOICENUM] [varchar](50) NULL,
[PRODUCTNUM] [varchar](50) NULL,
[PRODUCTDESC] [varchar](50) NULL,
[QTY] [varchar](50) NULL,
[AMT] [varchar](50) NULL
) ON [PRIMARY]
GO
Now run some of the methods for the NVarcharArray data type. I result set after each line of code commented for you.
--Get Some Data
DECLARE @strVar varchar (100)=''
SELECT @strVar = column_name+',' + @strVar from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'STAGE_ITM'
SELECT @strVar=LEFT(@strVar, LEN(@strVar)-1)
--Use Type_NVarcharArray
DECLARE @strVar2 SQL#.Type_NVarcharArray
SELECT @strVar2=@strVar
SELECT @strVar2.Count
-- 8
SELECT @strVar2 = @strVar2.AddData(2,'NewCol').ToString()
SELECT @strVar2.ToString()
--AMT,NewCol,QTY,PRODUCTDESC,PRODUCTNUM,INVOICENUM,STORENUM,LINENUM,LINETYPE
SELECT @strVar2 = @strVar2.Sort()
SELECT @strVar2.ToString()
--AMT,INVOICENUM,LINENUM,LINETYPE,NewCol,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2.ContainsItem('NewCol')
--1
SELECT @strVar2.GetAt(5)
--NewCol
SELECT @strVar2.ContainsItem('PRODUCTNUM')
--1
SELECT @strVar2 = @strVar2.RemoveAt(1).ToString()
SELECT @strVar2.ToString()
--INVOICENUM,LINENUM,LINETYPE,NewCol,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2= @strVar2.RemoveItem('NewCol').ToString()
select @strVar2.ToString()
--AMT,INVOICENUM,LINENUM,LINETYPE,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2 = @strVar2.RemoveRange(1,2).ToString()
select @strVar2.ToString()
--LINENUM,LINETYPE,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
select @strVar2.Clear().ToString()
--
I tested these methods - Count, ToString(), AddData(), Sort(), ContainsItem, RemoveAt(), RemoveItem(), RemoveRange(), and Clear() in the above examples. All worked very well, just as you would expect if you were coding in c# rather than in the database. Cool!
Overall, I think SQLSharp is really slick, and I recommend downloading and testing it out. Look into it closely and see if there aren't a few functions that you could use in your shop...I'm betting that there are some. As with several of the other free tools, there is a fee for an expanded version, but I did not investigate what the pay version gave you. Go to their site and read up on them, and let me know if there is one that you get stoked about.
If you're looking to expand the functionality of TSQL, and don't want to write a bunch of functions in TSQL or c#/vb.net, look to this library for some nice additions to your arsenal. To the guys at SQL #, well done.
Thanks for reading,
Lee Everest
References
[1]. SQLSharp. Retrieved on 3/20/2010 from http://www.sqlsharp.com/
[2] Manual. Retrieved on 3/20/2010 from http://www.sqlsharp.com/download/SQLsharp_Manual.pdf
[3] facebook page at http://www.facebook.com/pages/SQL/97562289644
Posted in:
SQL Server 2008 ,
TSQL ,
.Net Tags:
f468545d-8e07-42aa-82f2-a40e2ab7bd7b|4|5.0
A solution that seems to work. I've been dorking with SSIS and Linq the last couple of days, and it's been enjoyable. I tried an experiment with the "ORM" tool for Linq in a blog but that didn't work as I initially expected, and certainly needs some more work. I probably should have posted this particular blog first, because this seems to be a viable solution for using Linq inside of SSIS; whether or not the performance is good or if it makes sense is yet to be determined. TBD! At any rate, I'll combine what we did in the demystify script component blog, part 1 and create a data source as a linq query to a SQL Server database. Sweet! In order to run this, you'll need SQL Server 2008 Integration Services and the Adventure Works OLTP database. I use c# in all of my stuff, but this can be rewritten in vb.net for sure. First, create a database called Test if you don't have one already, and then run this script. We'll land the data here from the AdventureWorks HumanResources.Employee table. Note: because I'm experimenting to a degree here, I'll leave off the PK which allows me to run this over and over, so thanks in advance for no nasty Joe Celko-esque emails that my DDL stinks. USE [Test]
GO
/****** Object: Table [dbo].[Linq_Employee] Script Date: 03/07/2010 09:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Linq_Employee]')
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Linq_Employee](
[EmployeeID] [int] NULL,
[Title] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
Now go open up BIDS, get to an SSIS project, add a package, add a Data Flow task to the Control Flow tab, and drag a Script Component over to the Data Flow tab. Remember that you first need to add that output buffer to the "Inputs and Outputs" of the Script Transformation Editor as well. I called mine OUTPUT. Go back to "Script" crack open the Edit Script, and paste the following code. It goes without saying - but I'll say it anyway - that you need to change your server name, unless you also have a (Hyper-V) SQL Server named SQLMV1:
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;
using ns_Employee;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void CreateNewOutputRows()
{
DataContext db = new DataContext(@"Data Source =SQLVM1;
Initial Catalog=AdventureWorks;
Integrated Security = SSPI");
Table<Employee> Employees = db.GetTable<Employee>();
var EmployeeResults = from e in Employees
select e;
foreach (var emp in EmployeeResults)
{
OUTPUTBuffer.AddRow();
OUTPUTBuffer.EmployeeID = emp.EmployeeID;
OUTPUTBuffer.Title = emp.Title;
}
}
}
Notice that I have a line using ns_Employee. This is because I've been dorking with this thing today and decided to create a separate entity class file for my object. So, either right click on the project in the script component, do an Add Class to add a separate file, name it Employee.cs, and add the following, OR optionally just add the class above or below the class in your main.cs file:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;
namespace ns_Employee
{
[Table(Name = "HumanResources.Employee")]
public class Employee
{
[Column(IsPrimaryKey = true)]
public int EmployeeID;
private string _Title;
[Column(Storage = "_Title")]
public string Title
{
get { return this._Title; }
set { this._Title = value; }
}
}
}
Ok, we're almost there...now simply create a connection to a source destination (I used OLE), map the script output, and fire it up! Booyah!!!
Lee Everest, M.S.
------------------------------
SSIS and Linq - Who'd a thought?
Join my facebook group and let's chat:
Flickr Tags: SSIS and linq
Posted in:
SSIS ,
.Net ,
SQLServerPedia Tags:
613351d1-b4c9-4a75-8c2c-14aad9e65a88|0|.0
Someone jumped the gun again.
If you try to install the VS 2008 GDR R2 download and you receive this...
You probably need to get another operating system, preferrably an old one. This is the download for VS that provides support for SQL Server 2008, as well as Team Edition for Database. I tried to install it on Windows 7 and got the message; the download is an April 2009 version, so hopefully they come out with a new one soon. In the meantime if I want to gen up a bunch of data with Visual Studio, I'll have to do it elsewhere.
Lee
---------------------------
Ralphie: [Ralphie is shoved down the slide, but he stops himself and climbs back up] No! No! I want an Official Red Ryder Carbine-Action Two-Hundred-Shot Range Model Air Rifle! Santa Claus: You'll shoot your eye out, kid.
7b44c51d-b045-4fd9-abaa-c5193092d434|0|.0
Tools galore!
I dig the list of tools that Scott Hanselman has been putting together - really a great list! You can find it here and at the below link. This has to be the most exhaustive that I've seen, so check it out!!!
Lee
---------------------
Maybe there is one to fix the formatting of all of my posts on this blog
http://www.hanselman.com/blog/ScottHanselmans2009UltimateDeveloperAndPowerUsersToolListForWindows.aspx
57bd089c-0f63-480f-8db4-3fbd69a5085f|0|.0
Got the install completed…wow that took awhile For Team Edition Database 2008, if you try and use the Data Generator it won’t work (on SQL Server 2008) because you have to first install the GDR R2. Well, not necessarily true. You first must install Service Pack 1 before, or you get an error. Yes, I tried to install without the service pack and got the error. Anyway, this little gem of a download does a number of things; what I was looking for was to be able to generate data with the new SQL Server 2008 data types.Now I finally see the data gen wizard for SQL Server 2000, 2005, and 2008. Yay! Lee ------------------------------------------ Talk about something wearing you out del.icio.us Tags: Visual Studio 2008 Team System 2008 Database Edition,data generator http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en#filelist
Posted in:
.Net ,
SQL Server 2008 Tags:
c4d57efa-8b67-4b0a-bab0-fe818aaa1c7f|0|.0
Code to create a working version of this technique…
So here’s the implementation for part 2 in a web project. There are a few caveats to this thing. First off, for demonstration purposes only m-kay? Don’t go trying to put something like this into your production environment, please. Second, it does not have a great deal of logic as far as randomization goes for search results, so I’ll leave some of that fun for you to work on. (And when you come up with something cool, please forward to me so I can check it out). Finally, it dumps all results that match at least 33% to the data grid, something that you would probably not do in your search. Anyway, download the zip file which includes the project, below, and get the TVF from the link below. There is a Readme.txt located in the project. As always, RTFM, please.
If you need any assistance putting this together, give me a shout.
Lee
------------------------------------------
Whassat you say? Bitmasking, part 3? Look for it to come out around 2012.
Table-valued function - http://www.sqlservercentral.com/articles/bitmasking/66774
c# web project – ASP.NET Web in c#
Flickr Tags: Bitmasking in sql server part 2 implementation,bitmasking web project,bitmasking demo web project,lee everest,sql server blogs
5e20fcb3-5947-4271-859d-1ea9b3b70792|0|.0
Experiences with SSIS calling (yeech) Pervasive as a data source
I needed to create a web service to pass data from Pervasive to SAP, and completely bypass SQL Server. Since I didn’t have the code handy and was too lazy to look on MSDN, I looked around on Google! … and found nothing to write home about. So, here’s my contribution in case you need to write one. Hope it helps someone looking to do same.When building a web service to call an SSIS package (without touching a SQL Server and in my case calling Pervasive), I had to make a few adjustments that I probably wouldn’t have if we were only talking SQL Server. First, the ProtectionLevel had to be set with encrypt sensitive with password. I found that if I didn’t use this I would have the password default to blank as mentioned in help. Adding password to SSIS package configurations in a table didn’t work either – the password ‘*****’ really was ‘*****’. So, inside of the web service, you have to use something what I have below. I first thought at first that package.password would work, but you must set the application password.
Application app = new Application();
app.PackagePassword = "sql";
You can also use the configuration in the project properties configuration tab to lock in the password:
This allows you to use the following to access the password:
package.PackagePassword = Properties.Settings.Default.pwd;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using Microsoft.SqlServer.Dts.Runtime;
namespace SAPPOC
{
/// <summary>
/// Summary description for WebServiceExample
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class WebServiceExample : System.Web.Services.WebService
{
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public string POCDates(string StartDt, string EndDt, String packageName)
{
string pkg = @"\\yourservernamegoeshere" + packageName.ToString();
Application app = new Application();
Package package = app.LoadPackage(pkg, null);
package.PackagePassword = Properties.Settings.Default.pwd;
Variables vars = package.Variables;
vars["StartDt"].Value = StartDt;
vars["EndDt"].Value = EndDt;
DTSExecResult result = package.Execute();
if (result == DTSExecResult.Failure)
return result.ToString();
return "SSIS package executed successfully. You sent for start/end: " + StartDt.ToString() + " / " + EndDt.ToString();
}
}
}
Lee
-----------------------------
Thought for the day: “Compost Happens”
Flickr Tags: web service calling an ssis package,web service SSIS,SQL Server blog,sql server blogs,c# web service calling an SSIS package
Posted in:
.Net ,
SSIS Tags:
9e836cf1-87e7-436b-85dd-c9d18ef5c06d|0|.0
I was goofing off with bitmasking again and decided to do some in c#. I came up with this helper class to aid in working with bit fields as an example. This class takes advantage of an enum with flags attribute to assist with assigning values to the flags. An interesting addition might be to create the logic so that, for example, the particular database permissions would take precedence over another. So, when you assign a user DB data reader and DB denydatareader, the permission "deny" would be the prevailing permission. Likewise, if assigning Administrator would not allow a mask for db datareader, for instance, because that permission set has higher privileges than the latter.
Lee
using System;using System.Collections.Generic;
namespace BitWiseExample_Permissions{ [FlagsAttribute] public enum DBPermissionValues : int { None = 0, DbDataReader = 1, DbDataWriter = 2, Administrator = 4 }; public class DBPermissions { public DBPermissions() { _permissions = DBPermissionValues.None; } public DBPermissions(DBPermissionValues permissions) { _permissions = permissions; } private DBPermissionValues _permissions; public bool IsDbWriter { get { return ((_permissions & DBPermissionValues.DbDataWriter) == DBPermissionValues.DbDataWriter); } set { if (value == true) _permissions = _permissions | DBPermissionValues.DbDataWriter; } } public bool IsDbReader { get { return ((_permissions & DBPermissionValues.DbDataReader) == DBPermissionValues.DbDataReader); } set { if (value == true) _permissions = _permissions | DBPermissionValues.DbDataReader; } } public bool IsAdmin { get { return ((_permissions & DBPermissionValues.Administrator) == DBPermissionValues.Administrator); } set { if (value == true) _permissions = _permissions | DBPermissionValues.Administrator; } } }}
--------------
Glad this isn't a database blog
b468298e-0b4b-4b13-87f7-4c2d7a190947|0|.0