SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Foreach Loop (Text Files as Flat File Source and Excel files as Excel File Destination)


Foreach Loop (Text Files as Flat File Source and Excel files as Excel File Destination)

Author
Message
kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50327 Visits: 21152
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50327 Visits: 21152
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
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.

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50327 Visits: 21152
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 ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
which variable do you think will be useful for Excel Connection Manager? The OutputFileName variable?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50327 Visits: 21152
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
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.

kpann
kpann
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 79
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search