Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to read Flat File data from a Variable?

Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

Simple answer is no. I'd question why you want to do this though as any volume of data in a variable will not perform well.

A Script component could act as a source though and output rows and columns. A Script Task could also be used to write the variable data to a file, and then just use the normal source.

|||

Darren,

Thanks for the quick response.

I realize that performance wont be the best but I was wondering more from the point of view of taking XML data that has been transformed using XSL and feeding that straight into a table.

I realize that I was going down the wrong path as I should not have the XSL convert to a flat file format but keep the data in XML format and then use the XML Source's read from variable capability.

Keeping the data in a variable is going to eat lots of RAM but hopefully avoiding the write/read to disk will give a significant enough performance gain to justify it.

Thanks,

-dhideal

|||

The disk cost may be less than the cost or marshalling data in memory.

I have seen similar situations where it has been cheaper to stage data through files, because that way you can use the pipeline which ideally only works on a buffer full of data at one time (not quite true, but the point is there). Therefore you don't have the pain or finding a lot of memory, you just work in piecmeal sections.

In summary the cost of writing and reading to and from a file can be far outweighed by the overhead of finding a lot of memory in one go. This does of course depend on hardware and data volumes, but you may want to try both methods.

|||

Its also worth saying that raw files can be screamingly fast. I've just constructed a quick and dirty demo where a raw file containing 10000000 records of width 270 bytes (nearly 3GB of data) was read from disk and pushed into a rowcount component in 68seconds. That's fast. When I pushed the same data into another raw file (i.e. writing to disk) it took 175seconds. Again very impressive, all the more so considering the files were on the same physical disk.

Definately worth a look.

-Jamie

|||

Jamie/Darren,

Thanks for the advice. I will definitely try out both options but I believe staying in memory might be better for me because the XML files I'm working with aren't huge but there are just a lot of them.

-dhideal

Is it possible to make INSERT/UPDATE operation in SSIS?

Hi!
I use SSIS to insert some data from text sources to SQL server 2005. I use
check constraints option. Is it possible if iserted record has the same
primary key as existing record in table to replace existing record? How to
make it?
Thank you
Igor A. ChechetIgor
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM Table WHERE id=@.id)
BEGIN
UPDATE Table SET col=...,col2...c,ol3=... WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO Table (cols here) VALUES (here)
END
COMMIT TRANSACTION
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:uVHJyuxpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet
>|||Igor,
First better to import to a staging table all the records .
You can write a query which checks the existence of a record on Primary
Key
UPDATE TABLE SET COL1= STAGING.A1,
COL2 = STAGING.COL2
...
...
FROM TABLE , STAGING
WHERE TABLE.PK - STAGING.PK
INSERT INTO TABLE
SELECT * FROM STAGING A
WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
Note: PK is PRIMARY KEY
M A Srinivas
Igor A. Chechet wrote:
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet|||There's no need to drop to an intermediary table. You can do this in the
pipeline.
Here's how: http://www.sqlis.com/default.aspx?311
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethomson/
<masri999@.gmail.com> wrote in message
news:1152880213.339951.42940@.i42g2000cwa.googlegroups.com...
> Igor,
> First better to import to a staging table all the records .
> You can write a query which checks the existence of a record on Primary
> Key
> UPDATE TABLE SET COL1= STAGING.A1,
> COL2 = STAGING.COL2
> ...
> ...
> FROM TABLE , STAGING
> WHERE TABLE.PK - STAGING.PK
> INSERT INTO TABLE
> SELECT * FROM STAGING A
> WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
> Note: PK is PRIMARY KEY
> M A Srinivas
>
>
> Igor A. Chechet wrote:
>

Is it possible to make INSERT/UPDATE operation in SSIS?

Hi!
I use SSIS to insert some data from text sources to SQL server 2005. I use
check constraints option. Is it possible if iserted record has the same
primary key as existing record in table to replace existing record? How to
make it?
Thank you
Igor A. ChechetIgor
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM Table WHERE id=@.id)
BEGIN
UPDATE Table SET col=...,col2...c,ol3=... WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO Table (cols here) VALUES (here)
END
COMMIT TRANSACTION
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:uVHJyuxpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet
>|||Igor,
First better to import to a staging table all the records .
You can write a query which checks the existence of a record on Primary
Key
UPDATE TABLE SET COL1= STAGING.A1,
COL2 = STAGING.COL2
...
...
FROM TABLE , STAGING
WHERE TABLE.PK - STAGING.PK
INSERT INTO TABLE
SELECT * FROM STAGING A
WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
Note: PK is PRIMARY KEY
M A Srinivas
Igor A. Chechet wrote:
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet|||There's no need to drop to an intermediary table. You can do this in the
pipeline.
Here's how: http://www.sqlis.com/default.aspx?311
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethomson/
<masri999@.gmail.com> wrote in message
news:1152880213.339951.42940@.i42g2000cwa.googlegroups.com...
> Igor,
> First better to import to a staging table all the records .
> You can write a query which checks the existence of a record on Primary
> Key
> UPDATE TABLE SET COL1= STAGING.A1,
> COL2 = STAGING.COL2
> ...
> ...
> FROM TABLE , STAGING
> WHERE TABLE.PK - STAGING.PK
> INSERT INTO TABLE
> SELECT * FROM STAGING A
> WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
> Note: PK is PRIMARY KEY
> M A Srinivas
>
>
> Igor A. Chechet wrote:
>> Hi!
>> I use SSIS to insert some data from text sources to SQL server 2005. I
>> use
>> check constraints option. Is it possible if iserted record has the same
>> primary key as existing record in table to replace existing record? How
>> to
>> make it?
>> Thank you
>> Igor A. Chechet
>

Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.sql

Is it possible to have SSIS flatten XML data?

Hi,

I have to take a hierarchical XML file and store it into a table.

I'm looking at the XML Source and it shows me all of the various XML tags as separate tables. I know that a lot (if not all) of the data can be denormalized so that I have flattened records in one table instead of multiple tables.

I was hoping the XML source would allow me to designate how to denormalize the data but it doesn't seem like I can without using a bunch of sorts and merges in the data flow to denormalize it which I'm trying to avoid.

Does anyone know if SSIS has any easy way to flatten XML data? I know about XSL transforms but I was hoping that there might be an easier method.

Thanks,

-dhideal

The pivot transform wil denormalise data so perhaps that could help you in some way. Fundamentally though you can't stop the XML source from producing multiple outputs because that is what it does - converts input into something that SSIS understands.

-Jamie

|||Couple of ways to flatten XML in SSIS. First off, flattening it using the stock SSIS source adapter is quite arduous, because of all the sort/merges. The more hierarchical, the worse off the the morass becomes. It rapidly becomes unmaintainable.

4 ways to do this, with various degree of difficulty (a fifth non SSIS solution thrown in for good measure)

1. XSLT, as you mentioned. Not too bad, dependency on your profienciency (and/or antipathy) for XSL.

2. Build a custom source adapter and use .NET 2.0 XPathExpressions to load the pipeline.

3. Build a custom source adapter based on an XmlSerializer:
To do so, get the XSD, convert the XSD to an XmlSerializer calss with xsd.exe as follows
xsd sample_schema.xsd /classes /language:vb

4. Build a custom source adapter based on a dataset
xsd sample_schema.xsd /dataset /language:vb

5. Load it into SQL server and use XQuery (not an SSIS solution)|||

jaegd,

Thanks for the pointers.

It seems though that I'm not understanding something because I looked up the items you mentioned and it doesn't seem like they accomplish what I want. The following is my understanding of things (which is most likely incorrect):

1) XSLT requires the use of XPath to identify the various tags to be processed and the structure of the XSL document will take care of the flattening.

2) Based on some information I found, XPathExpressions are just compiled XPath statements. I don't understand how this would flatten the data. It seems I would have to have some custom code in the source adapter to take the results of the XPathExpressions searches and join them together to flatten the data.

3) Based on what I read, it seems like I would have to deserialize the XML data in order to be able to access it. The deserialization would then restore the original hierarchichal structure so I'm not quite sure I understand how this would flatten the data. It seems like if I could use the serialized version fo the data it might get me what I need.

4) Based on what I read, the dataset would be comprised of datatables and their relationships. It seems like I would then have to identify the relationships in the custom code to identify how the tables should be linked together and then denormalize the data by joining them together in the source adapter. I'm not quite sure how this would be different from using suggestion 5 except for the benefit of not having to load the data into SQL Server first.

5) We're trying to avoid this particular solution for now if we can.

Once again, thanks for the response and the pointers. I'm sure I have misunderstood some of the items as I have not really worked much with XML data prior to this.

-dhideal

|||It seems you understand it quite well. To flatten, though must join and/or aggregate.

Flattening XML is custom, and there is no "push to flatten" task or component in SSIS, for lack of better term.

Now, I have tested all five of these approaches, and used two of them

(XPath,XmlSerializer, leaving out XQuery for the moment). They all

require you to essentially do XML "joins" via custom code, whether that

code is a set of XPath expressions in a custom adapter, a set of

property "gets" in in a custom adapter.

Had Microsoft shipped a client side XQuery implementation, I would have

recommended that, since XQuery allows you to do XML joins, which is

basically what you're looking for.

Is it possible to ftp files using code in a SSIS Script Task?

Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.

I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.

Thanks,

MShah

In the Control Flow you should be able to use a ForEach loop to loop over all the files. Then you can use a script task (XML task might also work but not sure) to extract the information needed to create the path & filename to FTP.

You would store the create filename in a Variable and then do the FTP task in the loop to upload the files. Anyone know if the FTP task will connect to FTP once in this type of loop?

Fred

sql

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie

Is it possible to access data associated with a hyperlink in SSIS?

I need to be able to to extract the data that is returned by clicking a hyperlink. I click the link and it displays the data.

I know that SSIS has a web services task, and that appears to be a neat feature, but the link I am connecting to does not have a WSDL file or anything like that. So I think I cannot use this task.

Does anyone have a suggestion about how I might do this?

Thanks for your help,

Harold

What data that is returned by clicking a hyperlink?

What is the hyperlink hosted in?

What defines or restricts this data?

What do you expect to happen to this extracted data?

SSIS is a backend ETL tool, I cannot work out what you would expect it to do for you, in what I am guessing is some kind of web UI

|||

Hello Darren,

Thanks for responding to my post.

I want to read the data that are displayed when I click a hyperlink.

When the package is executed, it should go to the hyperlink (URL) and get the data it displays.

The data that are displayed are:

var imgRates = {"ThirtyFixed":"6.298","FifteenFixed":"5.926","ThirtyFixedJumbo":"6.339","FiveOneARM":"5.831"};

I want to read that string into my package. I will then parse and store it.

Do you know if this can be done?

Thanks,

Harold

|||Your data looks to be a line of code. It doesn;t look very big, and not really what i woudl expct for an ETL source. Just write this in your programming language of choice, forget about SSIS.|||Here is some code that will go to a URL that you provide and read the content into a string.

Code Snippet

Dim webRequest As System.Net.WebRequest = System.Net.HttpWebRequest.Create(Dts.Variables("Url").Value.ToString())
Dim webResponse As System.Net.WebResponse = webRequest.GetResponse()
Dim stream As System.IO.Stream = webResponse.GetResponseStream()
Dim streamReader As New System.IO.StreamReader(stream)
Dim content As String = streamReader.ReadToEnd()

|||

Hello Jay,

The code snippet was just what I needed!

Thanks for your help, and for the others who took time to repond.

Harold

Is it possiable to store password encrypted to an external config file?

I got a problem when developing SSIS packages.

For security reason, the sensitive information must be encrypted, and they should be configurable dynamically (by an ASP.NET application).

so I tried several ways to achieve this,

1. use SSIS Package Configurations to generate an XML config file
It's a convenient way to generate config file. but the passwords are not encrypted (or I don't know how).

2. use Variables and Property Expressions
I can set variables by reading from an external custom xml config file which was content encrypted (read and decrypt the custom config file in a script task). but in a FTP Connection Manager entity, the Password property can not be set via Property Expressions.

Is any way to store password encrypted to an external file?
OK, It was resolved, I try to set Variables from a custom XML config file. Most of Properties of Connection Managers can be set via Property Expressions but password. So I set the password in a Script Task programatically. It will like this:

Public Sub Main()
'a custom class to read my custom config file
Dim c As Config = Config.GetConfig()

Dts.Variables("FtpServerIP").Value = c.Settings("FtpServerIP")
Dts.Variables("FtpServerPort").Value = Convert.ToInt16(c.Settings("FtpServerPort"))
Dts.Variables("FtpServerLogin").Value = c.Settings("FtpServerLogin")
Dts.Variables("FtpServerPassword").Value = MyDecryptMethod( c.Settings("FtpServerPassword") )

Dts.Connections("FTP Server").Properties("ServerPassword").SetValue(Dts.Connections("FTP Server"), Dts.Variables("FtpServerPassword").Value)

Dts.TaskResult = Dts.Results.Success
End Sub

Now I can store and encrypt my password or other sensitive information in a custom XML config file, and change it dynamically. It looks quiet complex, but it's the only way I know.

Any good suggestion?

IS IT Possbile to Invoke DATASTAGE (oracle Package) Job in Dot Net .

Hi ,

Anyone help me. Now we can do Invoke SSIS Package into ASp.NET.

Vice versa

Is there Possbile to invoke Datastage (oracle Job) into ASp.net? . Is already created in Oracle DataStage Server. it call or access through Dot net. Is it possble? Please any one help me.

Thanks & Regards,

Jeyakumar.M

chennai

What are the interfaces to run a DATASTAGE package. Web service, COM, OLEDB. Any of these would enable you to start the package from SSIS. The script component can do anything you can do in .Net

Is it ok to post a SSIS contract vacancy on this forum?

Hi,

I wonder if anyone could tell me whether it is acceptable to post a SSIS contract vacancy on this forum?

If not, where would be the best forum/site to find UK-based SSIS developer contractors?

Many thanks,

Gavin.I do not beleieve it is acceptable or the right place. If you are looking for places to post resumes perhaps you want to look here .|||Also many technology events and fairs are a great place to scout for people especially if they are pertaining to your requirements. .|||Hi Marc,

Thanks for taking the time to reply to my post.

I do not beleieve it is acceptable or the right place. If you are looking for places to post resumes perhaps you want to look here .

I thought that might be the case. It's a shame though: I can't help but think that the kind of person who is actively involved in an SSIS forum is much more likely to be the kind of person I'm after than someone who just happens to mention SSIS on their CV on a generic jobsite... I'm after a real hardcore SSIS developer so this forum just seemed like the ideal place to look.

Also many technology events and fairs are a great place to scout for people especially if they are pertaining to your requirements.

Unfortunately I need someone to start yesterday - so technology events and fairs (whilst a good suggestion for planned permanent employees) are not really feasible in this case.

Thanks,

Gavin|||

You could contact the webmaster at www.sqlis.com - they may be interested in having a vacancies page. Then again, they may not. There can be privacy issues.

Have you tried the UK SQL Server User Group. http://www.sqlserverfaq.com/ ?

Or the local PASS chapter? http://careers.sqlpass.org/home/index.cfm?site_id=400

Or perhaps even http://jobcenter.ittoolbox.com/ ?

Donald

is importing a dtsx file Necessary

Ok, I'm actually adding a SSIS job to my job agent on my test SQL server. Noticed that when I go to my job agent --> add new job, under the steps option, I click new. this then takes me to the new job step window. When I select

Type as SQL Server Integrated Services, I then see some new tabs at the bottom of the form. Under package source I can select File System, SQL Server, or SSIS Package Store, then I have to select the location of the dtsx file.

So my question is, since I can select the actual file (package) I want to run from here, do I really have to import a package to the file system or MSDB under the SQL Integration Services on the server?

It appears to me that its kind of the same thing.

I'm new to this SSIS, SQL DB work, so I'm learning as I go. . . .Yes, you do. If the file doesn't reside on the server, how will the agent job be able to find it?

I believe that when you select filesystem in the Agent job step, it's showing you your local filesystem, not that of the server.

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.