Hi there,
I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...
1. Is there a way to create a .csv file programmatically after runing the query?
2. How would I save the .csv file to a specified directory on the network?
TIA
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
View 6 Replies View RelatedHello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.
heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter
PUser.ControlID = "lblUser"
PUser.Name = "LoginName"
PUser.PropertyName = "Text"
PUser.Type = TypeCode.String
PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource
SQLDS_Login.ID = "SQLDS_Login"
SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?"
SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)"
SQLDS_Login.SelectParameters.Add(PUser)
SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
GridView1.DataSource = SQLDS_Login
GridView1.DataBind()
End Sub
When i run, i get this error message:
The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.
I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.
Can you help me with this?
Hi!
I am trying to write a script in VB.NET that will run a report that already exists in the system and export the results to my local machine. We are using MS Reporting Services to manage and manipulate the reports. So here's my question:
Is it possible to programmatically create a report in VB.NET based on an existing report? I noticed that crystal reports has a nice export method, but I have not been able to find anything similar for my situation. Basically I believe I would need some sort of reporting services object in .NET that would allow me to run the report and export the results. Does anyone know of such a structure, or if this is even possible? Thanks!!
Best regards,
Josh
Hi guys,
I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.
Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?
I am giving my code here too.
I will appreciate any kind of suggestion on this.
Code snippet:
public void CreatePackage()
{
string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;
Package pkg = new Package();
pkg.PackageType = DTSPackageType.DTSDesigner90;
ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);
ConnectionManager flatfileConnectionManager =
CreateFileConnection(pkg);
// creating the SQL Task for table creation
Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);
ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;
execSqlTask.Connection = oledbConnectionManager.Name;
execSqlTask.SqlStatementSource =
"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO";
// creating the Data flow task
Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");
TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;
MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;
// Put a precedence constraint between the tasks.
PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);
pcTasks.Value = DTSExecResult.Success;
pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;
// Now adding the data flow components
IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceDataFlowComponent.Name = "Source Data from Flat file";
// Here is the component class id for flat file source data
sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();
managedInstance.ProvideComponentProperties();
sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;
sourceDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);
managedInstance.AcquireConnections(null);
managedInstance.ReinitializeMetaData();
managedInstance.ReleaseConnections();
// Get the destination's default input and virtual input.
IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];
// Here I dont find any columns at all..why??
// Now adding the data flow components
IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
destinationDataFlowComponent.Name =
"Destination Oledb compoenent";
// Here is the component class id for Oledvb data
destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;
destinationDataFlowComponent.
RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);
// Set the custom properties.
managedOleInstance.SetComponentProperty("AccessMode", 2);
managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");
managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData(); // Throws exception
managedOleInstance.ReleaseConnections();
// Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],
destinationDataFlowComponent.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
DTSExecResult res = pkg.Execute();
}
public ConnectionManager CreateOLEDBConnection(Package p)
{
ConnectionManager ConMgr;
ConMgr = p.Connections.Add("OLEDB");
ConMgr.ConnectionString =
"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";
ConMgr.Name = "SSIS Connection Manager for Oledb";
ConMgr.Description = "OLE DB connection to the Test database.";
return ConMgr;
}
public ConnectionManager CreateFileConnection(Package p)
{
ConnectionManager connMgr;
connMgr = p.Connections.Add("FLATFILE");
connMgr.ConnectionString = @"D:MyCSVFile.csv";
connMgr.Name = "SSIS Connection Manager for Files";
connMgr.Description = "Flat File connection";
connMgr.Properties["Format"].SetValue(connMgr, "Delimited");
connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);
return connMgr;
}
And my CSV files is as follows
NAME, AGE, GENDER
Jon,52,MALE
Linda, 26, FEMALE
Thats all. Thanks.
I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.
Everything is good except for the WHERE section, which is created from values in a list box.
For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2
The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"
My employer wants this to be valid, but I am having a tough time coming up with a solution.
I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2
I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.
Any HELP?
Hi all
this is my code and i find it in microsoft's site
if i run it with sql server connection it works but if i try to use it with sql express it give me this error:
CREATE FILE encountered operating system error 5(access denied) while attempting to open or create the physical file 'c://mydatabase.mdf'
it seems as a permission error but it isn't. I have to set something in sql express while in sql server it is already setted?
static void WriteDB()
{
String str;
//sql server connection
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
//sql express connection
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, " +
"FILENAME = 'C:\MyDatabaseData.mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:\MyDatabaseLog.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
}
thanks
Marco
Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow? I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.
I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well. No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021
IDTSComponentMetaData90 scriptPropType = dataFlow.ComponentMetaDataCollection.New();
scriptPropType.Name = "Transform Property Type";
scriptPropType.ComponentClassID = "DTSTransform.ScriptComponent";
// have also tried scriptPropType.ComponentClassID =typeof(Microsoft.SqlServer.Dts.Pipeline.ScriptComponent).AssemblyQualifiedName;
scriptPropType.Description = "Transform Property Type";
CManagedComponentWrapper instance2 = scriptPropType.Instantiate();
instance2.ProvideComponentProperties();
Any help or examples would be greatly appreciated! Thanks!
Does anyone know how to create a Source Script Component programmatically. I can only seem to create a Transformation Script Component. I have this:
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent =
((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.ComponentClassID = app.PipelineComponentInfos["Script Component"].CreationName;
Is there anything I have to do extra to make it a source script component, seeing how it defaults to a transformation.
Below is C# code used to create a FuzzyLookup SSIS package programmatically. It does 95% of what I need it to. The only thing missing that I cannot figure out is how to take a Fuzzy Lookup Input column (OLE DB Output Column) and make it "pass through" the fuzzy lookup component to the OLE DB Destination. In the example below, that means I need the QuarantinedEmployeeId to make it into the destination.
Look in the "Test Dependencies" region below to get instructions and scripts used to set assembly references, create the sample tables used for this example, and insert test data.
Can anyone help me get past this last hurdle? You will see at the end of my Fuzzy Lookup region a bunch of commented out code that I've used to try to accomplish this last problem.
Code Block
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace CreateSsisPackage
{
public class TestFuzzyLookup
{
public static void Test()
{
#region Test Dependencies
// Assembly references:
// Microsoft.SqlServer.DTSPipelineWrap
// Microsoft.SQLServer.DTSRuntimeWrap
// Microsoft.SQLServer.ManagedDTS
// First create a database called TestFuzzyLookup
// Next, create tables:
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[EmployeeMatch](
// [RecordId] [int] IDENTITY(1,1) NOT NULL,
// [EmployeeId] [int] NOT NULL,
// [QuarantinedEmployeeId] [int] NOT NULL,
// [_Similarity] [real] NOT NULL,
// [_Confidence] [real] NOT NULL,
// CONSTRAINT [PK_EmployeeMatch] PRIMARY KEY CLUSTERED
//(
// [RecordId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
//GO
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[QuarantinedEmployee](
// [QuarantinedEmployeeId] [int] IDENTITY(1,1) NOT NULL,
// [QuarantinedEmployeeName] [varchar](50) NOT NULL,
// CONSTRAINT [PK_QuarantinedEmployee] PRIMARY KEY CLUSTERED
//(
// [QuarantinedEmployeeId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
//GO
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[Employee](
// [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
// [EmployeeName] [varchar](50) NOT NULL,
// CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
//(
// [EmployeeId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
// Next, insert test data
//insert into employee values ('John Doe')
//insert into employee values ('Jane Smith')
//insert into employee values ('Ryan Johnson')
//insert into quarantinedemployee values ('John Dole')
#endregion Test Dependencies
#region Create Package
// Create a new package
Package package = new Package();
package.Name = "FuzzyLookupTest";
// Add a Data Flow task
TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;
taskHost.Name = "Fuzzy Lookup";
IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;
// Get the pipeline's component metadata collection
IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection;
#endregion Create Package
#region Source
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the OLE DB Source Adapter
oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";
// Instantiate the OLE DB Source adapter
IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbSourceComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");
connectionManagerSource.Name = "OLEDBSource";
// Set the connection string
connectionManagerSource.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the OLE DB Source adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;
// Tell the OLE DB Source adapter to use the source table
oledbSourceComponent.SetComponentProperty("OpenRowset", "QuarantinedEmployee");
oledbSourceComponent.SetComponentProperty("AccessMode", 0);
// Set up the connection manager object
runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource);
// Establish the database connection
oledbSourceComponent.AcquireConnections(null);
// Set up the column metadata
oledbSourceComponent.ReinitializeMetaData();
// Release the database connection
oledbSourceComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionSource.ReleaseConnectionManager();
#endregion Source
#region Fuzzy Lookup
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 fuzzyLookupMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the Fuzzy Lookup object
fuzzyLookupMetadata.ComponentClassID = "DTSTransform.BestMatch.1";
// Instantiate
IDTSDesigntimeComponent90 fuzzyLookupComponent = fuzzyLookupMetadata.Instantiate();
// Ask the component to set up its component metadata object
fuzzyLookupComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerFuzzy = package.Connections.Add("OLEDB");
connectionManagerFuzzy.Name = "OLEDBFuzzy";
// Set the connection string
connectionManagerFuzzy.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the fuzzy lookup component's runtime connection
IDTSRuntimeConnection90 runtimeConnectionFuzzy = fuzzyLookupMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionFuzzy.ConnectionManagerID = connectionManagerFuzzy.ID;
// Set up the connection manager object
runtimeConnectionFuzzy.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFuzzy);
// Establish the database connection
fuzzyLookupComponent.AcquireConnections(null);
// Set up the external metadata column
fuzzyLookupComponent.ReinitializeMetaData();
// Release the database connection
fuzzyLookupComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionFuzzy.ReleaseConnectionManager();
// Get the standard output of the OLE DB Source adapter
IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];
// Get the input of the Fuzzy Lookup component
IDTSInput90 fuzzyInput = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"];
// Create a new path object
IDTSPath90 path = pipeline.PathCollection.New();
// Connect the source to Fuzzy Lookup
path.AttachPathAndPropagateNotifications(oledbSourceOutput, fuzzyInput);
// Get the output column collection for the OLE DB Source adapter
IDTSOutputColumnCollection90 oledbSourceOutputColumns = oledbSourceOutput.OutputColumnCollection;
// Get the external metadata column collection for the fuzzy lookup component
IDTSExternalMetadataColumnCollection90 externalMetadataColumns = fuzzyInput.ExternalMetadataColumnCollection;
// Get the virtual input for the fuzzy lookup component
IDTSVirtualInput90 virtualInput = fuzzyInput.GetVirtualInput();
// Loop through output columns and relate columns that will be fuzzy matched on
foreach (IDTSOutputColumn90 outputColumn in oledbSourceOutputColumns)
{
IDTSInputColumn90 col = fuzzyLookupComponent.SetUsageType(fuzzyInput.ID, virtualInput, outputColumn.LineageID, DTSUsageType.UT_READONLY);
if (outputColumn.Name == "QuarantinedEmployeeName")
{
// column name is one of the columns we'll match with
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinToReferenceColumn", "EmployeeName");
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "MinSimilarity", 0.6m);
// set to be fuzzy match (not exact match)
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinType", 2);
}
}
fuzzyLookupComponent.SetComponentProperty("MatchIndexOptions", 1);
fuzzyLookupComponent.SetComponentProperty("MaxOutputMatchesPerInput", 100);
fuzzyLookupComponent.SetComponentProperty("ReferenceTableName", "Employee");
fuzzyLookupComponent.SetComponentProperty("WarmCaches", true);
fuzzyLookupComponent.SetComponentProperty("MinSimilarity", 0.6);
IDTSOutput90 fuzzyLookupOutput = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"];
// add output columns that will simply pass through from the reference table (Employee)
IDTSOutputColumn90 outCol = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, "EmployeeId", "");
outCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
fuzzyLookupComponent.SetOutputColumnProperty(fuzzyLookupOutput.ID, outCol.ID, "CopyFromReferenceColumn", "EmployeeId");
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId)
//IDTSOutput90 sourceOutputCollection = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];
//IDTSOutputColumnCollection90 sourceOutputCols = sourceOutputCollection.OutputColumnCollection;
//foreach (IDTSOutputColumn90 outputColumn in sourceOutputCols)
//{
// if (outputColumn.Name == "QuarantinedEmployeeId")
// {
// IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, outputColumn.Name, "");
// col.SetDataTypeProperties(
// outputColumn.DataType, outputColumn.Length, outputColumn.Precision, outputColumn.Scale, outputColumn.CodePage);
// //fuzzyLookupComponent.SetOutputColumnProperty(
// // fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", outputColumn.LineageID);
// }
//}
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId)
//IDTSInput90 fuzzyInputCollection = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"];
//IDTSInputColumnCollection90 fuzzyInputCols = fuzzyInputCollection.InputColumnCollection;
//foreach (IDTSInputColumn90 inputColumn in fuzzyInputCols)
//{
// if (inputColumn.Name == "QuarantinedEmployeeId")
// {
// IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, inputColumn.Name, "");
// col.SetDataTypeProperties(
// inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
// fuzzyLookupComponent.SetOutputColumnProperty(
// fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", inputColumn.LineageID);
// }
//}
#endregion Fuzzy Lookup
#region Destination
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the OLE DB Destination Adapter
oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination";
// Instantiate the OLE DB Destination adapter
IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbDestinationComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB");
connectionManagerDestination.Name = "OLEDBDestination";
// Set the connection string
connectionManagerDestination.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the OLE DBDestination adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID;
// Tell the OLE DB Destination adapter to use the destination table
oledbDestinationComponent.SetComponentProperty("OpenRowset", "EmployeeMatch");
oledbDestinationComponent.SetComponentProperty("AccessMode", 0);
// Set up the connection manager object
runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination);
// Establish the database connection
oledbDestinationComponent.AcquireConnections(null);
// Set up the external metadata column
oledbDestinationComponent.ReinitializeMetaData();
// Release the database connection
oledbDestinationComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionDestination.ReleaseConnectionManager();
// Get the standard output of the fuzzy lookup componenet
IDTSOutput90 fuzzyLookupOutputCollection = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"];
// Get the input of the OLE DB Destination adapter
IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"];
// Create a new path object
IDTSPath90 ssisPath = pipeline.PathCollection.New();
// Connect the source and destination adapters
ssisPath.AttachPathAndPropagateNotifications(fuzzyLookupOutputCollection, oledbDestinationInput);
// Get the output column collection for the OLE DB Source adapter
IDTSOutputColumnCollection90 fuzzyLookupOutputColumns = fuzzyLookupOutputCollection.OutputColumnCollection;
// Get the external metadata column collection for the OLE DB Destination adapter
IDTSExternalMetadataColumnCollection90 externalMetadataCols = oledbDestinationInput.ExternalMetadataColumnCollection;
// Get the virtual input for the OLE DB Destination adapter.
IDTSVirtualInput90 vInput = oledbDestinationInput.GetVirtualInput();
// Loop through our output columns
foreach (IDTSOutputColumn90 outputColumn in fuzzyLookupOutputColumns)
{
// Add a new input column
IDTSInputColumn90 inputColumn = oledbDestinationComponent.SetUsageType(oledbDestinationInput.ID,
vInput, outputColumn.LineageID, DTSUsageType.UT_READONLY);
// Get the external metadata column from the OLE DB Destination
// using the output column's name
IDTSExternalMetadataColumn90 externalMetadataColumn = externalMetadataCols[outputColumn.Name];
// Map the new input column to its corresponding external metadata column.
oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, inputColumn.ID, externalMetadataColumn.ID);
}
#endregion Destination
// Save the package
Application application = new Application();
application.SaveToXml(@"c:TempTestFuzzyLookup.dtsx", package, null);
}
}
}
Hi All,
I have been stuck with this problem since few days, need help regarding the same. I am enclosing the problem description and possible solutions that I have found.
Can anyone please help me out here?
Thanks and regards,
Virat
Problem Description:
I have a requirement for which I have created a data driven subscription in
SQL Server 2005, the whole thing works like this:
I have a report on Report Server which executes a stored procedure to get
its parameters; then it calls another stored procedure to get data for the
report; then it creates the report and copies it to a file share. This is
done using data driven subscription and the time set for repeating this
process is 5 minutes.
You can assume that following are working fine:
1. I have deployed the report on the Report Manager (Uploaded the report,
created a data source, linked the report to data source) - manually, the
report works fine.
2. Created a data driven subscription.
3. The data driven subscription calls a stored procedure, say
GetReportParameters which returns all the parameters required for the report
to execute.
4. The Report Manager executes the report by calling a stored procedure, say
GetReportData with the parameters provided by GetReportParameters stored
procedure; after it has generated the report file (PDF) is copied to a file
share.
For each row that GetReportParameters stored procedure returns a report (PDF
file) will be created and copied to file share.
Now, my question is
1. How to I get a notification that this file was successfully created
or an error occurred?
2. The only message that reporting service shows on 'Report Manager >
My Subscriptions' is something like "Done: 5 processed of 10 total; 2
errors."
How do I find out which record was processed successfully and which ones
resulted in an error?
Based on above results (success or failure), I have to perform further
operations.
Solutions or Work around that I have found:
1. Create a windows service which will monitor the file share folder
and look for the file name (each record has a unique file name) for the
reports that were picked up for PDF creation. If the file is not found, this
service will report an error. Now, there's a glitch there; if a report takes
very long time to execute it will also be reported as error (i.e. when this
service checks for the PDF file, the report was currently being generated).
So, I can't go with this solution.
2. I have also looked at following tables on ReportServer database:
a. Catalog - information regarding all the reports, folders, data
source information, etc.
b. Subscriptions - all the subscriptions information.
c. ExecutionLog - information regarding execution of the subscriptions
and the also manual execution of reports.
d. Notifications - information regarding the errors that occurred
during subscription execution.
For this solution, I was thinking of doing a windows service which will
monitor these tables and do further operations as required.
This looks like most feasible solution so far.
3. Third option is to look at DeliveryExtensions but in that case I
will have to manually call SSRS APIs and will have to manage report
invocation and subscription information. What is your opinion on this?
My environment details:
Windows XP SP2
SQL Server 2005
Reporting Services 2005
Please let me know if I am missing something somewhere...
Hello,
I would like programmatically generate rdl files with image.
I am using this XML Text Writer to create the rdl file.
But i have no idea how can i carryon on this task.
Can anyone help me in this topic?
Thanks.
Hello,
I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value.
The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it.
I do not want to have to maintain 2 DFTs. How can I get around this problem?
Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do that. Does not necessarily HAVE to be a pure programmatic approach.
ANY help would be greatly appreciated!
Feel free to email me at ccorbin@topcoder.com with any questions, or leave me some good news here :)
THANKS!
Chris Corbin
Software Developer
TopCoder Inc.
Hi,
i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got
Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.
how can i rectify this error? or how can i ignore the identity column in coding.
thanks & regards,
sivani
I am programmatically creating a package in c#. I need this package to contain a flat file connection manager. I have been successfully able to create the connection manager with the following code:
Package p = new Package(); //New package
ConnectionManager cm = p.Connections.Add("FLATFILE"); //Add a flat file connection manager
cm.ConnectionString = "C:DevmyTestFile.txt"; //Set up the connection string
At this point, I want to configure the flat file specific properties of the connection manager (RowDelimiter, HeaderRowDelimiter, etc.). The problem is that the properties collection of the ConnectionManager object is read only. I think I could access the inner object of the connection manager and set the properties in the following way:
ConnectionManagerFlatFileClass ffClass = (ConnectionManagerFlatFileClass) cm.InnerObject; //Get the inner object
ffClass .RowDelimiter = "{LF}"; //Set the properties here
Even if this would work, I do not like the solution b/c the BOL states that the ConnectionManagerFlatFileClass "supports the SQL Server 2005 infrastructure and is not intended to be used directly from your code". Does anyone know the "right" way to set these properties?
Many thanks in advance!
David
Hello,
I'm looking for Programmatically genration of Report Model .smdl file.
Please guide me or suggest some links so that ui can refer same.
Is there any smdl genrator or any classes for the same...?
Thanks in advance.
Sandip
I was just doing a table import task (right click database name/Tasks/Import Data), not knowing my boss had just loaded the same file. it did not warn me that the table currently existed. It just appended the same information to the same table, doubling it. I fixed that one, but, it seems that I might have done this myself in the last couple of weeks, and I'd like to find that table, and there have been a LOT of table loads.
I'm thinking I could get the difference between tables by comparing:
select distinct count(*) from tblname
against
select count(*) from tblname
But how do I incorporate this into some sort of proc that will go through all the tables and let me know where the issue is? I'm swamped and don't have the time to go through each table manually.
I have code that shows me row counts, and have been able to eliminate a few tables from contention, as they are loading monthly data that should only increase minorly month to month, so, no double jumps there.
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";
}
}
}
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Can anybody run me through the process to create a CSV file from an SQLexpress database using BCP.exe ?
Or is there a simpler way to create a CSV for that matter?
Really important and would be much appreciated.
Hi,
If anyone knows ,please tell how to store all fields with comma seperation by select statement into a text file, without using Extended Stored Procedure.
Thanks in advance.
I have a system which has several different dbases involved, and have created an update procedure within it all. It all works fine, but as a refrenence point I want to create a log file,each time the procedure is run.
This file will have useful info such as no of transactions added, no of product row n customer rows, any unmatching entries and any exception records. What I dont know is how do I create such a file?
I was thinking maybe I could save the info into a table, and then somehow transfer this into a txt file? The txt file will be replaced each time the stored procedure is run.... Does any1 know any links or ideas that might help me?...I never does anything lik this b4 but I have seen it done.....
Thanks in advance!
Hi All!
I am still trying to understand all the differences between SQL 2000 and 2005. This question probably has a really easy answer, but I just don't know it. I need to create a DTS routine that creates a file from a table. The catch is, it needs to have a header segment, data segment, and footer segment. In SQL 2000, I would have created activex script to create this file. Since SQL 2005 is going away from activex script I am at a loss.
The file will be in this format.
Header
Data
Data
Data
Data
Data
Data
Footer
Basically output header from table... loop through table for data... and output footer from table.
If anyone knows how to do this, I would appreciate the help.
Thanks.
Danielle
Hi,
We are using VS 2005 with SQL Server Mobile Edition. We created the .sdf file on the desktop and added this file to our VS project and set the Build Action to content. Next we deployed our app on Symbol PDA which has Windows CE 5.0 as the OS. We followed the following steps:
1. Deploy the app.
2. Install .NET CF 2.0
3. System_SR_ENU.CAB
4. sqlce30.wce5.armv4i.CAB
5. sqlce30.dev.ENU.wce5.armv4i.CAB
6. sqlce30.repl.wce5.armv4i.CAB
Now I want to create a new .sdf file in PDA. So I open QA3.0 and type the create database command. But I get error:
You need to have a database connection opened in order to run this operation.
What could be the issue?
Regards,
Vilas
Hi can I know how to create a dbml file for northwind database. Please note I am using sqlserver 2008
View 4 Replies View RelatedHi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
Dim Cm As SqlCommand
Dim dr As SqlDataReader
Dim FileName As String = Guid.NewGuid.ToString & ".csv"
Dim FilePath As String = Server.MapPath("") & "CSVFiles" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
Dim Line As String
Dim lItem As System.Xml.XmlElement
Dim i As Int16
If ValidatePage() Then
Cm = New SqlCommand(BuildQuery, Conn)
Conn.Open()
dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument()
lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes
Select Case lItem.InnerText
Case "chkPRId"
Line &= "PR Id,"
Case "chkDateIssued"
Line &= "Date Issued,"
Case "chkOriginator"
Line &= "Originator,"
Case "chkBuyer"
Line &= "Buyer,"
Case "chkVendor"
Line &= "Vendor,"
Case "chkCostCode"
Line &= "Cost Code,"
Case "chkOracleRef"
Line &= "Oracle Reference,"
Case "chkTotal"
Line &= "Total,"
End Select
Next
Line = Line.Substring(0, Line.Length - 1)
sw.WriteLine(Line)
Line = ""
While dr.Read
For i = 0 To dr.FieldCount - 1
Line = Line & dr(i) & ","
Next
sw.WriteLine(Line)
Line = ""
End While
dr.Close()
Conn.Close()
sw.Close()
fs.Close()
Response.Redirect(FilePath)
End If
ResetPage()
PopulateListBoxFromXML()
how do you create a database from an .mdf file?
View 2 Replies View RelatedHi,
How can we create a new D/B from a D/B Backup file (DataBase.Bak).
Thanks,
Princy
Hi,
I am new to SqlServer (I am using 7.0) and I have just recieved a database.bak file from one of our clients and I want to create a new D/B from this back up file ,I have already installed the MS SqlServer. Can you tell me how to do this.
Thanks,
Princy
I am using this bcp out construct and it works fine except that if the query does not return values it bcp's out a file anyhow. This is not wanted and I am looking for a work around.
SELECT @Year = CONVERT(varchar(4), @trxYearMonthStart, 120)
SELECT @Month = RIGHT(CONVERT(varchar(7), @trxYearMonthStart, 120),2)
SELECT @cmd = 'BCP "SELECT * FROM ' + @TableToBeCleaned + ''
SELECT @cmd = @cmd + ' WHERE '+ @SelectedColumn + ' BETWEEN '
print @cmd
SELECT @cmd = @cmd + '''' + CONVERT(varchar(10),@trxYearMonthStart,120) + ''' and ''' + CONVERT(varchar(10),@trxYearMonthEnd,120) + ''''
print @cmd
SELECT @cmd = @cmd + 'AND NOT EXISTS (Select * from DBCleanerHist Where TableName = ''' + @TableToBeCleaned + ''' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
print @cmd
SELECT @cmd = @cmd + ' " QUERYOUT ' + @DBCleanerBackUpPath+'' +@TableToBeCleaned +'_'+ @Year + '_' + @Month + '.txt '
SELECT @cmd = @cmd + ' -c -C1250 -S -Uopms -Psmpo'
EXEC master.dbo.xp_cmdshell @cmd
The subquery checks first in DBCleanerHist if a file already has been extracted onto hd and if so do not create an empty file and overwrite an existing file.
thanks
mipo
Bit of a SQL newbie, having taken over a support role for a piece of software that my firm sells. It is basically a DB that runs on MSDE or SQL Server in either desktop or enterprise scenarios.
We do sell our software to clients who may or may not already have SQL on their machines. During installation, it searches for sqlservr.exe for existing SQL installations and if it finds nothing, will then install MSDE 1.0 to house the db files.
My question is this -
I can manually create a db or manually attach an existing db through sql query analyser where there is a full-blown SQL installation. Is there any way I can do the same where there is only MSDE installed? The simple solution, I guess, would be to download one of the freeware or shareware MSDE "Enterprise Managers", but this won't always be suitable for a client installation.
Many thanks for your assistance all.
:cool:
how can i create a new database with a .mdf file ? in mS SQL 2000
thank you