June 25, 2015 at 1:22 pm
Hi all,
I have a csv file that I get every month that includes total counts of something by month. The file has fields that correspond to monthyear and counts of something during that monthYear. I want to import them using SSIS into SQL 2012 but would like to avoid having to rebuild the Connection managers, as well as source and destinations every month due to a new field (current MonthYear) being added. The flat file I receive starts in Jan-2000 and goes to the current MonthYear.
Is there a way to configure the connection manager to just accept this extra field without me having to reconfigure it? I'm running the package from Visual Studio for now but if I deploy it I'd like to not have to make a change a re-deploy every month just because a new field is in the flat file.
Thanks in advance
June 25, 2015 at 1:36 pm
Oh and I can't just grab the latest data and append it onto the table because the counts may change for any MonthYear.
June 25, 2015 at 1:46 pm
You'll have to manually update the connection manager each month.
You could use a script task to split the files into a number of files, one for each year, each one having columns for the 12 months, and use the same connection manager for each file.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 25, 2015 at 1:56 pm
Sounds like the format of the input file is changing every month. The source of that file needs to realize how much effort is involved for you and use individual lines to represent a YearMonth value and the associated count. Otherwise you have no way to avoid the monthly changes. At some point in the future, that text file is going to get rather seriously wide and be a potential problem. I'm pretty sure that just adding a field every month is not sustainable longer-term. The wider those columns are, the sooner the problem shows up.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 8:42 am
Thanks for the replies Steve and Alvin. Steve in an ideal world my source would hand deliver to me data in a format that works best but unfortunately they don't seem to want to change things despite the obvious issues this causes. The file works, they send smallint values, and is very readable in excel. With a Excel 2010 column limit of around 16000+ (my file is about 180 columns currently) they won't have to worry about this issue for a very long time. Of course that means I have to workaround it.
Alvin your idea of breaking up the file into pieces has promise, though I'm not much of a C# or VB script programmer. Probably easiest just to refresh the Connection Manager but can't blame a guy for trying....
Thanks again to both of you for your time.
June 26, 2015 at 9:07 am
j.portolese (6/26/2015)
Thanks for the replies Steve and Alvin. Steve in an ideal world my source would hand deliver to me data in a format that works best but unfortunately they don't seem to want to change things despite the obvious issues this causes. The file works, they send smallint values, and is very readable in excel. With a Excel 2010 column limit of around 16000+ (my file is about 180 columns currently) they won't have to worry about this issue for a very long time. Of course that means I have to workaround it.Alvin your idea of breaking up the file into pieces has promise, though I'm not much of a C# or VB script programmer. Probably easiest just to refresh the Connection Manager but can't blame a guy for trying....
Thanks again to both of you for your time.
The larger problem might be what you load the data into. You have 180 columns now, but the limit for a "non-wide" table is 1,024. Admittedly, that's 70 years down the road, but the chances of human error mucking up this spreadsheet long before then increase with every additional column. Troubleshooting a data problem is quite likely already impractical. I would set up a VBScript to process this thing as soon as I could. At least that way, you can loop across the existing sheet, and column by column, you add the data and the date to the next empty cell in a second "sheet" that gets created by the VBScript at the beginning, so that you end up with rows instead of columns, and then the import job no longer needs monthly maintenance. This is the kind of thing I could crank out in a weekend. It could be set up as an independent VBScript, or as a Script Task within SSIS, or possibly as VBA code within Excel. It is bigger than a breadbox, but it's not total rocket-science. Give it a shot and if you have VBScript or VBA questions, bring those back here and I'll answer them.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply