SSIS – rouge running packages

This week has been full of excitement here at cbtr.net.  We had ssis packages that were being executed via a sql jobs.  These jobs were taking forever to finish.  For all our other servers, there’s a Long Running Job setup via DPA to monitor but since many developer uses SSIS and DPA can’t send alerts to specific group of people, the alert was disabled.

Today, I got a message from a dev telling me that some of his packages were running for a long time, 18 hours long (that’s what she said), and it should of taken < 30 seconds (that’s not what she said).

Continue reading

SSIS: Data import lesson learnt

Yesterday, I was trying to import a flat file into a sql table.  The table has varchars and ints.  I had to use a data conversion tool to convert to string and 4byte unassigned so it the data type would convert correctly.  Except, SSIS was being a big douche and didn’t want to be nice.  I kept getting errors about the data type coversion for client_id is invalid and blah blah blah.

Continue reading

SSIS: Export more than 255 columns from SQL table to Excel

A few weeks ago, I was looking to export more than 255 columns from a table in SQL to Excel using SSIS.  I thought this task was going to a piece of cake because in SSIS, within the dataflow task, you get an Excel Destination task and this will take care of all my woes!  But alas, it didn’t and I have spent the past 2.5 weeks, not all 8hrs a day, looking for a solution.

Continue reading

SSIS: adding a dynamic date to filename

This past week, I worked a lot with SSIS, more often that I’m used too and I foresee working on it a lot more in the future.  It is actually a pretty cool application.  I don’t know why I shyed away from it before.

So this past week, I had a bunch of files that were being created via SSIS and one of the requirements were that the file names had to have a specific timestamp as its suffix followed by a .xml extension.

In SSIS, I created a variable and gave it the file name. In the filename connection manager, I gave it an expression.  The expression look as followed:

Continue reading