Launching and monitoring SSIS packages from a web app

Has anybody developed a ASP.Net app that interfaces to SSIS? If so, what was your experience? Any pitfalls, tips, etc? We have a requirement to launch and monitor SSIS packages via a web interface.I've got the same question...

Launch SSIS package with SQL Event

Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?

You can use xp_cmdshell to call DTEXEC. You could also set up a job for the package and call sp_start_job.

See this link for some more detail and some other options: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

Large XML file source in SSIS?

Hi,

I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.

Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.

Is really SSIS the right tool for this operation?

The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.

Best regs,

//Patrick

Which SSIS approach did you try, the XML Task, or the dataflow with an XML source? Presumably it was the task, because of stock SSIS xml source component's inability to handle mixed content?

The XML Task, in my experience, croaks on large XML files, and also doesn't work in loops if there is a single failure.

On the other hand, I have used the XML source in an SSIS dataflow with relatively large files, 100Mb or so, without a problem, but have never tested with Gb+ sized files.

SQLXmlBulkLoad, on the other hand, works fine with large xml files, but there again, I'm not certain about mixed content. I have posted a script task which uses SqlXmlBulkload in another forum post.

Also, what version of SQL Server are you using, since there a number of additional options in 2k5?

Large Insert Causing Problem With TempDB

Hello,

I have an SSIS package that basically inserts a large amount of data into a SQL Server table. The table contains sixty five columns, and a single load of data can contain two million records.

The 'loads' are split up into several 'daily' flat files. The package uses a ForEachFile loop to process each of the files. As each file is processed, the data from the files is loaded into a SQL Server table (destination).

Apparently, as the package is running, tempDB begins to consume a lot of disk space. The data file for TempDB on this particular server is configured to grow in 50mb increments with unrestricted file growth. During the last run of the package, the data file grew to 17GB. I ran the following and got the data file size down to 50mb;

USE TempDb

GO

DBCC SHRINKFILE(tempdev, 1)

Should I consider incorporating this code as part of the package, or is there something else I should consider to configure the SSIS package so that I don't run into space problems with TempDB?

Thank you for your help!

cdun2

Be sure you are loading using "fast load" and set the Maximum Insert Commit Size to a reasonable value. (100,000 perhaps)|||

Thanks. I'll check into this.

cdun2

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.

Large Fixed width Text files using SSIS

What is the easiest way to get a large fixed width text file (200 columns) defintion into SSIS? To have to define each column with the ruler would be very cumbersome.

I am guessing many of those columns would have the same size. If that is true and you do not mind writing some code, configuring this connection manager programmatically would be relatively easy.

If you are not up to coding, it might be easier for you to go to the advanced page and click 200 times on the New button. After this you should be able to select al the columns that share settings (size, data type, etc) and set it in bulk for all selected columns.

HTH,

Bob

|||

Thanks Bob. Many of the columns will have the same length. I am up for some coding. Could you provide a shell for me to get started with? I assume if I write some code that I could read in the column names and column lengths from the file layout that I already have in Excel?

|||

Try searching this forum and documentation for samples. Here are a few I found:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56928&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=462356&SiteID=1

If you write the code you pretty much contol it, so you should be able to load external metadata definitions.

HTH,

Bob

|||I ended up building the XML string in Excel using the file layout that I had. This was a much easier way to do it compared to having to define each one in SSIS.