Run SSIS Programmatically In 32-bit Mode On 64-bit Server
Jan 15, 2008
Environment:
Windows Server 2003 64-bit
SSIS 32-bit & 64-bit installed
.NET 2.0 application (Platform target x86) which executes an SSIS package programatically (using Microsoft.SQLServer.ManagedDTS.dll)
The issue I am having is that we connect to a Sybase server and even though the .NET app is executing in 32-bit mode, the SSIS pack appears to try load Sybase 64-bit OLEDB drivers.
The error I receive is DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
When I run on a 32-bit machine all runs fine or if I run using the 32-bit version of DTEXEC the package also runs fine.
Any ideas on how I can force SSIS to use 32-bit drivers when run on 64-bit server programatically?
View 5 Replies
ADVERTISEMENT
Mar 10, 2015
Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.In SSMS window 1 I run the following code:
use master
go
alter database test
set single_user
with rollback immediate
[code]....
Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.Any other solutions to catch this SPID with a script?
View 5 Replies
View Related
Feb 4, 2015
I have a package that I developed in VS2012 (using the SQL Data Tools component) which collects data from a DBF file using the VFPOLEDB provider, and puts it into a database on a SQL Server 2012 X64 server. The project containing the package has the DebugOption of Run64BitRuntime set to false. I have imported this package to the SSIS Package Store of a test and live server (identical setups). The VFPOLEDB provider is installed on both, and I can see in the registry of both machines that it's there for 32bit runtimes.
The package runs fine on the test machine, but fails on the live machine. The live instance of SQL does not appear to recognize the 32bit VFPOLEDB provider that is installed.The only difference in the instances of SQL is that the live environment has an Integration Service Catalog set up, where as the test does not. Looking at the logs of the servers, when the live starts, it runs sp_ssis_startup, and then logs messages about unsafe assemblies being loaded in. This SP is not run on the test environment because there is no catalog.
The jobs I create have the flag set to use the 32bit runtime, but I feel that the SSIS Catalog is having an issue with the VFPOLEDB I am using, and not loading it.
View 9 Replies
View Related
Jun 21, 2007
We would like to deploy SSIS packages as an ETL Tool to an appserver that does not have SQL Server 2005 installed. Is this possible, or does it HAVE to be executed on the server with SQL 2005 installed?
Kevin
View 4 Replies
View Related
Jul 25, 2014
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
View 1 Replies
View Related
Mar 26, 2005
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
View 3 Replies
View Related
Jan 4, 2007
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.
View 4 Replies
View Related
Nov 28, 2007
Hi,
I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library.
We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.
Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#.
Any help with white paper or sample code would be appreciated.
Thanks in advance
View 6 Replies
View Related
Jan 21, 2008
I cannot seem to get my code to work. I have read all of the samples I could find (about 30 of them), a chapter on SSIS programming in a SQL Server 2005 book, and all of the MS online documentation. I would like to programmatically create an SSIS package that can load data from a flat file source into an oledb destination. I am not doing any transformations.
For the test, my source file has now degenerated to two columns. The entire file looks like this:
hello,goodbye
here,there
That's it, two rows, two column, no headers.
My destination table is TestTable(col1 varchar(150), col2 varchar(150))
This ought to be trivial. Here is my code. If someone can point out what I have done incorrectly, I would be truly grateful.
//// Create the package
Package myPackage = CreatePackage("SSISPackage", "Sample CSV Import Package");
//// Events.
PackageEvents myPackageEvents = new PackageEvents();
ComponentEvents myPipelineEvents = new ComponentEvents();
//// Create the data flow task
TaskHost th = myPackage.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
th.Description = "The DataFlow task in the Onvoy CSV Import sample.";
MainPipe myDataFlowTask = th.InnerObject as MainPipe;
myDataFlowTask.Events = myPipelineEvents as wrap.IDTSComponentEvents90;
//// Add the flatfile source connection manager.
ConnectionManager cmFlatFile = myPackage.Connections.Add("FLATFILE");
// Set the stock properties.
cmFlatFile.Properties["ConnectionString"].SetValue(cmFlatFile, "c:\temp\testimport.txt");
cmFlatFile.Properties["Format"].SetValue(cmFlatFile, "Delimited");
cmFlatFile.Properties["DataRowsToSkip"].SetValue(cmFlatFile, 0);
cmFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmFlatFile, false);
cmFlatFile.Properties["Name"].SetValue(cmFlatFile, "FlatFileConnection");
cmFlatFile.Properties["RowDelimiter"].SetValue(cmFlatFile, "
");
cmFlatFile.Properties["TextQualifier"].SetValue(cmFlatFile, """);
//// Add the OLEDB destination connection manager.
ConnectionManager cmSubscriber = myPackage.Connections.Add("OLEDB");
// Set stock properties.
cmSubscriber.ConnectionString = @"Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=subscriber;Data Source=MyDataSource;Auto Translate=False;";
cmSubscriber.Name = "SubscriberConnection";
cmSubscriber.Description = "SubscriberConnection";
//// Add Flat File Source
// Add the component to the dataFlow metadata collection
IDTSComponentMetaData90 myFlatFileSource = myDataFlowTask.ComponentMetaDataCollection.New();
myFlatFileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
CManagedComponentWrapper instFlatFileSrc = myFlatFileSource.Instantiate();
instFlatFileSrc.ProvideComponentProperties();
instFlatFileSrc.SetComponentProperty("RetainNulls", true); // Treat empty columns as null.
// Set the common properties
myFlatFileSource.Name = "FlatFileSource";
myFlatFileSource.Description = "Flat file source";
// Associate the runtime ConnectionManager with the component
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["FlatFileConnection"].ID;
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["FlatFileConnection"]);
// Add columns to the FlatFileConnectionManager
// Hardcode the columns for now
List<string> srcColumns = new System.Collections.Generic.List<string>();
srcColumns.Add(""Column1"");
srcColumns.Add(""Column2"");
//srcColumns.Add(""Column3"");
//srcColumns.Add(""Column4"");
//srcColumns.Add(""Column5"");
//srcColumns.Add(""Column6"");
//srcColumns.Add(""Column7"");
//srcColumns.Add(""Column8"");
//srcColumns.Add(""Column9"");
//srcColumns.Add(""Column10"");
//srcColumns.Add(""Column11"");
//srcColumns.Add(""Column12"");
//srcColumns.Add(""Column13"");
// get the actual connection manager instance
//wrap.IDTSConnectionManagerFlatFile90 ff = myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager as wrap.IDTSConnectionManagerFlatFile90;
wrap.IDTSConnectionManagerFlatFile90 ff = cmFlatFile.InnerObject as wrap.IDTSConnectionManagerFlatFile90;
if (ff != null)
{
wrap.IDTSConnectionManagerFlatFileColumn90 column;
wrap.IDTSName90 name;
foreach (String colName in srcColumns)
{
// iterate
// now create a new column for the connection manager
column = ff.Columns.Add();
// if this is the last row
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
column.ColumnDelimiter = "
";// add the row delimiter
else
column.ColumnDelimiter = ",";
name = (wrap.IDTSName90)column;
name.Name = colName.Replace(""", "");
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 150;
column.MaximumWidth = 150;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
instFlatFileSrc.AcquireConnections(null);
instFlatFileSrc.ReinitializeMetaData();
instFlatFileSrc.ReleaseConnections();
//// End Add Flat File Source
//// Add OLEDB Destination
IDTSComponentMetaData90 myOledbDestination = myDataFlowTask.ComponentMetaDataCollection.New();
myOledbDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
CManagedComponentWrapper instOleDbDest = myOledbDestination.Instantiate();
instOleDbDest.ProvideComponentProperties();
// Set stock properties.
myOledbDestination.Name = "OLEDBDestination";
myOledbDestination.Description = "Destination for data";
// Associate the runtime connection manager
// The connection manager association will fail if called before ProvideComponentProperties
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["SubscriberConnection"].ID;
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["SubscriberConnection"]);
// set custom component properties
instOleDbDest.SetComponentProperty("OpenRowset", "[dbo].[TestTable]");
instOleDbDest.SetComponentProperty("AccessMode", 0);
// Acquire Connections and reinitialize the component
//instOleDbDest.AcquireConnections(null);
//instOleDbDest.ReinitializeMetaData();
//instOleDbDest.ReleaseConnections();
//// Map the source to the destination
myDataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
myFlatFileSource.OutputCollection[0], myOledbDestination.InputCollection[0]);
instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();
//// Map the source columns to the destination columns
// Get the destination's default input and virtual input.
IDTSInput90 input = myOledbDestination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
//foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
//{
// // Call the SetUsageType method of the destination
// // to add each available virtual input column as an input column.
// instOleDbDest.SetUsageType(
// input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
//}
IDTSVirtualInputColumn90 vColumn = vInput.VirtualInputColumnCollection[0];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col1"].ID);
vColumn = vInput.VirtualInputColumnCollection[1];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col2"].ID);
instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();
//// Validate and execute the package
DTSExecResult status = myPackage.Validate(null, null, myPackageEvents, null);
if (status == DTSExecResult.Success)
{
DTSExecResult result = myPackage.Execute(null, null, myPackageEvents, null, null);
}
View 8 Replies
View Related
Jul 9, 2006
hi all,
I m new to this forum and hope i ll get warm welcome from all of you.... thank you
I m Praveen kumar Dayanithi... a master student doin my assistantship in a Company. Kindly help me with this...
Here is my issue.... i would like to know how can i pass arguments to connection manager programmatically. In other words how can i acquire connection manger through SCRIPT task(Vb script). I know it is very easy to manually select n specify database name, table name by right clicking in dataflow task but in my company if i do that it will be very cumbersome for production people to change all the database and table names manually when the project is moved to production. Rather if i programmatically acquire connection manager using global variables it will make production people's job very easy. They have to just change the value of the variables. So can any one help me with this issue.
Thanks n regards
View 1 Replies
View Related
Feb 13, 2006
dear experts,
i'm trying to build a package programmatically from client c# application. I'm working to create three dataflow components:
- OleDB Source
- Derived Column Transformations
- OleDb Destination.
My package works good, but i have several problems when insert an expression as Value of an IDTSCustomPropriety90 object, like this one:
[LEN](#firsname.lineageID) > 5 ? <<if true>> : <<if false>>
Simple expressions like concatenate strings, for example "#firstname.lineageID" + "#lastname.lineageID" seem to work good.
Thanks a lot in advanced.
Paganelli Francesco
View 19 Replies
View Related
Apr 23, 2007
Hi,
I have created an application that loads a package and executes it using DTS runtime classes. But when I run the application on a machine where only SQL Express edition is installed it's throwing
"Retreiving COM class factory for component with CLSID E44847F1-FD8C-4251-B5DA-B04BB22E236E failed due to the following error : 80040154"
Can someone help?
View 1 Replies
View Related
Jul 18, 2007
I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.
I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.
So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?
Thanks,
Keith
p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.
View 3 Replies
View Related
Feb 26, 2007
Hi There,
I am loading/executing packages from c# and I need to populate a temp table from user input and pass this table as a variable to the datareader source components sql command. I am using expression to build this query, but I am getting design time error when I have this command..
"select id, (SysDate + 28) as ExpiresDate from Table1 where id in (Select Id from" +@[User::Table2]+")"..
I have declared Table2 as a variable of type Object and I am creating Table2 in C# and I am assigning that Table to the user Table. But in the design mode, I am getting an error...expression cannot be evaluated.
Can anybody please tell me when I cannot do this?
Thanks,
View 3 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
Nov 29, 2007
I added an activeX package and then try to execute it, when I run it comes up with following error:-
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The Acquire Connection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the Acquire Connection method call failed. SearsCDCC_Transfer.dtsx 0 0
I did some research on line and I came to know that I need to switch the debugging mode to change the 64 bit to false. But I am too novice in SSIS, so I am not sure how to switch the debugging mode. Any idea about it?
View 1 Replies
View Related
Jan 9, 2015
I have a bat file that kicks off a master package, which kicks off about 300 child packages. My bat file is working correctly, however there seems to be issues executing packages in 64 bit mode.
So my question is...
Is there a way to specify in the bat file to execute packages in 32 bit mode and not 64 bit?
bat file:
"C:Program FilesMicrosoft SQL Server110DTSBinnDTExec.exe" /f "E:MasterPackage.dtsx"
View 1 Replies
View Related
Jul 24, 2007
Hi Guys,
Refering to my previous post I've a package which easily run when transaction mode is Supported but when i put it to Required and All DFs are supported inside the sequence container it won't run.
Well technically it goes and pass step 1, so it goes to green but regardless of what i put in the 2nd DF it won't run. It just hangs in there.
When i check the component service and check DTC, the transaction would be running but never commited.
I have done most of the things about DTC and configuration with the MS articles as this package was failing before but now it just sits at this point.
Any ideas?
Thank you
Gemma
View 3 Replies
View Related
Apr 30, 2008
Hi All,
I am not able to open the package in BIDS. When I open the package I am seeing only the XML. Below I had given what I have done.
First I have installed Visual studio 2008 Professional and next I installed SQL Server 2005 with Integration services, database services, workflow components.
I am able to see the BIDS in Start --> All Programes --> Microsoft SQL Server 2005 --> "SQL Server Business Intelligence Development Studio"
Any help would be appreciated.
Thanks in Advance.
View 3 Replies
View Related
Apr 3, 2007
Thanks for any one can give me a help.
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
Thanks in advance.
View 7 Replies
View Related
Aug 16, 2006
Hello
When running a package in VS you can see something like this in the output window:
SSIS package "logging.dtsx" starting.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "C: est ssis loggingad_data1.txt" is 477.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (87)" wrote 0 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1011)" wrote 1 rows.
SSIS package "logging.dtsx" finished: Success.
This is exactly when I need when a package is running but I want to be able to see it without using Visual Studio.
I would do it in Reporting Services but I need to find out to get the information. The SSIS logging feature in a package does not provide that kind of info.
Did someone try this already?
Thanks!
Mop
View 6 Replies
View Related
Mar 12, 2008
This would actually be funny if I weren't under serious time constraints right now. I have an SSIS project with several script tasks in the control flow. I put breakpoints in one of them to allow me to step through and see whats going on. However, when I start the project, the IDE brings up the script editor for a different script task. The breakpoints in this task are actually on the same line numbers as the task I need to debug but the code is all wrong. I checked the task for which the code is being displayed and there are no breakpoints there.
Thanks for the assist!
Don
View 11 Replies
View Related
Jan 18, 2007
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies
View Related
Mar 9, 2000
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
View 1 Replies
View Related
Jul 27, 2015
how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.
  i am using SQL server 2008 R2
View 5 Replies
View Related
Jul 16, 2007
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
View 1 Replies
View Related
Jan 3, 2006
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
View 1 Replies
View Related
Jun 9, 2006
Hi, all here,
Would please anyone here give me any idea about how can I connect to SQL Server 2005 analysis services server and send XML request to it programmatically (with Business intelligence development studio in SQL Server 2005)? Thanks a lot.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Jan 26, 2008
hi
my domain is hosted on a remote server
related databases are stored on a seperated DataServer
I have the name of that DataServer
and i need to create new DBs programmatically
I use this statement on my local machine
sqlstat= "CREATE DATABASE dbc_" + dbid + " ON PRIMARY" + "(Name=db_" + dbid + ", filename = '" & Server.MapPath("~/app_data") & "" + dbfilename + "')"
but it will not work , because i dont know the physical path
i cant use Server.MapPath()
How could i do?
thanks
View 6 Replies
View Related
Sep 3, 2015
I am in the process of migrating from SQL Server 2008 over to 2012. The issue is that on some of the Stored Procedures object names are not matching the name inside of the procedure itself. So, for instance the object name under Programability in SSMS is different than the name in the CREATE PROC. Perhaps the object name was changed. The CREATE PROC procedure sits in the sys.sql_modules table and in the column "DEFINITION" there lies the code. The code is in text. EXAMPLE
CREATE PROC [SCHEMA].OBJECTNAME AS OR IT COULD BE CREATE PROCEDURE [OBJECT] ( Select * from etc )
I need a way of changing the [SCHEMA].OBJECTNAME to match the object name in programmabillity.
View 4 Replies
View Related
Jul 23, 2005
Hi,Does anyone here know whether it is possible to change theauthentication method of a SQL server instance programmatically, ratherthan going through enterprise manager.I am using SQL-DMO (under C#) for some other things, but thedocumentation is so bad that I can't find out whether what I want to docan be done. I have also tried googling but came up empty handed.I hope that someone can help me.CheersJono
View 2 Replies
View Related
Jun 13, 2006
In deploying SQL Express, how do I create a new server (INSTANCENAME)programmatically?What would be the best approach to accomplish this.Thank you so much,
View 1 Replies
View Related
Mar 12, 2008
I have a web application I created in asp.net + sql2005 and am deploying a genercized version of it to multiple clients. I have created a script which dynamically generates a database to a server and creates all of the tables, procs, and views. I now need data from the old database, which will be imported into the new database. It's basically default users, lookup values, etc. What are some methods of getting this data out of the old server and into the new server? I was thinking about generating flat files with the data, then writing code to loop through and insert the data, but it seems very tedious. Do I have any other options? I cannot use Backup and Restore because I have no access to the new sql server's filesystem.Thanks.
View 3 Replies
View Related
