Has Anyone Created A Thesaurus File?
Feb 25, 2008
Has anyone created a Thesaurus file that is using it? Specifically, has anyone set up a First Name thesaurus file and is it possible to get a hold of it. Why reinvent the wheel if someone has already done this. I am creating a database that needs to return records for fuzzy matches on first names. I don't know what all forms of the names can be such as Robert, Bob, Bobby, Bert or Sue, Suzie, Susie, Susan, Suzanne, etc. and I am betting someone has created something already that can be a starting point at least.
What about the Thesaurus Word currently uses? Does anyone know what the physical file name and location is of the thesaurus file Word uses?
Any help or direction is appreciated.
Thanks
Paul
View 1 Replies
ADVERTISEMENT
Jan 30, 2012
I remembered that the Thesaurus that comes with SQL Server is essentially empty.Is there a Thesaurus that can be downloaded for US English so I won't have to sit there with Roget's doing data entry for a few weeks?
View 9 Replies
View Related
Aug 8, 2007
Hi! I am moving this from the Transact SQL forum as I am not getting a reply. Hope it's OK!
Hello: I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
View 1 Replies
View Related
Jun 23, 2015
I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file. I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the data from Source to Destination.
View 5 Replies
View Related
Jul 24, 2015
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
View 2 Replies
View Related
Nov 5, 2007
Hi all,
I have been trying to extract table data into file but its not getting extracted. I am using following command to run.
exec spGetIPAddress @ip = @servername output
select @SQL = 'bcp '
+ char(34)
+ ' select Field from ['
+ @DatabaseName
+ '].[dbo].Temp_ExtractSR39'
+ ' order by Field'
+ char(34)
+ ' queryout '
+ @RenamePathAndFile
+ ' -T -c -CACP -S'
+ @servername
exec master..xp_cmdshell @SQL, no_output
Is there any setting need to be done at database level? or any setting for Sql server?
Thanks in adavance,
Anshu
View 3 Replies
View Related
May 11, 2006
I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?
Any inputs will be appreciated.
M.Shah
View 3 Replies
View Related
Jun 11, 2006
I am getting a return code of 12 (""File not created"") from sp_trace_create. I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account. SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it. The following is the relevant portion of what I am attempting. DECLARE @RC int, @TraceId intExec @RC = sp_trace_create @TraceId OUTPUT, 0, N'C:TraceFile' I have tried other paths that also do not work.
View 1 Replies
View Related
Apr 3, 2008
Hi Guys,
Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Could someone help me there?
View 2 Replies
View Related
Mar 27, 2007
How dow you schedule a file(.dtsx)(I think it is a package) that was created in the SQL Server BI Design Studio to run? I can locate the file I created in SQL Server Management Studio and see it in the right hand pane and even edit it. I can run it in the Design Studio, but cant see how to do that in Management Studio nor how to schedule it.
This is my first attempt at recreating a DTS package using SQL Server tools instead of the Legacy conversion wizard, and I think my brain has overheated. I just cant find it on the Management Screen or the Design. I am also not searching correctly anymore on books online because I cannot find it.
Thanks for pointing me to where I need to go or do.
Tracey
View 5 Replies
View Related
Aug 15, 2007
Hi there,
Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.
Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.
Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.
http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?
Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!
View 5 Replies
View Related
Apr 9, 2015
I have setup a custom Thesaurus for use with SQL Full Text Index (2008). For example I have the following terms :
<expansion>
<sub>400v</sub>
<sub>400volt</sub>
<sub>400 volt</sub>
<sub>415v</sub>
<sub>415volt</sub>
<sub>415 volt</sub>
<sub>3 phase</sub>
<sub>3phase</sub>
<sub>three phase</sub>
</expansion>
When I issue a search for "415 volt" I am getting matches on records with "3" in the data, not just "3 phase" - example query below :
select tblp.productid, tblp.productcode, tblp.description, KEY_TBL1.RANK
from tblProduct tblp
INNER JOIN FREETEXTTABLE(tblProduct, description, '"415 volt"') AS KEY_TBL1 ON tblP.ProductID = KEY_TBL1.[KEY]
ORDER BY KEY_TBL1.RANK DESC
It is returning items that don't have the word "phase" in at all and just have "3"! I have had to take all numbers out of the stop list as we sell a lot of technical items that will be searched for by size / voltage etc.
View 0 Replies
View Related
Oct 5, 2006
Dear All,
I am not able to let thesaurus in SQL Server 2005 works.
My full indexed table has 4 records as follows:
tony
roby
mary
antony
The following is my tsENU.xml file:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<expansion>
<sub>Tony</sub>
<sub>Roby</sub>
<sub>Mary</sub>
</expansion>
</thesaurus>
</XML>
After I execute the following query:
SELECT * FROM Person WHERE CONTAINS(*,'FORMSOF(Thesaurus,"Tony")')
the result is only the record containing 'Tony' but I need Tony, Roby and Mary to be treated as synonimus and for that reason the result should be the 3 records containing all the 3 names.
Which is my mistake?
thanks and regards
Buzz
View 19 Replies
View Related
Aug 13, 2007
Hello: Please let me know if another forum is a better place to post! I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
View 7 Replies
View Related
May 27, 2007
Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated
View 4 Replies
View Related
Apr 11, 2005
I'm trying to fire a DTS package through a stored procedure.
After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.
How would I use the DTS file to update my data.
Thanks,
RB
View 1 Replies
View Related
May 9, 2000
I am trying to restore a file that was created using Database Maintanence Wizard on a server that is now totally crashed.
I want to use T-SQL to restore the physical file to a new name and location.
Using the backup command, I get the error that I must use the with move option??
What are the required steps to restore from a
physical file (with a bak extention)???
I do not know the original database/log file names. Any advice?
Thanks
Ziggy
View 1 Replies
View Related
Aug 13, 2015
i am trying to run an exe using t-sql command and even through job, but it is giving me issues. But if i double click on the exe it runs fine.
For example i created a bat file just to make a directory - mkdir d:example
double clicking it, a folder is created BUt through
EXEC xp_cmdshell "D:ew.bat"
it gives me error -
'D:ew.bat' is not recognized as an internal or external command,
and when i do with the job with command - @command=N'idhimahajanD$ew.bat',
it gives an error on execution - Executed as user: NT ServiceSQLAgent$PAXAS. The process could not be created for step 1 of job 0x2FCDDE36F160CD4688D2ACE1D748A99E (reason: Access is denied). The step failed.
View 3 Replies
View Related
Aug 26, 2007
I am trying to get the full-text search thesaurus to work in Sql
Server 2005 Express edition. I live in the USA so I assume the
tx.ENU.xml is the appropriate file to modify. I used Notepad to
modify the tx.ENU.xml file as follows:<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>littre</sub>
<sub>leydig</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
</thesaurus>
</XML> I closed Management Studio and reopened to allow MSFTESQL service to restart. Then I ran these queries:SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'littre') *** returned 3 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'leydig') *** returned 169 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, ' FORMSOF(THESAURUS, littre) ') *** returned 6 rows ***
Thus the Thesaurus does not seem to be working since it should
have returned at least 169 rows. I rebooted my entire system to
make sure Sql Server is starting fresh. Any help in sorting this out will be greatly appreciated.
View 1 Replies
View Related
Nov 13, 2007
Does anyone know where I can get a Thesaurus (or list) of People's Names and their relationships?
For Example:
Bill = Billy = Billie = Will = Willie = William
Sue = Susie = Susan = Suzanne
I can build the needed xml file to become a Thesaurus for MSSQL Full Text Searching. But I can't find a list of related people names anywhere.
Any help would be great! Thanks!
Lyam
View 3 Replies
View Related
Dec 13, 2001
Hi,
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
Thanks for the help!
View 1 Replies
View Related
Feb 17, 2012
Provided that we have 2 type of tables, the first type of tables with schema schema_a and the second type with schema schema_b.Also 2 filegroups apart from primary created: filegroup_a and filegroup_b.How can I configure so that when table with schema schema_a created, it can be put on filegroup filegroup_a automatically and when table with schema schema_b created, it can be put on filegroup filegroup_b automatically?
View 1 Replies
View Related
Feb 8, 2007
NOONE ASKED FOR THIS!!! why the heck is BACKUP CERTIFICATE altering the file's ACLs - it's just a PUBLIC CERT I'm exporting!!
in additions MS doesn't seem able to add the ability to re-inherit file-rights from parent objects via the command-shell.
I export the public-key for a reason! I want to import it on another machine (automatically) to establish a trust. But this freaking "feature" is costing me HOURS of research on how to get rid of these ACLs on a public certificate!!!
argh!!!
sorry, I really had to get this out!
why not at least give us a switch we can set in BACKUP CERTIFICATE command to stop this from happening?
is there a work-around? now I need to activate
EXEC sp_configure 'show advanced options', 1
and
EXEC sp_configure 'xp_cmdshell', 1
just to be able to issue a (not working) DOS command cacls... to try to fix the "feature".
there's got to be a better way!!!
also - why can't there be a switch to force overwriting the target file, if it already exists? It's a BIG PAIN having to do lots of other stuff, just to be able to "backup" the certificate!
You gave us "xp_FileExist", but no xp_FileDelete with similar parameters!! again, I need to issue DOS COMMANDS just to do stuff I would not have to if someone at the levers was actually activating the back-brain!
PLEASE give me some hints on how MS recommends to solve these features!
View 6 Replies
View Related
Nov 13, 2007
I love SQL Server Reporting Services (SSRS), but it doesnt let me name the tabs when exporting to Excel. The feature is well documented as not being available.
So I figured I can use the cell(1,1) on each tab to contain the tab name, and have an SSIS job poll a file share, open the file, read the cell, rename tabs, rename the file, and then email out the file. Polling the directory and emailing is a piece of cake, but I can't find any way to rename a tab without having Excel loaded on the SERVER. Not to mention instantiating an entire instance of Excel on the SERVER every time I want to do this.
I want to leverage SSRS, and preserve the formatting of the excel file, which is why I don't just dump the data in with SSIS to begin with.
Anybody have any ideas?
View 4 Replies
View Related
May 20, 2006
Hi,
Ive been trying to add keywords to the thesaurus used for SQL Server 2005 Full Text services but do not seems to get any improved results.
For example, I have a catalogue of article titles which contains titles such as CRM and then some others use the words Customer Relationship Management, the ideal solution I would have thought was SQL Servers thesaurus support, Ive been though and added the values:
<expansion>
<sub>customer relationship management</sub>
<sub>crm</sub>
</expansion>
to the txGlobal.xml file in the SQL Servers FTData directory, I then amended by test query to use this clause:
FORMSOF(THESAURUS,"CRM")
The idea being that when I search for CRM it will bring back articles with Customer Relationship Management also. However Im just getting results for the same results as for the word 'CRM' and no expansion is taking place it would seem.
Would anyone be able to clarify what the best approach to making this work would be:
Do I need to restart the SQL Server FullText service after making any changes to the XML files?
(Done this, no sucess)
Do I need to restart the SQL Server service after making any changes to the XML files?
(Done this, no sucess)
Do you need to re-populate the full text catalogues after making a change the XML files?
(Done this, no sucess)
If this syntax correct when trying to utilise the thesaurus?
CONTAINSTABLE (Article_Metadata, *, FORMSOF(THESAURUS,"CRM") )
(It doesn;t give any errors, just no improved results over:
CONTAINSTABLE (Article_Metadata, *, "CRM" )
Any help would be appreciated.
Thanks
Ed
View 3 Replies
View Related
Feb 26, 2007
Hello,
I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.
My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible?
Regards
RH
View 4 Replies
View Related
Apr 4, 2006
Hi
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
thanks
Satya
View 1 Replies
View Related
Jun 29, 2015
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
View 3 Replies
View Related
Jan 29, 2008
I need the file created by my BCP QUERYOUT command I'm executing from XP_CmdShell in Transact-SQL to have a specific OWNER. I've tried using the sp_xp_cmdshell_proxy_account to setup the correct owner. The owner is always SERVERNAMEAdministrators.
thanks,
jim
View 1 Replies
View Related
Sep 23, 2007
Hi everyone,
I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!
I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.
I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.
I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!
I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.
Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!
Could anyone please provide insight as to what my issue may be?
Thanks in advance!
Code Snippet
using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace SumCodeApp
{
class SumCodeApp
{
// Variables.
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataFlowTask;
private List<String> srcColumns;
int file_count;
SqlConnection connection;
String folder_path;
String username;
String password;
String DB_server;
String catalog;
// Default Constructor.
public SumCodeApp()
{
}
// Constructor taking in user info.
public SumCodeApp(String folder_path, String username, String password,
String DB_server, String catalog)
{
this.folder_path = folder_path;
this.username = username;
this.password = password;
this.DB_server = DB_server;
this.catalog = catalog;
}
private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "SumCode Package";
package.Description = "Upload the SumCode files to the database";
package.DelayValidation = true;
package.PackageType = DTSPackageType.DTSDesigner90;
}
private void CreateFlatFileConnection()
{
String flatFileName = ". 1105.csv";
String flatFileMoniker = "FLATFILE";
flatFileConnectionManager = package.Connections.Add(flatFileMoniker);
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = String.Concat("SSIS Connection Manager");
flatFileConnectionManager.ConnectionString = flatFileName;
// Set some common properties of the connection manager object.
//flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, """);
flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, "
");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
// Create the source columns into the connection manager.
CreateSourceColumns();
}
private void CreateSourceColumns()
{
// Get the actual connection manager instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;
// Fill the source column collection.
srcColumns = new List<String>();
srcColumns.Add("CreateDate");
srcColumns.Add("CorpID");
srcColumns.Add("SumCodeID");
srcColumns.Add("Priority");
srcColumns.Add("SumCodeAbv");
srcColumns.Add("SumCodeDesc");
srcColumns.Add("SumCodeGroupID");
foreach (String colName in srcColumns)
{
column = flatFileConnection.Columns.Add();
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
//column.ColumnDelimiter = "
";
column.ColumnDelimiter = "{CR}{LF}";
else
//column.ColumnDelimiter = ",";
column.ColumnDelimiter = "Comma {,}";
name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName;
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
private void CreateDestinationDatabaseConnection()
{
destinationDatabaseConnectionManager = package.Connections.Add("OLEDB");
destinationDatabaseConnectionManager.Name = "Destination Connection - SumCodeCorpGroup";
destinationDatabaseConnectionManager.Description = "Connection to the temporary table SumCodCorpGroup";
destinationDatabaseConnectionManager.ConnectionString = "Data Source=DIVWL-356KCB1;Initial Catalog=SumCode;Provider=SQLOLEDB;Persist Security Info=True;User ID=sum;Password=code";
}
public class Column
{
private String name;
private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType;
private int length;
private int precision;
private int scale;
private int codePage = 0;
public String Name
{
get { return name; }
set { name = value; }
}
public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType
{
get { return dataType; }
set { dataType = value; }
}
public int Length
{
get { return length; }
set { length = value; }
}
public int Precision
{
get { return precision; }
set { precision = value; }
}
public int Scale
{
get { return scale; }
set { scale = value; }
}
public int CodePage
{
get { return codePage; }
set { codePage = value; }
}
}
private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("CreateDate"))
{
cl.Name = "CreateDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (
sourceColumnName.Contains("CorpID"))
{
cl.Name = "CorpID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeID"))
{
cl.Name = "SumCodeID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Priority"))
{
cl.Name = "Priority";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeAbv"))
{
cl.Name = "SumCodeAbv";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeDesc"))
{
cl.Name = "SumCodeDesc";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeGroupID"))
{
cl.Name = "SumCodeGroupID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
return cl;
}
private void CreateDataFlowTask()
{
String dataFlowTaskMoniker = "DTS.Pipeline";
dataFlowTask = package.Executables.Add(dataFlowTaskMoniker);
}
public void ImportFile(String directory_path)
{
// Create the package.
CreatePackage();
// Create Flat File Source Connection.
CreateFlatFileConnection();
// Create Database Destination Connection.
CreateDestinationDatabaseConnection();
// Create DataFlowTask.
CreateDataFlowTask();
// Create the DataFlowTask
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = "DTSAdapter.FlatFileSource";
PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
managedFlatFileInstance.ProvideComponentProperties();
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatFileConnectionManager);
managedFlatFileInstance.AcquireConnections(null);
managedFlatFileInstance.ReinitializeMetaData();
Dictionary<String, int> outputColumnLineageIDs = new Dictionary<String, int>();
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn = null;
foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in sourceComponent.OutputCollection[0].OutputColumnCollection)
{
exOutColumn = sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
managedFlatFileInstance.MapOutputColumn(sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
managedFlatFileInstance.ReleaseConnections();
String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString();
String b = sourceComponent.OutputCollection[0].Name;
String c = sourceComponent.OutputCollection[0].Description;
String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();
// Create DataFlowTask Destination Component.
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
destinationComponent.Name = "OLEDB SQL Connection";
destinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";
PipeLineWrapper.CManagedComponentWrapper managedOleInstance = destinationComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();
// Create a path and attach the output of the source to the input of the destination.
PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);
managedOleInstance.SetComponentProperty("AccessMode", 0);
managedOleInstance.SetComponentProperty("OpenRowset", "[SumCode].[dbo].[SumCodeCorpGroup]");
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252);
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions","TABLOCK,CHECK_CONSTRAINTS");
managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData();
PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
//if (outputColumnLineageIDs.ContainsKey(vColumn.LineageID.ToString()))
//{
managedOleInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType.UT_READONLY);
//}
}
List<String> tmp = new List<String>();
foreach(PipeLineWrapper.IDTSInputColumn90 inc in destinationComponent.InputCollection[0].InputColumnCollection)
{
tmp.Add(inc.Name);
}
PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in destinationComponent.InputCollection[0].InputColumnCollection)
{
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
String destName = mappedColumn.Name;
exColumn.Name = destName;
managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
managedOleInstance.ReleaseConnections();
DTSExecResult result = package.Execute();
a = "0";
}
}
}
View 3 Replies
View Related
Jul 17, 2007
Hi,
I needed to recreate/publish a DDL and data file(of a database) that was produced using Database Publishing Wizard. The size of the file is 4G. I receive the following error when I try to open it up in the new query window of sql server 2005 (sp2)for execution.
"The operation could not be completed. Not enough storage is available to complete this operation."
I know my server has 829G of free storage space available. i receive no errors when I open up a smaller file also created by database publishing wizard.
What possibly is causing this?
Thanks in advance
View 3 Replies
View Related
Jul 6, 2015
created a very basic flow in SSIS: extracted table data through ole db connection, added multicast and as end result i created a flat file destination (with .txt file) and a ole db destination.
My question is; how can i delete the .txt file before executing the flow again? Want to avoid that the .txt file has duplicated rows after a second execution of the flow. Is it possible to use scd component or is this way to complicated? A for each loop?
i need a similar solution for the data that will be transported through the ole db destination task....
View 2 Replies
View Related
Sep 17, 2007
SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data in MS Excel?
I can see that under
C:Program FilesMicrosoft SQL Server Compact Editionv3.5
I've one DLL called
sqlceoledb35.dll
But I don't have any oledb driver listed when creating an UDL file?
How can I consume the data in other apps that are not .NET and developed in house them?
Thanks, AM.
View 13 Replies
View Related
