Help With Trying to Import File Names into a Table
-
Me again...
I have a small SSIS package which is using a ForEach container to iterate through .txt files to import data into a staging table. This actually works quite fine and gave me no issues. However, I just found out that I have to save the file names as well. I've researched this and found that I can use the Derived Column task, however it returns an empty string for the file name so that the column in the table is blank. Also, i'm not sure how to get it into the same staging table with the data, right now i'm trying to put the file names into a separate tale just to get it working.
I'm not sure where to start. Tutorials and explanations aren't specific enough and i'm forced to guess. I have a data flow task inside the ForEach loop container, but i'm not sure what to connect it too. Right now i'm connecting it to a Flat File Source, then Derived Column task, then OLE DB Destination. This doesn't seem right to me, but i'm not sure where to start.
Do I need the Flat File Task inside the Data Flow? That's what confuses me - how do I start that process?
Here's the other thing - i'm aware that I can do this much faster in xp_cmdshell, however the CTO just denied my request and won't let me turn it on. I'm going to have to suffer through SSIS to get this job done which really bugs me.
Here are screen shots
Control Flow view -
Inside Data Flow Task -
-
One way to approach this is to assign the file name to a variable as the first step inside the ForEach loop.
In your data flow, you can use the derive column transform to add that file name from this variable into the data flow.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
Thanks for the reply -
I already have a variable which iterates through the file names on the ForEach container - this is how the data from the files is ingested into the table. However, i'm not sure why it's not being passed to me data flow task so that I can insert the file name into the table.
So this is what i'm not sure about - how to capture the file name, pass it to the data flow task, and run it through the Derived Column task and into the table. What exactly do I hard-wire these objects to if i'm already wiring to the variable on the Foreach container?
-
Folks i'm dead in the water here - can anyone help at all? Nothing I try works and I have a feeling i'm overlooking something very simple.
-
Polymorphist (1/9/2015)
Folks i'm dead in the water here - can anyone help at all? Nothing I try works and I have a feeling i'm overlooking something very simple.Quick thought, add a script transformation in the dataflow, assign the variable/file name value to an output column and into the destination table. Alternatively, add an ExecuteSQL task which inserts the variable value into a table, grabs an id value from the table and that becomes a foreign key in the destination table.
On the xp_cmdshell issue, it is just like any other permissions on the server, not dangerous by itself but requires proper management, agree that would be a piece of cake;-)
😎
-
Polymorphist (1/9/2015)
i'm connecting it to a Flat File Source, then Derived Column task, then OLE DB Destination. This doesn't seem right to me, but i'm not sure where to start.Assuming that you are using the File Name Variable in the derived column component then that should be the correct way of doing it.
Have you attached a data viewer to the derived column component output to see what is actually coming out of it?
Do your data types align between variable, derived column component and destination component and table?
Have you mapped the derived column to the table column? This might have to be done manually
Polymorphist (1/9/2015)
I'm going to have to suffer through SSISYou're not suffering, you're learning something you'll need to get used to! 😉
-
Samuel Vella (1/13/2015)
Polymorphist (1/9/2015)
i'm connecting it to a Flat File Source, then Derived Column task, then OLE DB Destination. This doesn't seem right to me, but i'm not sure where to start.Assuming that you are using the File Name Variable in the derived column component then that should be the correct way of doing it.
Have you attached a data viewer to the derived column component output to see what is actually coming out of it?
Do your data types align between variable, derived column component and destination component and table?
Have you mapped the derived column to the table column? This might have to be done manually
Polymorphist (1/9/2015)
I'm going to have to suffer through SSISYou're not suffering, you're learning something you'll need to get used to! 😉
Heh... nope. Considering how easy it is for me to do things like this in T-SQL without SSIS, SSIS will always be in the "suffering" category for me. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/13/2015)
Heh... nope. Considering how easy it is for me to do things like this in T-SQL without SSIS, SSIS will always be in the "suffering" category for me. 😉The suffering is all of your own making 🙂
Incidentally I'd find this much more difficult in T-SQL than SSIS or even C#.
-
Hey Guys,
Thanks for all of the responses. What I ended up doing is convincing the team to do something a bit different rather than trying to capture file names. I've created a batch file which moves all of the files from a production folder to an archive folder on the Amazon S3 server after they are extracted into a table in the database. This way only new files will be in the production folder every day and I don't have to do a comparison. It's not what they wanted but this is the only quick way I could handle this situation. Boss is really on me to get this wrapped-up, he just left my deskside a few moments ago.
What I find confusing about SSIS is all of the point-and-click and UI options. Things get buried, I forget what I'm doing, where things are at. Just to test something to see if it values are being passed requires yet more configuration. I get nervous every time I have to use it. What's interesting to me is that most senior DB folks we interview refuse to use SSIS or have very little experience with it. When I was at a major online travel company they forbade us from using it altogether, the developers just didn't like it. I get that I would increase my worth if I could use it better, but damn do I dread having to get there, LOL.
-
Samuel Vella (1/13/2015)
Jeff Moden (1/13/2015)
Heh... nope. Considering how easy it is for me to do things like this in T-SQL without SSIS, SSIS will always be in the "suffering" category for me. 😉The suffering is all of your own making 🙂
Incidentally I'd find this much more difficult in T-SQL than SSIS or even C#.
Heh... the difficulty is all your own making. 😉
So let's see your SSIS example and your C# example integrated with the SSIS example.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Polymorphist (1/14/2015)
What's interesting to me is that most senior DB folks we interview refuse to use SSIS or have very little experience with it.They're not alone. I'm one of those folks. My only experiences with it have been to write T-SQL for people that use it and come across a problem that they can't solve in SSIS. That and witnessing the nightmare that people had to go through when MS changed from DTS to SSIS. Well, that and the time I was asked to convert all the SSIS packages to T-SQL stored procedures.
That, notwithstanding, some folks swear by it and have gotten quite good at getting it to dance. I just learned to dance differently. Apparently, my boss has, as well, because he won't allow it in our enterprise. We don't even use the "Maintenance Plan" bit of SSIS available in the Object Explorer of SSMS.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/14/2015)
Heh... the difficulty is all your own making. 😉
So let's see your SSIS example and your C# example integrated with the SSIS example.
Just for you Jeff, I'll knock something together on the train home tonight 😉
-
Jeff Moden (1/14/2015)
We don't even use the "Maintenance Plan" bit of SSIS available in the Object Explorer of SSMS.I'm with you on that one Jeff, but I have to disagree with you in general about SSIS; I've found it to be incredibly powerful. It's all about using the right tool for the right job and it surely can't hurt to broaden one's knowledge of the SQL Server suite?
Regards
Lempster
-
Apologies for the delay - a combination of Laptop battery dying, intermittent mobile internet coverage and Visual Studio not liking my Virtual Machine 🙁
Two packages built
One which loads via conventional SSIS methods, the other via c# (embedded in a package as requested)
Both append the file name to each row
Both exclude any files which already exist in the load table.
The packages can be downloaded from here:
https://dl.dropboxusercontent.com/u/1630056/CSFileLoadDemo.dtsx
https://dl.dropboxusercontent.com/u/1630056/SSISFileLoadDemo.dtsx
Sample file is here:
https://dl.dropboxusercontent.com/u/1630056/Import1.txt
They are minimally tested and thrown together. The C# especially should not be used as an example of how to code C#.
Script to create the destination table is here:
CREATE TABLE [dbo].[SampleFileStaging]([col1] [int] NULL,
[col2] [varchar](50) NULL,
[FileName] [varchar](100) NULL
) ON [PRIMARY]
-
Lempster (1/15/2015)
Jeff Moden (1/14/2015)
We don't even use the "Maintenance Plan" bit of SSIS available in the Object Explorer of SSMS.I'm with you on that one Jeff, but I have to disagree with you in general about SSIS; I've found it to be incredibly powerful. It's all about using the right tool for the right job and it surely can't hurt to broaden one's knowledge of the SQL Server suite?
Regards
Lempster
Understood and that's a common comment. But, if I can easily do what I need to do in T-SQL, why does everyone think I need to do it in SSIS? Why do people think that the right tool for the right job is SSIS for all things ETL when it's so simple for me to do the things I need to using T-SQL and the occasional trip to the DOS prompt? If I can do everything that someone else can do using my tools, would it not be beneficial for people using SSIS to broaden their knowledge a bit on the tools that I use so that you don't have to use so many tools?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply