Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

progress indication from ssis app in VB

Hi

am calling an ssis app from my vb programme. It takes about 5 minutes to run. What is the best way to provide progress feedback to my vb user.

Cheers

Ants

I

Ants Hurdley wrote:

Hi

am calling an ssis app from my vb programme. It takes about 5 minutes to run. What is the best way to provide progress feedback to my vb user.

Cheers

Ants

I

Package.Execute() has an overloaded constructor that allows you to pass in a IDTSEvents object. As far as I'm aware this allows you to get events out of the executing package - after which you can surface them to your VB app.

Take a look here for more info:

DtsContainer.Execute Method
(http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.execute.aspx)

-Jamie

|||There is also a sample event listener code at
http://msdn2.microsoft.com/en-us/library/ms136090.aspx|||

Hi

Thanks for this - I am not sure if I am understanding this correctly as I am new to this but I want to show the progress as the package is executed. I.e. my Package has 15 steps, so I would like to show a progress bar or something to let the user know that things are happening. Not just show a success or failire.

Cheers

|||

The sampel only demonstrates overriding the OnError event, but you can override any of the other events, such as OnPreExecute and OnPostExecute, which will give you begin and end notices for each container. See here for more information on the DefaultEvents class-

DefaultEvents Members (Microsoft.SqlServer.Dts.Runtime)
(http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.defaultevents_members.aspx)

|||

Ants Hurdley wrote:

Hi

Thanks for this - I am not sure if I am understanding this correctly as I am new to this but I want to show the progress as the package is executed. I.e. my Package has 15 steps, so I would like to show a progress bar or something to let the user know that things are happening. Not just show a success or failire.

Cheers

Well that's not really a SSIS question. SSIS can tell you its progress and its up to you how you display that information in your app. A progress bar is an option I guess.

Note that BIDS uses the same mechanism to populate the "Package Execution" tab when you debug a package .

-Jamie

Programming with c#.NET and variables

Hi all,

I know how to basically change SSIS variables from within a C#. NET winform application. But can anyone point me on how to change the contents of a connection manager SSIS connection from withing C#.net please. That is..how do i parameterize a connection manager connection to basically accept the connection information from within the winform app.

Thanks

bump|||

One way is to use an expression to set the ConnectionString property of the ConnectionManager. You can base the expression on a variable.

You can also set the connection manager properties directly through code, by getting the collection of connections from the package.

|||

the problem is that i already have the ssis package...im basically creating a win form front end to mask the ssis package. in my win form im capturing the sql server connection info, and im trying to figure out how to variabalize the sql connection in the package connection manager.

i was able to do something like this easily for a flat file connection but am have no clue on how to do it for the sql connection.

|||

This should not be at all harder then dealing with flat file connections. The only thing you need to do is compose your connection string and assign it to the connection manager. You might want to post your initial code here and somebody will help you.

Thanks.

|||

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

|||

YoungEngineer wrote:

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

You add the Password parameter to the ConnectionString. Typically I build a variable that is used to build a dynamic connection string, and then I just reference that variable in the connection manager's expression property for ConnectionString.|||

is the password field in the connectionstring just called "password"?

Data Source=localhost;User ID=myUser;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

|||Yes.

Programming with c#.NET and variables

Hi all,

I know how to basically change SSIS variables from within a C#. NET winform application. But can anyone point me on how to change the contents of a connection manager SSIS connection from withing C#.net please. That is..how do i parameterize a connection manager connection to basically accept the connection information from within the winform app.

Thanks

bump|||

One way is to use an expression to set the ConnectionString property of the ConnectionManager. You can base the expression on a variable.

You can also set the connection manager properties directly through code, by getting the collection of connections from the package.

|||

the problem is that i already have the ssis package...im basically creating a win form front end to mask the ssis package. in my win form im capturing the sql server connection info, and im trying to figure out how to variabalize the sql connection in the package connection manager.

i was able to do something like this easily for a flat file connection but am have no clue on how to do it for the sql connection.

|||

This should not be at all harder then dealing with flat file connections. The only thing you need to do is compose your connection string and assign it to the connection manager. You might want to post your initial code here and somebody will help you.

Thanks.

|||

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

|||

YoungEngineer wrote:

well...one of the problems that i am currently running into is that the Expressionlist for an OLE DB connection does not have an assignable password field.

what that means is that when i click on my SQL connection in the Connection Manager and click the elipsis in the expression field in the properties window, I cant seem to assign a variable to the password field. Seems like i can assign an initial catalog, username and servername..but without being able to assign the password field to a variable im not sure how to proceed.

suggestions anyone?

You add the Password parameter to the ConnectionString. Typically I build a variable that is used to build a dynamic connection string, and then I just reference that variable in the connection manager's expression property for ConnectionString.|||

is the password field in the connectionstring just called "password"?

Data Source=localhost;User ID=myUser;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

|||Yes.

Programming SSIS - Exception 0xC0204006

Hello. I am attempting to use SSIS to import a table from MS Access in to SQL Server. However, when i set the destination component properties I get the following error:

Exception from HRESULT: 0xC0204006

The table exists within the destination database and if I comment the line out, it is able to acquire the connection.Anyone have any ideas?

Thanks

ConnectionManager connMgr;
ConnectionManager connMgr1;

//Create the package
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

//Create to connections to the package
Connections packageConns = package.Connections;

connMgr1 = package.Connections.Add("OLEDB");
connMgr1.ConnectionString = accessModelConnectString;
connMgr1.Name = "OLEDB ConnectionManager";

connMgr = package.Connections.Add("OLEDB");
connMgr.ConnectionString = sqlConnectionString;
connMgr.Name = "OLEDB ConnectionManager1";

//Add a dataflow task to the package.
MainPipe dataFlowTask = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

IDTSComponentMetaData90 sourceComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";
sourceComponent.Name = "yyy";

// Get the design time instance of the component.
CManagedComponentWrapper instance = sourceComponent.Instantiate();

// Initialize the component
instance.ProvideComponentProperties();

// Specify the connection manager.
if (sourceComponent.RuntimeConnectionCollection.Count > 0)
{
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
}

// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "SELECT Test FROM Test");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

IDTSComponentMetaData90 destinationComponent = dataFlowTask.ComponentMetaDataCollection.New();
destinationComponent.ComponentClassID = "DTSAdapter.OleDBDestination.1";
destinationComponent.Name = "xxx";

// Get the design time instance of the component.
CManagedComponentWrapper instance1 = destinationComponent.Instantiate();

// Initialize the component
instance1.ProvideComponentProperties();

// Specify the connection manager.
if (destinationComponent.RuntimeConnectionCollection.Count > 0)
{
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}

instance1.SetComponentProperty("AccessMode", 3);
instance1.SetComponentProperty("OpenRowSet", "[PedestrianFlow].[dbo].[OLE DB Destination]");

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData();
instance1.ReleaseConnections();

// Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0],destinationComponent.InputCollection[0]);

IDTSInput90 input = destinationComponent.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
string y = vColumn.Name;
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;
Console.WriteLine(err);
}

Can anybody help? I am getting nowhere fast.

Thanks.|||

This explains it quite well I think, but feel free to update-

0xC0204006
(http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html)

Check the property you are using, it does not exist.

|||... case sensitive name is what I meant to add.|||

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.|||

cjturner wrote:

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.

Please be sure to mark the appropriate post as the answer to your question.

Programming in ssis

Hi..

I am working on sql server integration services..New to this topic...I want to transfer data from flat file to sql database by doing it programatically.Creating source,destination and transform adapters

my basic question is should the source,transform and destination adapter be bulid for each file i am trying to transfer.

What is the advantage of this doing it this way.

Would anyone let me know about this..

Thanks

The answer to your question will depend on your application's requirements, but in principle, if you are using the same metadata throughout the data flow, you can use a for-each loop and dynamically update the connection string of the flat file connection manager, using the property expression feature.

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();

Programmatically creating SSIS package

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] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// 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.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically creating SSIS package

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] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// 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.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.

|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically Created SSIS Package, CSV file to OLDDB (SQLSever 2005)

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 = ".\01105.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, "\r\n");
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 = "\r\n";
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";
}

}
}

A good first step in troubleshooting these types of problems: Save the file out to a DTSX (using the Application.SaveToXml method, then open and run it in BIDS. That might give you a better idea what's going on.

|||

I include a save to file by default, but have it for Debug confiurations only -

Code Snippet

#if DEBUG

// Save package to disk, DEBUG only

new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);

#endif

You would also be better off using the overloaded Execute method that enables you to pass in IDTSEvents amongst other things. This way you can capture error events in your application, otherwise you have no idea of what has one wrong, which when deployed will be an issue I guess. The save to file trick is easier when developing the code as you get the power of the designer and debugger, rather than just events you capture.

Programmatically create data sources

Is there a way to do this? It does not appear as part of the standard SSIS API.

Thanks.

The DataSource and DataSourceCollection are part of the SSAS API with the latter having Add and AddNew methods. Not quite sure how to get the project context to create them, but never looked either. They are the same data sources in SSIS and SSAS projects, just do a View Code on them for each project, they are the same XSD etc.|||

Noa,

You can create anything programmatically (in a .NET application) that you can create in the BI Studio. Here is an example:

I'll create a new FlatFile ConnectionManager and it will have two columns, Name (40 chars long and Age, 3 chars long)

Dim cmflatFileNew As ConnectionManager = myPackage.Connections.Add("FLATFILE")
cmflatFileNew.Properties("ConnectionString").SetValue(cmflatFileNew, "C:\aFile.dat")
cmflatFileNew.Properties("Format").SetValue(cmflatFileNew, "FixedWidth")
cmflatFileNew.Properties("DataRowsToSkip").SetValue(cmflatFileNew, 0)
cmflatFileNew.Properties("ColumnNamesInFirstDataRow").SetValue(cmflatFileNew, False)
cmflatFileNew.Properties("Name").SetValue(cmflatFileNew, "FlatFileConnection New")
cmflatFileNew.Properties("RowDelimiter").SetValue(cmflatFileNew, vbCrLf)
cmflatFileNew.Properties("TextQualifier").SetValue(cmflatFileNew, """")

Dim ffNew As RuntimeWrap.IDTSConnectionManagerFlatFile90 = Nothing
ffNew = TryCast(cmflatFileNew.InnerObject, RuntimeWrap.IDTSConnectionManagerFlatFile90)

Dim newCol1 As RuntimeWrap.IDTSConnectionManagerFlatFileColumn90
Dim newName1 As RuntimeWrap.IDTSName90
newCol1 = ffNew.Columns.Add()
newCol1.ColumnType = "FixedWidth"
newCol1.DataType = RuntimeWrap.DataType.DT_STR
newCol1.ColumnWidth = 40
newCol1.MaximumWidth = 40
newName1 = TryCast(newCol1, RuntimeWrap.IDTSName90)
newName1.Name = "Name"

Dim newCol2 As RuntimeWrap.IDTSConnectionManagerFlatFileColumn90
Dim newName2 As RuntimeWrap.IDTSName90
newCol2 = ffNew.Columns.Add()
newCol2.ColumnType = "FixedWidth"
newCol2.DataType = RuntimeWrap.DataType.DT_I4
newCol2.ColumnWidth = 3
newCol2.MaximumWidth = 3
newName2 = TryCast(newCol2, RuntimeWrap.IDTSName90)
newNme2.Name = "Name"

If you have any more questions, post here and I can reply.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||

Thank you Mark.

I know how to create connection managers, as it is part of the standard API.

Data sources are design time components - part of the SSIS project and has no impact on programmatically creating packages.

Can you provide an example of accessing the SSIS project and adding a data source to it?

|||

Ahh, I see what you are asking about.

Actually - I don't know of a way to create a .ds file programmatically. I bet it can be done though. I do know that to add it to your project you will have to edit the dtproj file which is xml. That shouldn't be too dificult.

Sorry for the misunderstanding.

Mark

http://spaces.msn.com/mgarnerbi

Programmatically configuring error and truncation dispositions for row redirection

Hi,

I have created a SSIS package programmatically using C#.

The package should do the following take data from source A, and place rows into destination B, if there are any error rows then redirect the rows to destination C. In my package I have the following components:

DTSAdapter.OLEDBSource.1 - Used as the Source

DTSAdapter.OLEDBDestination.1 - Used for the Destination Output - (let me call this normalOutput)

DTSAdapter.OLEDBDestination.1 - Used for the Destination Error Output - (let me call this errorOutput)

All my mappings appear to be correct, I build and save the package and receive a Successful validation and Success on Execution.

However, When I open the application using the Execute Package Utility I get the warning:

Warning:No rows will be sent to the error output(s). Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s)

How do I get around this?

I have placed on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Output), on the input collection I have placed:

normalOutput.InputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.InputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.OutputCollection[0].ExclusionGroup = 1;

on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Error Output) I have placed:

errorOutput.OutputCollection[0].ExclusionGroup = 1;

However this does not work, I just get the wanring displayed above.

I have also tried to set the

OutputCollection[0].SynchronousInputID for both the error output and the normal output to the same values

so that:

normalOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

errorOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

However, the above scenario does not pass the package validation, in the Execute Package Utiltity, I get the wanring mentioned above and also the error:

Error: The input "OLE DB Destination Input" (16) has an invalid error or truncation row disposition.

So my question is what are the correct configuration settings to have in this scenario?

Thanks

Just looking at your code, I see a couple things to suggest. Where you are setting the Error and Truncation dispositions to redirect, I think that needs to be done on every item in the OutputCollection and not on the InputCollection. I also think you should not be setting anything for the ExclusionGroup.

I suggest you mock up what you're trying to create manually in BIDS and look at the Advanced Editor for OLE DB to get an idea for what properties need to be set where.

Also, in the beginning of your message you listed the same component for the normal and error outputs. Was that a typo?
|||

Thanks JayH, I ended up setting the ErrorRowDisposition to redirect on the InputCollection[0] of the NormalDestination (OLE DB Destination) and then creating an error path from the NormalDestination to the ErrorDestination (OLE DB Destination) .

This works for errors such as trying to place a varchar(20) column into a float column, but for some reason you are not allowed to set the TruncationRowDisposition to redirect at this level.

So, I went ahead a created a package on the VS IDE and set the TruncationRow dispositions to redirect on every column in the Source (OLE DB Source), and made a path from the error output to a ErrorDestination (OLE DB Destination).

and then... nothing...no redirection, when running the package using the Execute Package Utility, I see the warnings that a truncation will occur, but it just goes ahead and truncates the data in the column and placing the row into the NormalDestination and I get no rows redirecting to my ErrorDestination.

Is there something I am missing? Should be using a transformation component?

|||I think I'm missing the bigger picture. I'm not sure how a transformation component could help you. I'm imagining a single source OLE DB and two destination OLE DBs, one "normal" and the other "error".

You are correct that you can't set truncation disposition on an OLE DB destination. You can set an error disposition to redirect, but only if you're not fastloading. If there is an error when fastloading, the entire load will fail.

Your pipeline metadata should have the correct column definitions, and they should match your destination. Thus you should have no truncations or type mismatches detected at the destination. The place to detect truncations is on the source when the data is read and put into the pipeline. The only errors that should get detected at the destination are constraint violations.

If you'd like to send me code, my email is jay underscore hackney at hotmail dot com.
|||

No your spot on that is the final setup I had on Friday.

Point to note: I currently have my MaxInsertCommitSize set to 1000 which allows the fast loading.

Now back to the matter at hand, so, my package now has all the output columns on the source have both the ErrorRowDispositions and TruncationRowDispositions set to redirect and I've removed the ErrorDisposition from the "nomal" destination.

Now when I run the package I receive a truncation error, it should then perform the redirect but no rows are inserted and I get the following error message:

Error: There was an error with input column "City" (109) on input "OLE DB Destination Input" (16). The column status returned was "Text was truncated or one or more characters had no match in the target code page"

Let me break down the City column:

On the OLE DB Source is varchar(50)

On the "normal" destination is varchar(20)

On the "error" destination is varchar(MAX)

I want the truncation error to occur and redirect into the "error" destination where it should be inserted without any issues.

|||Regarding the MaximumInsertCommitSize, this does not enable any type of error redirection when fastloading. All it means is that instead of your whole load failing due to an error, only that 1,000 row chunk containing the error fails, and the rest of the load is considered successful.

Everything you're describing sounds correct to me. Have you saved the programmatically generated package and viewed it in the IDE to verify that everything was created correctly? Have you set the IsErrorOut on your error output?

|||Sorry. Have to correct myself. The failed chunks do get redirected.
|||

Thanks for the information on the fastloading, for the time being I have switched it off...

Regarding the the IsErrorOut field on the error output that is... (source.OutputCollection[1].IsErrorOut), it is already set to true by the framework.

Yes, the package is saved and i've looked at it in the IDE to check it and everything is fine:

However on executing the package I am left with:

Error: An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Descriptioni "Invalid character value for cast specification" Information: The final commit for the data insertion has ended.

Error: There was an error with input column "City" (108) on input "OLE DB Destination Input" (29). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: the "input "OLE DB Destination Input" (29)" failed because error code 0xC0209078 occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

The strange thing is the "OLE DB Destination Input" the error refers to in the last line belongs to the "source" component. However, this should be left to "Fail component", because the redirections have been set on the output items.

Looking at the External and Input columns, on the error destination the DataTypes for the External columns are DT_TEXT and on the Input columns they match the type and length of the "normal" Destination OutputColumns which seems correct to me.

I'm at a loss....

Can you advise of any good books on programming SSIS (and handling error outputs!) using C#?

|||

Ok, I remade the new package with the VS IDE, with only one mapping on the City column.

I map the "source" city (varchar(50)) to "destination" city (varchar(5))

I map the "source" city (Error output - varchar(50)) to "error destination" city (varchar(MAX))

I set the the errorRow and truncationRow dispositions on the "source" on the city column to RedirectRow.

I then run the package and get the warning that a truncation could occur on the column city, but the package just runs through and places all the rows into the destination table, no rows are redirected - (Note that there are is data in the source which should be redirected).

Looking at the "destination" all the truncations have gone ahead leaving me with 5 character strings in the City column.

Surely I must be doing something wrong here?

|||No books that I know of. Darren probably has the most experience doing this type of stuff, but he apparently hasn't seen fit to comment.

Are you controlling your source data for the testing? Do you know that there aren't some invalid characters causing this error?

I think the best way to determine what your code should be doing is by comparing it to the XML of a package created in the IDE. You may also need to simplify your package so you can isolate components until they are working correctly. Maybe you should consider just using dead end components like Unions instead of OLE DB destinations for your normal and error outputs.

These are the disposition-related settings I think you should be using for your Source component. Are you setting the UsesDispositions on your source component?

OLE DB Source Component
UsesDispositions=True

NormalOutput
IsErrorOut=False
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Normal OutputColumn
ErrorOrTruncationOperation="Conversion"
ErrorRowDisposition=RD_RedirectRow
TruncationRowDisposition=RD_RedirectRow

ErrorOutput
IsErrorOut=True
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Error OutputColumn
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

|||

Yep, I already had a windiff moment with it to find any differences between a IDE made package and a dynamically created one, thats how I've ironed out initial issues.

I've also used the example shown here:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Setting up the whole package in the IDE and using my database tables instead, mapping just the city column and no other columns, and all the happens is I get a warning about the imminent truncation and the truncation goes ahead. So I get nothing in my error destination.

This is getting me extremely fustrated, is there anyone who has an example of setting up a simple TruncationRow redirect?

Jamie Thompson could you knock one up?

|||I think you just said that if you create a package in the IDE (using your tables), that you can't get the redirection to work there either? That's the second indication I've heard that this may be a different problem.

Let's try to establish a baseline of functionality by creating a simple package in the IDE. If you have AdventureWorks installed, try it with that database first to take your data out of the equation. If not then just use your own table.
OLE DB Source in table mode that loads the Person.Address table from the AdventureWorks database. On the columns tab, select only the City column (it is an nvarchar(30))
On the Error Output tab, set the error and truncation dispostions to redirect row Close the Source, right-click it, and select "Show Advanced Editor" On the "Input and Output Properties" tab, open "OLE DB Source Output", open "Output Columns" Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
Drop two Union All components on the data flow surface, connect one to the normal (green) output from the source, and the other to the error (red) output.|||

Mr JayH you are a godsend.

From reading your points the problem with the my package was made clear in the line:

Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
|||

Just incase anyone else runs into this problem here is my final mappings code for the "Source".

private void CreateMappings()

{

// Map OutColumns to there external metadata columns for my mappings

foreach (SSISMapping mapping in dtsMappings)

{

IDTSOutputColumn90 outputColumn = component.OutputCollection[0].OutputColumnCollection[mapping.ToColumn.Name];

IDTSExternalMetadataColumn90 exMetaDataColumn = component.OutputCollection[0].ExternalMetadataColumnCollection[mapping.FromColumn.Name];

componentInstance.MapOutputColumn(component.OutputCollection[0].ID, outputColumn.ID, exMetaDataColumn.ID, true);

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

// Note that this must come AFTER the mapping because otherwise the properties will

//be mapped to the ExternalMetaColumn's properties

outputColumn.SetDataTypeProperties(SSISUtilities.GetDataType(mapping.ToColumn.DataType),

mapping.ToColumn.Length,

mapping.ToColumn.Precision,

mapping.ToColumn.Scale,

SSISUtilities.DEFAULTCODEPAGE);

}

}

Note that SSISUtilities and SSISMapping are not part of the framework.

Programmatically change property

In the old DTS, we can use the ActiveX Script to change any task's property programmatically.

Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?

Say, I want to change the Fuzzy look up reference table name.

Can we do it?

Hi,

No, you can't do this using the Script Task.

You CAN change any property of a task or container at runtime using a property expression: http://www.google.co.uk/search?hl=en&q=ssis+property+expression&meta=

You can change some properties of components (Fuzzy Lookup is a comoponent, not a task) at runtime using the same technique, but not many. The component properties that can be changed by property expressions are surfaced in the properties pane for the task in which the component resides.

-Jamie

Programmatically Access The ExecuteProcessTask

Hi all,

I am trying to programmatically create an Execute Process Task in an SSIS package.

So far, I have the following:

Private package As Package
Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:ExecuteProcessTask"), TaskHost)
th.Name = "Execute Process Task"
th.Description = "Execute Process Task"

That will get me the ExecuteProcessTask in my package that I want. But now, I would like to set the properties of it (i.e., the executable and arguments)

Basically, my IDE does not have any idea what an "ExecuteProcessTask" is. After lots of research, I cannot find out which assembly I need to reference in order to gain access to this object. In addition, whenever I try to Import the assembly that I think it is (i.e. Microsoft.SqlServer.ExecProcTask, or Microsoft.SqlServer.Dts.Tasks.ExecuteProcess), none of them work. I can see in my Assembly Cache that the .dll is registered correctly ...

What is going on?
What do you mean "none of them work"? When you set a reference to the Microsoft.SqlServer.ExecProcTask.dll then the code below should work.

Code Snippet

Dim execProc As Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess = CType(th.InnerObject, Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess)
execProc.Executable = "executable.exe"
execProc.Arguments = "/arguments"

|||Really, my problem is that I can't access the assembly that I need.

Basically, I need access to the Microsoft.SqlServer.Dts.Tasks.ExecProcTask assembly-

But, when I try to add a reference to it, I do not see it. In addition, with the interface in SSIS, I cannot "browse" for the assembly.

The only assembly that shows up in Intellisense is Microsoft.SqlServer.Dts.Tasks.ScriptTask

I am editing a script task- so what good is a script task if I can't access the assemblies I need?

What can I do?

|||Ah, you need to copy it from C:\Program Files\Microsoft SQL Server\90\DTS\Tasks to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Then it will show up in your References.
|||Graahhhhh!! I just came up with that idea, tried it out and it worked- graahh-- wasted so much time and was so frustrated by that yesterday because of something so simple- I hate that!

Programmaing with SSIS Object model

Hi there,

Can anyone point me to some sample source codes or any articles that describes how I can programmatically create a package which will import data from a flatfile (csv) to Sql server database.

I know there is some example that describes exporting data from sql server to flatfiles. Anyway I have failed to accomplish my goal by following those examples.

If anyone have a code snippet to do that please help me with that.

Thanks in advance

Moim Hossain

If you want, post the code you have already to build the SSIS package (or a link to it). This response is not the code snippet you're looking for, but it sounds like the package builder code you already have (from the other post), is not far from working.|||

jaegd ,

Sorry I did not understand. Should I post my code to you?

I have already posted my code here into another posting. you can take a look on my code here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073656&SiteID=1

Can you tell me where I am going wrong?

Thanks

Moim

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.

programatic transferdatabasetask in ssis

I am trying to code a package that runs a transferdatabasetask with the following code

Dim package As New Package()

package.PackageType = DTSPackageType.DTSDesigner90

package.Name = "transfer db task"

package.Description = "transfer db task"

package.CreatorComputerName = System.Environment.MachineName

package.CreatorName = System.Environment.UserName

Dim dest As ConnectionManager = package.Connections.Add("OLEDB")

dest.Name = "Dest"

dest.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'dest.ConnectionString = "SqlServerName=PDNCNLNJ1S\SQLSERVER2005;UseWindowsAuthentication=True;UserName=sa;"

Dim source As ConnectionManager = package.Connections.Add("OLEDB")

source.Name = "Source"

source.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'source.ConnectionString = "SqlServerName=NSW97V9F1S\NSW97V9F1S;UseWindowsAuthentication=True;UserName=;"

Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:TransferDatabaseTask"), TaskHost)

th.Name = "transfer db task"

th.Description = "The transfer task"

th.Properties("Action").SetValue(th, 0) '0: copy

th.Properties("Method").SetValue(th, 1) '1: destination online

th.Properties("DestinationConnection").SetValue(th, dest.ID)

th.Properties("DestinationDatabaseFiles").SetValue(th, "rgTemp1.mdf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,'';rgTemp1_log.ldf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,''")

'th.Properties("DestinationDatabaseFiles").SetValue(th, "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1.mdf;C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1_log.ldf")

th.Properties("DestinationDatabaseName").SetValue(th, "RGTemp1")

th.Properties("DestinationOverwrite").SetValue(th, True)

th.Properties("ReattachSourceDatabase").SetValue(th, False)

th.Properties("SourceConnection").SetValue(th, source.ID)

th.Properties("SourceDatabaseFiles").SetValue(th, "'rgTemp.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA','';'rgTemp_log.ldf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA',''")

MsgBox(th.Properties("SourceDatabaseFiles").GetValue(th).ToString)

MsgBox(th.Properties("DestinationDatabaseFiles").GetValue(th).ToString)

th.Properties("SourceDatabaseName").SetValue(th, "RGTemp")

Dim status As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

' If the package validated successfully, then execute it.

If status = DTSExecResult.Success Then

' Execute the package

Dim result As DTSExecResult = package.Execute(Nothing, Nothing, Nothing, Nothing, Nothing)

End If

'Dts.TaskResult = Dts.Results.Success

End Sub

The problem is that the package validation fails and the 'execute' statement never runs. the two message boxes report zero length strings in the source and destination files properties. It seems that the hard coded filenames that I have provided are not correct.

Can anyone shed any light on what is wrong here:

regards

Ray

What is the error message on validation?

When you add connection manager, the type should be "SMOServer" instead of "OLEDB". So, use something like:
package.Connections.Add("SMOServer")

Setting the properties is easier if you get the InnerObject from task host as in:
TransferDatabaseTask task = (TransferDatabaseTask) th.InnerObject;

For source and destination connections, you should set the name of the connection manager instead of ID. (I am not sure if using the ID is correct)

You have to escape '\' and " in the values for source and destination database file as
"\"rgTemp.mdf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";"\"rgTemp.ldf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";|||

Thanks for the reply

I tried your suggestions and got as follows

Using SMOServer invalidated the connection strings and I could only set them in a format that doesn't allow SQL Server login or a password

I escaped the file names in VB as double double quotes and that fixed the source and destination file name assignments

But the routine still returns 'failed' from the 'validate' call. there is no error message the 'validate' routine just returns 'failed' with no exception or message that I can find.

if i comment out the validate call the 'execute' call just returns failed with no exception.

I am not getting any help from the system here.

we have made a step forward here thanks to you but I am still stuck

Do you have any other ideas?

regards

Ray

|||To create connection manager you can use the following code:
connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=true;", serverName);
//connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=false;UserName={1};Password={2}",serverName, userName, passwd);

ConnectionManager connectionManager = package.Connections.Add("SMOServer");
connectionManager.ConnectionString = connectionString;
connectionManager.Name = connectionManagerName;

Programatic creation of SSIS packages

I am playing around with creating packages using C#. One major problem I have run into is controlling the location of the tasks I create in the package. I have found no way to access any properties to set this. Does anyone know how to do this?

Dave,

I don't think the ability to define tis exists in the API at the moment. if you think it should then ask for it at Connect (http://connect.microsoft.com)

I recommend you take a read of this though:

Extended properties...
(http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/13/6247.aspx)

-Jamie

|||Thank you, the extended properties look like they may do the trick.