9

I am trying to programmatically create an SSIS package containing a simple data flow from table A to table B in the same database. I am using the example given here. The package gets created and saved to a dtsx file, but when I open it in visual studio I see that the source and destination tables have not been selected.

I also want to insert a transformation task in between before it reaches the destination.

EDIT

Well I have managed to get the source and destination tables selected and the task even ran successfully. Turns out I needed to

(a) use one oleDB connection each for the source and destination [I don't like this way; I want to use one connection only]

(b) set the Accessmode to 3 in the destination SetComponentProperty("AccessMode", 3) which equates to Table or View - fast load.

Is there some documentation on this somewhere. All I can find is what SetComponentProperty does, and not what all parameters it can take to do that. eg. what else can I put there besides "AccessMode"?, what does the second parameter 3 or 2 or 1 stand for?

Still trying to map the monikers. STOCK:PipelineTask means DataFlow Task. What is the moniker for say... copy column?

Improve this question

3 Answers 3

Reset to default
3

Yes, this is one of my frustrations with SSIS: that they do not provide simple table or map of the new terminology.

You can use a small piece of code to discover components available for use.

The code is located here: http://msdn.microsoft.com/en-us/library/ms136106.aspx

When I ran it, I found the moniker for copy column to be: Name: Copy Column CreationName: DTSTransform.CopyMap.1

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

If you don't want to use the DTS .net assemblies you can use EzApi which provides a simpler syntax to create SSIS package programmatically.

EzAPI is a .NET library written in C# by Evgeny Koblov one of the testers on the SSIS team to abstracts away a lot of the cumbersome low-level coding needed to create SSIS packages XML directly in a programming language

EzApi was first published as a part of the SSIS community samples project created by Microsoft product team:

And later it is published as a separate project to add support SQL Server 2016:

To get starteed with EzApi there are many link on the internet that you can refer to such as:

Improve this answer

Comments

0

I used to have a copy of SQL Server 2005 Integration Services, a Wrox book. I am pretty sure it has a whole chapter or two dedicated to programmatically working with SSIS packages. You may want to check out a local book store and flip through it, it may have what you want.

Sorry I can't give you anything more specific than a book reference. Good luck.

Improve this answer

1 Comment

I would also look at Kirk Haselden's SSIS book - it is more programmer-oriented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.