Metadata issues! Help!
-
I have found out that the metadata within an SSIS package can not be changed at runtime. This is a serious problem for me because I need to find a way to manage the monthly export of data to Excel from a table whose column names change every month. The table is created dynamially in a stored procedure and the column names change to reflect what month the script was run. Is it even possible to address this issue using SSIS? If not, does anyone have a suggestion what else I can do?
-
What if you ouput the data to excel using a dynamic parameterised sql command so that it always has the same input names, even if the column name it goes to changes? You will probably have to turn off ValidateExternalMetadata for the output component.
Not sure if it will work or not, but worth a try.
-
You could write a data source script component that created a standard collection of output columns and took care of mapping the stored procedure variable column names to the standard columns. The name changes are then hidden in your code, and all SSIS column metadata is static.
A DataReader component might be used to accomplish the same thing.
Is it reasonable to spool the stored proc results to a temp table? Then you can either use ALTER TABLE to rename the temp table columns, or dynamically generate a statement like "SELECT <VarCol1> as StdCol1, ... FROM #tmpResults" to avoid the changing metadata problem.
-
Thanks for your ideas--they have given me a few more approches to try out
Humm... as an experiment I changed the stored procedure so that the input table always has the same column names. However, when the data is exported into Excel, the spreadsheet needs to have the column names reflecting the specific month and year for which the report was run...
How do you go about using the dynamic SQL statement to create the Excel table? I need to use variables to set the column names, but all the assistance I find on Google is about how to set Where parameters dynamically--not what I need...
Any ideas?
-
You could set up the Excel destination with the "First row has column names" box unchecked. Then you need to add a row with the variable column names first, followed by the actual values.
If the stored procedure output was in a temp table with the variable column names, and all column datatypes can be implicitly converted to varchar(50), this query would do it (example shows eight columns).
SELECT MAX(CASE WHEN colid=1 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL1,
MAX(CASE WHEN colid=2 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL2,
MAX(CASE WHEN colid=3 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL3,
MAX(CASE WHEN colid=4 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL4,
MAX(CASE WHEN colid=5 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL5,
MAX(CASE WHEN colid=6 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL6,
MAX(CASE WHEN colid=7 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL7,
MAX(CASE WHEN colid=8 THEN cast([name] as varchar(50)) ELSE NULL END) AS STDCOL8
FROM tempdb.dbo.syscolumns WHERE id = OBJECT_ID('tempdb..#xyz')
UNION
SELECT * FROM #xyz
Then the problem is how to get the stored proc results in the temp table. Using "INSERT INTO #xyz EXEC storedproc" only works if the destination table has been created in advance, with the desired column names.
I think the most practical solution to make this work as a dataflow may be the custom script data source component. Populate a DataReader with the results of the stored procedure, then fill the first output row (with fixed column names) with the variable column names from the DataReader. Then copy all the rows from the DataReader, converting every column to a string datatype.
Another option may be SQLCMD. You can use it to sent the stored procedure output to a tab-delimited text file, but if you give it the extension ".xls" then it will open in Excel. It will probably have the goofy row of hyphens between the column headers and the data, but if you can live with that this is the simplest way. Forget column metadata, you just need an Execute Process control step that runs the command:
SQLCMD -S <server> -d <database> -Q "EXEC <stored proc>" -s " " -W -o "<destination XLS file>"
The [ -s " " ] part specifies the column separator, it should be a single tab character and not the two space characters you'll get if you cut and paste it.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply