January 5, 2010 at 10:53 am
Hello all, I'm hoping I can explain this well enough to have someone help me with this task. I have a database table that gets updated each week with data from the prior week. There is a field in this table that contains the week ending date and I need to create a text file that contains the max week ending date as part of the file name. So
for instance Test_01/02/2009. Test will not change but 01/02/2009 will. Another group will be building a process to look for the previous week ending date so a process can be kicked off so the text file does not need to be populated with any data but if it is that is no problem. I know I need a variable but I have tried creating one and can't get it to work. I have never used variables in SSIS so I need instructions.
I would be so happy if someone could help me.:-D
January 5, 2010 at 12:06 pm
There are a few ways to do this. I'll explain the simplest way in detail and give you a brief expalation of other options.
The simplest way to do this is to add an Execute SQL task prior to your Data Flow that creates the file. The SQL Code in the Execute SQL task should get the MAX(YourDate) from the data set that the data flow will be using.
Configure the ExecuteSQL task as such:
--Result Set = Single Row
--Connection Type/Connection = your connection manager object
--SQLSourceType = Direct Input
--SQLStatement = SELECT MAX(YourDate) FROM YourTable GROUP BY ??
----I'm trusting that you can write the SQL Statement to get you the last date value
On the Result Set tab, Add a result set and set the properties as such:
--Result Name = 0
--Variable Name = Your Variable, for example User::MaxDate
Now you'll have the max data value from your SQL statement inside the variable. Next, in your Data flow, use an Expression to set the ConnectionString property of your Flat File Connection manager. You'll need to CAST your data variable to STR in the expression. Here's an example of how the expression may look:
"Test_" + (DT_STR, 10, 1252) @[User::MaxDate]
January 5, 2010 at 12:54 pm
Thank you, this is almost exactly what I've done but I'll try again.
January 5, 2010 at 1:21 pm
The Execute SQL task can be a bit tricky to work with when you are trying to return values back into variables. Everything has to be set up just right or it will fail. Here's a good read on the task that I think will help you:
January 5, 2010 at 1:23 pm
Thank you so much for taking the time to help me. I think the article is just what I need.
January 5, 2010 at 1:26 pm
No problem. Read through the article, give it a shot, and then post back with your results. If we need to work through it, we will; otherwise, it will be good to know that your problem has been solved.
January 5, 2010 at 1:29 pm
I certainly will get back to you. I use SSIS all the time and I have used simple variables but not expressions. I will post back when I'm successful. I have some other priorities so I may not get back to this right away but I will certainly work on it shortly.
January 5, 2010 at 6:00 pm
I read the article and followed your instructions and Execute SQL task works just fine but the Data flow task doesn't. I get the following error message.
[Connection Manager "CreateFlatFile"] The File name "Test_1/5/2010" specified in the connection manager was not valid. Error at Package Connection create flat file.
The name of the file should actually be Test_1/2/2010 because that is the max date in the file.
January 5, 2010 at 10:18 pm
You'll probably want to slightly reformat that date - you can't created a windows file with any of these characters in the name: / \ : * ? < > |
Maybe use a SQL convert method, push into a YYYYMMDD format so Test_1/5/2010 would become Test_20100105 - or any other format you like, just without the slashes.
HTH,
Steve.
January 6, 2010 at 7:13 am
Thank you I will try that.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply