March 27, 2013 at 11:52 am
Hi,
I'm pretty new to SSIS. Is it possible if I already have those existing text files (a_points.txt, b_points.txt, c_points.txt) and then create those same file names in excel so they will be like this: a_points.xlsx, b_points.xlsx, c_points.xlsx and so on? I already have Foreach loop container and I placed the data flow task inside it. On the data flow, I have a dynamic flat file source connected to data conversion then derived column and finally excel destination. I also added a variable to the watch list, it successfully loops through text files and writes each output to an excel file (I also tried to make this dynamic but no success so far) but I want multiple outputs. For example, each ouput from a_points.txt flat file source will go in a_points.xlsx excel destination, the next file b_points.txt will go in b_points.xlsx...any idea? i need help...i've been struggling with this for a couple of weeks now.
March 28, 2013 at 3:13 am
This might work ...
1) Create a string package-scoped variable called ExcelFilePath (this will correspond with the ExcelFilePath property of your Excel destination)
2) Use a script task in your Foreach loop to populate ExcelFilePath, for every iteration of the container.
3) Use an Expression in your Excel destination to set the ExcelFilePath property to whatever is contained in your variable.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 28, 2013 at 5:33 am
I appreciate your response but do you think you could give me an example in your package? Do you think that ExcelFilePath will change dynamically for every iteration?
March 28, 2013 at 6:44 am
kpann (3/28/2013)
I appreciate your response but do you think you could give me an example in your package? Do you think that ExcelFilePath will change dynamically for every iteration?
Sorry, don't have time to do that at the moment, but yes - if you use variables and expressions in the way I described it certainly should change with every iteration of the FEL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 28, 2013 at 7:52 am
I have placed the script task inside the FEL. I have 3 package variables named Directory, FilePrefix, and OutputFileName and they all are strings. In script task editor, the read only variables are Directory and FilePrefix and the read write variables is OutputFileName.
The code in the script:
Dim sFinalFileNameAndPath as String
Dim sDirectory as String
Dim sFilePrefix as String
sDirectory = Dts.Variables("Directory").Value.ToString
If Not (sDirectory.EndsWith("\")) Then
sDirectory = sDirectory + "\"
End If
sFilePrefix = Dts.Variables("FilePrefix").Value.ToString
sFinalFileNameAndPath = sDirectory + sFilePrefix + ".xlsx"
Dts.Variables("OutputFileName").Value = sFinalFileNameAndPath
Dts.TaskResult = ScriptResults.Success
P/S: I don't have any idea how to get those outputs from each text file (flat file source) written to each excel file (excel destination).
Phil Parkin (3/28/2013)
kpann (3/28/2013)
I appreciate your response but do you think you could give me an example in your package? Do you think that ExcelFilePath will change dynamically for every iteration?Sorry, don't have time to do that at the moment, but yes - if you use variables and expressions in the way I described it certainly should change with every iteration of the FEL.
March 28, 2013 at 8:03 am
Excellent - now follow part (3) of my advice. Edit your Excel connection manager (not the Excel destination) and open up the Expressions property.
Create an expression for the ExcelFilePath property - set this to your new variable.
Fingers crossed ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 28, 2013 at 8:08 am
which variable do you think will be useful for Excel Connection Manager? The OutputFileName variable?
March 28, 2013 at 8:11 am
I'm using 2012 - maybe it's different if you are on an earlier version - hopefully it should be obvious, if you already have a connection configured.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 28, 2013 at 11:44 am
Hi again,
Oops I posted the topic in wrong forum. I'm currently using SSIS 2008 but my database team plans to upgrade to 2012. Anyways, I set up a variable in ExcelFilePath on Excel Connection Manager and I got three errors that said:
"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80040E37."
"Opening a rowset for 'Excel_Destination$' failed. Check that the object exists in the database."
(I checked all my excel files to see if it has Excel_Destination$ in it, it's still there)
"SSIS Pipeline Error: "component 'Excel Destination' (2625) failed validation and returned validation status "VS_ISBROKEN".
I tried to fix this by clicking on Excel Destination to bring up the Excel Destination Editor, I noticed that in name of the excel sheet drop down list - there are no tables or views.
Phil Parkin (3/28/2013)
I'm using 2012 - maybe it's different if you are on an earlier version - hopefully it should be obvious, if you already have a connection configured.
March 28, 2013 at 12:29 pm
I got it fixed by replacing the data flow and I was doing the same thing. It now accepts a variable in ExcelFilePath and it runs fine except it doesn't do anything (each output from a text file did not go in each corresponding excel file). I'm one step closer to solving the problem....argh! :crazy:
March 28, 2013 at 12:29 pm
Without actually setting this up for myself, I am not sure how much more help I can offer.
Do all of the spreadshhet files already exist?
Have you tried using breakpoints to check that everything is being set as expected?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 28, 2013 at 12:36 pm
Yes, I currently have two breakpoints (onpostexecute and onvariablevaluechanged) and all the spreadsheets exist. i think it's one of my variables that didn't change dynamically.
Phil Parkin (3/28/2013)
Without actually setting this up for myself, I am not sure how much more help I can offer.Do all of the spreadshhet files already exist?
Have you tried using breakpoints to check that everything is being set as expected?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply