July 27, 2012 at 4:24 am
I am newbie for SSIS and found out that we cannot map columns directly into SSIS data flow. I have below requirements:
1. Create a table which will contain per record set: simple sql query with aliases, destination path including file name where flat file needs to be placed.
2.a. Create SSIS package which will fetch individual record containing SQL query and destination path
b. Pass these values to Data Flow task into source variable
c. somehow get these columns mapped dynamically to flat file destination.
There are more than 20 such records. With difference in SQL queries and destination. Any suggestions would be appreciated.
July 27, 2012 at 5:37 am
Forget SSIS and use the bcp utility.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 27, 2012 at 5:43 am
Is this a one-off or will this be a reoccurring thing?
If reoccuring, will the 20+ records always have the same dynamic format?
July 27, 2012 at 6:57 am
Yes I think this should work out with BCP Utility
And Yes this is recurring process, with same table from where queries and destination path will be picked up dynamically and used to generate flat files.
July 30, 2012 at 7:27 am
aniruddh123 (7/27/2012)
And Yes this is recurring process, with same table from where queries and destination path will be picked up dynamically and used to generate flat files.
Not sure if we're talking about the same thing, but to double-check.
The 20+ files all have different formats from each other. Now, will the same 20+ files be picked up every time? Will each individual file have the same format as it had the last time?
If so, you can actually code SSIS with multiple source connections to handle the issue.
July 30, 2012 at 8:25 am
Yes the format is same, using pipe separator.
But its dynamic thing, based on table, there will be enabled flag, if its 1 then only pick up those queries to export into file.
So if tomorrow I set 10 reports as enabled = 1, I should get 10 reports as per their queries.
The columns can be dynamic.
I may add more than 20 records into the table for new reports.
So I should not change SSIS package every time I have new query.
Because the task is to accomplish exporting of data, irrespective of column.
If we use expressions and perform task in SSIS it might be complex than BCP I guess.
Any drawbacks of BCP against SSIS for such kind of requirement ?
July 30, 2012 at 11:44 am
aniruddh123 (7/30/2012)
Yes the format is same, using pipe separator.....
The columns can be dynamic.
Okay, these two statements contradict each other. Either the format is the same, or it's different. I wasn't referring to the delimiter. I was referring to the column datatypes and lengths on each individual report/file. Since you are saying the columns change, then I will assume your answer to my question is actually "no."
I may add more than 20 records into the table for new reports.
But its dynamic thing, based on table, there will be enabled flag, if its 1 then only pick up those queries to export into file.
So if tomorrow I set 10 reports as enabled = 1, I should get 10 reports as per their queries.
I do not understand this bit at all. Are you doing data exports that need to be transformed and loaded to another database, or are you doing reports? Reports can be done through SSRS (Reporting Services) and set up as subscriptions to send to people.
So I should not change SSIS package every time I have new query.
Of course not. I never said you should. I was merely recommending setting up all possibilities in the package so you would never have to fiddle with it. But your requirements confuse me, so I'm not going to make any additional recommendations. Otherwise, I might accidentally make your situation worse.
August 9, 2012 at 10:22 am
1. Create a table which will contain per record set: simple sql query with aliases, destination path including file name where flat file needs to be placed.
2.a. Create SSIS package which will fetch individual record containing SQL query and destination path
b. Pass these values to Data Flow task into source variable
c. somehow get these columns mapped dynamically to flat file destination.
#1 Suggests you'll have a table with queries and destination file name. I'd pull them using oledb_source.
#2 Connect the source in #1 to a ScriptComponent. Use C# code to Open OleDbConnection, run the query using command object, write it out as a csv file. You'll have to manage the append-ability of such file. Don't forget to close connection and file.
Does it make sense?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply