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).
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.
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.
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: