Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Foreach Loop (Text Files as Flat File Source and Excel files as Excel File Destination) Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, 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.
Post #1436074
Posted Thursday, March 28, 2013 3:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1436324
Posted Thursday, March 28, 2013 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, 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?
Post #1436403
Posted Thursday, March 28, 2013 6:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1436442
Posted Thursday, March 28, 2013 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, 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.
Post #1436471
Posted Thursday, March 28, 2013 8:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1436479
Posted Thursday, March 28, 2013 8:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, Visits: 79
which variable do you think will be useful for Excel Connection Manager? The OutputFileName variable?
Post #1436483
Posted Thursday, March 28, 2013 8:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1436485
Posted Thursday, March 28, 2013 11:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, 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.
Post #1436638
Posted Thursday, March 28, 2013 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, 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!
Post #1436654
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse