Programmatically Created SSIS Package, CSV File To OLDDB (SQLSever 2005)

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


ADVERTISEMENT

Programmatically Creating SSIS Package

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

Create SSIS Package Programmatically In VB6

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

I Just Created My First SSIS Package

Jun 27, 2007

Hi,



I just created my first SSIS package.



For now, I can only run it inside Visual Studio.



I want this packet to be run every day as a scheduled task. How do I do that?



When I double click my .dtsx I get an Execute Package Utility. Where can I set my package to run every day at certain time?



Thank you.

View 4 Replies View Related

Executing A SSIS Package Programmatically Where Only SQL Express Is Installed

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

How To Get Rowcount In A Programatically Created SSIS Package

Oct 22, 2007

Hi,

I created a SSIS Package programatically based on the few threads here in this forum. This package just has a data flow task, during data transfer for every 1000 rows or so I want to update the status in a table in the database.

How do I achieve this?

As of now I just have a source and a destination, no transformations in between the flow. I'm not sure if rowcount will help, when I tried it using a onprogress even handler it always showed up as zero.

Thanks

View 6 Replies View Related

Processing Last Created/modified Files From A Location Using SSIS Package

Mar 12, 2008

We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.

Kindly give respond to this if any one has worked on this.

Regards,
Sajesh

View 7 Replies View Related

Sqlsever 2005

Mar 12, 2008



How can i creat a new database in Sqlsever 2005. it looks different in 2000.
i really need help. all comments are welcome.

View 4 Replies View Related

Connection To Sqlsever 2005

Mar 13, 2008

Can someone help me to connect to sqlsever 2005 using codes. i have created the database but dont know how to connect using vb.net 2008. All comment will be of great help. thanks for the other help.

View 1 Replies View Related

Save DTS Package Created In DTS Designer(SQL 2000) On SQL 2005 Under Legacy

May 13, 2008



Hi All,
One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server..
At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??

View 2 Replies View Related

Accessing Data From A Programmatically Created SqlDataSource

Nov 3, 2007

Hi
I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example????
If Not Page.IsPostBack Then
'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
'Create a SqlConnection instanceUsing connString
'Specify the SQL query
Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17"
'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString)
'Get back a DataSetDim myDataSet As New Data.DataSet
'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -??????????????
'Close the connection
connString.Close()
End Using
End IfThanks for any helpRichard
 

View 1 Replies View Related

Where Is The Repository Gets Created For SSIS( Integration Services) 2005?

Apr 19, 2007

if someone can throw light regarding the metadata repository creation,its usuage , it will help me out.

View 5 Replies View Related

Integration Services :: Loading Tables Created In Previous Sequence Into Local Archive File - SSIS Path Error

Oct 5, 2015

I've been working on an SSIS package trying to load some data and the archive sequence is faulty. I've been trying to load a few tables created in a previous sequence into a local archive file and I've been getting the error "Could not find a part of the path."

The results aren't telling me what it's finding last and so I don't know where to start.

And the source DOES have data in it. It's something between the source and the destination.

View 2 Replies View Related

Integration Services :: Reading Data File Present In A File System From A Package Deployed In SSIS DB?

Dec 4, 2014

I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?

View 7 Replies View Related

SSIS - Get ServerName From A XML Or TXT File Into SSIS Package

Jan 4, 2008

Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!
Regards!!
Thanks.

View 18 Replies View Related

Ssis Package To Flat File

Nov 16, 2007

Ok everybody. I am new to sql. I have ms sql staging database that pulls data from mysql database. Then once a day I run a ssis package that moves the data to a live database and also creates a flat file that is posted to a ftp site then truncates the table. One problem I am running into is if the mssql staging database has no records the flat file is still created. How do I stop it?

View 10 Replies View Related

Unzip A File In SSIS Package

Feb 21, 2008

I want to unzip a file through "Execute Process Task".

For compressing(zip) a file i write the following.

In Process Tab:
In Executable: C:Program FilesWinZipWINZIP32.EXE
In Arguments: -min -a "C:file.zip" "C:file.mdb"

What arguments should i write to unzip a file?
And in case i want to copy a file from one location to another through Execute Process Task what should i do?

Please Help

View 12 Replies View Related

How Do You Unzip A File In An SSIS Package?

Jan 24, 2007

Is there a way to unzip files in a SQL Server 2005 SSIS package. I now I can do it using winzip and executing the procedure, master..xp_cmdshell, but that is not what I am preferring at the moment. Anything direction in this regards will be much appreciated.



Thanks,

Monisha

View 19 Replies View Related

How To Read A .PST File From A SSIS Package?

Nov 14, 2006

Hi everyone,

I'd like to read a .PST file (which contains thousands of mails) regardless of Exchange client.

Is it possible?

Thanks for your input

View 3 Replies View Related

Execute SSIS Package From .vbs File

May 24, 2006

I need to execute a SSIS package from a ".vbs" file on a computer that don't have SSIS installed on it.

Thank You

View 3 Replies View Related

Run RS Script File From SSIS Package

Oct 26, 2006

Hi!

I have a Reporting Services script file used to publish my reports. Is there anyway I can run it from a SSIS package?

Thank you!

View 1 Replies View Related

SSIS Package From Bat File - Never Stops

Mar 19, 2008

Below is my batch file that calls one SSIS package. When I run it from the command prompt, it runs and runs and never stops. What am I missing?

dtexec "/FILE "\sqldevssispackagesDW_DatamartETLDEVExtract_DimItem.dtsx" /DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW "

View 12 Replies View Related

Trying To Access A DBase III File With A SSIS Package

May 4, 2006

I am trying to get a handle on the new SQL Server Integration Services in SQL Server 2005. There is a legacy DTS package that I need to get working on our new server (using SQL Server 2005) but I can't seem to get it to work.First, I migrated it and attempted to run the package. It gave me many errors. So, I thought I would just rebuild it in integrated services. Everything was going smooth until I got to the real data transformation part.I added a data flow task and it took me to the Data Flow tab. Then I added a DataReader Source. I am trying to read from a dBase III file and I assumed I would be using an ODBC driver for that. Well... it has been hell trying to get it to see the dBase III file and access it.I have tried two solutions from the internet.This one and this one (by Wenyang) with no positive results.No matter what I do I still get an error in the bottom of the Advanced Editor for DataReader Source that reads: "Error at Sales Transformation [DataReader Source [81]]: Cannot acquire a managed connection from the run-time connection manager."Has anyone attempted anything like this before? Are there any SSIS experts around here that can drop me a few pearls of wisdom?Thanks in advance... I'm dying here.

View 3 Replies View Related

SQL 2012 :: Execute SSIS Package Using Bat File

Dec 28, 2014

I am tasked with creating a batch file to execute SSIS packages.

View 3 Replies View Related

SQL 2012 :: Run SSIS Package In 32 Bit Mode Using BAT File

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

Dependency File Locations For A SSIS Package

Jul 24, 2007

Hi all,

I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).



Thanks,

Johan

View 1 Replies View Related

SSIS Package To Read Stuff From A XML File.

Jun 26, 2007

I would like to have some key-value pairs set up in the form of a XML file and read the same from within a SSIS package. What I mean is something like business information needs to be configured using XML or INI or something similar. Could anyone help me with a similar sample solutions or give me some links which will lead me to the solution.

View 5 Replies View Related

SSIS Package Configuration File Malformed

Jan 24, 2008

Can someone identify what is wrong with the XML config file that I i'm using with a SSIS package? The XML file was created by Visual Studio tool




Code Snippet
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="BRIDGENETjpacella" GeneratedFromPackageName="SsisEndBatch" GeneratedFromPackageID="{91A1941E-E066-47F7-8DD4-9E260F38741D}" GeneratedDate="1/21/2008 4:35:22 PM" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2IMPORTTEST</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-DEV02</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-FILE01</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[Executable]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILEBridgeNet.Reporting.ExecuteConsole.exe</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[WorkingDirectory]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILE</ConfiguredValue>
</Configuration>
</DTSConfiguration>





When I run the pacakge, this is what happens:


Started: 10:48:56 AM
Info: 2008-01-24 10:49:02.92
Code: 0x40016041
Source: SsisEndBatch
Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc
ENDBATCH TEST ENV.dtsConfig".
End Info
Warning: 2008-01-24 10:49:03.15
Code: 0x80012011
Source: SsisEndBatch
Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
End Warning


View 6 Replies View Related

Problems Running A Bat File Within A SSIS Package

Jul 13, 2007

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!



Thanks in advance.

View 16 Replies View Related

Integration Services :: BAT File To Run SSIS Package

Oct 7, 2015

Looking for code for .Bat file to  run ssis package and i want to run it from Tidal.

View 8 Replies View Related

SSIS Package Can't Access File System

Sep 28, 2007

Hi,

I have an SSIS package that has an "execute process task" that executes a batch file. The package has been deployed to the msdb database, and is called from a stored procedure using xp_cmdshell dtexec ...

I can execute the package just fine if I'm logged onto the server as a system administrator, by running the stored procedure from a query window.

However, if I log on to the server as a non-admin user, the package attempts to run, but breaks at the file system task, with "Access Denied". It can't run the batch file. It seems to be a permissions issue at the file system level.

How do I resolve this?? Please help.

Thanks

View 15 Replies View Related

Programmatically Aborting A Package

Jul 31, 2007

Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow.

View 12 Replies View Related

Regenerating Package ID Programmatically

Feb 7, 2006

Hi,

I built a small application that helps me create new packages by loading a template and modifying some parameters, and eventually save the updated package to a user defined location. This application uses the SSIS programming API.

I know all the packages created this way from the same template will have the same package ID. Is there a way to regenerate the package ID programmatically? I don't want to use dtutil.exe, but rather a nice package level method in the API.

Is there such?

Thanks.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved