Once the "Varible mapping variable = Filename1 Index = 0" step is done, the file name has been captured. This simple. No other code is required. You can then inspect and/or use the the variable content in your package. Please tell me SSIS rocks!Arthur My Blog

I have a file that was ftp'd and want to use the file name variable to populate a column in a table with the filename that is used as a history table. How do I use the variable that has the file name in it.

Hello donnalc123, You can use a ForEach Loop (even though you get only one file), the luxury of using it is in the automated mapping of the file name (however you like it, e.g. full path, or only the name) being picked (based on a mask) to a package variavble with index 0. Then you can use the variable in any component down the execution pipe. Example: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/67871/Arthur My Blog

Hi, you can use Derived Column transformation in you Data Flow task to add a new column with the variable value. http://www.bimonkey.com/2009/08/the-derived-column-transformation/ David.

Hi Donnalc, The best option you have is FOR EACH LOOP CONTAINER, http://www.sqllion.com/2009/06/programming-foreach-loop-container-%E2%80%93-enumerating-excel-files/ =================================== Rahul Vairagi ========================================== My Blogs: www.sqlserver2005forum.blogspot.com

I really don't want to add a new column to the history table. I just want to put the file name into an existing column

Both solutions do not talk about adding new columns, you simply add a new data column to the data flow, internally (in the package), this remains transparent to the table or your end result. Please take one avenue or another and they will lead you to the same destination.Arthur My Blog

I'm rather new to SSIS packages so bear with me. I created a variable in the package Using a Foreach Loop Container Collection= ForEach File Enumerator Varible mapping variable = Filename1 Index = 0 Using Script task editor to get the file name used the readwrite variable = User::FileName1 Public Sub Main() Dts.Variables("FileName1").Value = System.IO.Path.GetFileName(Dts.Variables("FileName1").Value.ToString()) Dts.TaskResult = Dts.TaskResult.ToString Do I need more code than this? Now how do I use this filename1 variable to populate the Source_Filename column in another table

Once the "Varible mapping variable = Filename1 Index = 0" step is done, the file name has been captured. This simple. No other code is required. You can then inspect and/or use the the variable content in your package. Please tell me SSIS rocks!Arthur My Blog

Hello donnalc123, Once the file name is captured into a variable the most typical option to providing this variable to any downstream components would be via the Derived Column Transformation (DCT): Drag and drop a new DCT to the canvas andSet it to drive a new column (the <add as new column> needs to be chosen in Derived Column setting) from the file name variable;Use the newly created column (of say DT_WSTR type) in for example an OLEDB destination (or Execute SQL Task with a parameter) to map to the Source_filename column of the History table. Using SQL is also possible, it depends if you just update this single field, or you are inserting the data along with other.Arthur My Blog

Hello donnalc123, Once the file name is captured into a variable the most typical option to providing this variable to any downstream components would be via the Derived Column Transformation (DCT): Drag and drop a new DCT to the canvas andSet it to drive a new column (the <add as new column> needs to be chosen in Derived Column setting) from the file name variable;Use the newly created column (of say DT_WSTR type) in for example an OLEDB destination (or Execute SQL Task with a parameter) to map to the Source_filename column of the History table. Using SQL is also possible, it depends if you just update this single field, or you are inserting the data along with other.Arthur My Blog

This is being done in a foreach loop using Foreach Fie Enumerator Collecitons: Retireve file name --Fully qualified Variable Mappings Variable = User::vFilename2 Index = 0 ************************************ Then I populated the variable vfilename2 before the sql task using the script task to get the filename using a read write variable. This worked ok. I tried to use the sql task but still does not work. I don't know if I am setting the options on the edit screen correctly Resultset -- None sqlStatement Connection type -- OLE DB Connection -- ptimedb SQL Source Type -- Direct Input SQL Statement --- Insert into dbo.Source_File_Processing_History (Source_Filename)values (? ) IsQuery StoredProcedure --false BypassPrepare -- false In Parameter Mapping Variable = user:vfilename2 Direction = Input Data Type = VARCHAR ParameterName = 0 Parameter Size = 100 What am I doing wrong or am I missing something?

This is being done in a foreach loop using Foreach Fie Enumerator Collecitons: Retireve file name --Fully qualified Variable Mappings Variable = User::vFilename2 Index = 0 ************************************ Then I populated the variable vfilename2 before the sql task using the script task to get the filename using a read write variable. This worked ok. I tried to use the sql task but still does not work. I don't know if I am setting the options on the edit screen correctly Resultset -- None sqlStatement Connection type -- OLE DB Connection -- ptimedb SQL Source Type -- Direct Input SQL Statement --- Insert into dbo.Source_File_Processing_History (Source_Filename)values (? ) IsQuery StoredProcedure --false BypassPrepare -- false In Parameter Mapping Variable = user:vfilename2 Direction = Input Data Type = VARCHAR ParameterName = 0 Parameter Size = 100 What am I doing wrong or am I missing something?

It still doesn't work perhaps because the table has 2 primary keys file id which is automatically populated and Source_filename which is what I am trying to insert into the table in a new row. I also need to get the date from the package variable (filename1). When I tried to use the filename variable I get the above error. What I have tried so far in the Execute SQL task editor connection type is OLE DB SQLSource type = Direct input in the Parameter mapping used the filename1 variable with parameter name = 0 direction = input in the ResultSet = none my sql statements used that haven't worked Insert into dbo.Source_File_Processing_History (Source_Filename)values (?) Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('filename1').value )

It still doesn't work perhaps because the table has 2 primary keys file id which is automatically populated and Source_filename which is what I am trying to insert into the table in a new row. I also need to get the date from the package variable (filename1). When I tried to use the filename variable I get the above error. What I have tried so far in the Execute SQL task editor connection type is OLE DB SQLSource type = Direct input in the Parameter mapping used the filename1 variable with parameter name = 0 direction = input in the ResultSet = none my sql statements used that haven't worked Insert into dbo.Source_File_Processing_History (Source_Filename)values (?) Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('filename1').value )

For the primary key violations you supposed to get an error. Or I am suspecting you may have a table trigger that rolls back some inserts (e.g. on before insert that check the violation constraints, and silently rolls the transaction back). Is that possible? One thing though why the same SQL works in SSMS? PS: Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('filename1').value )').value ) is not a valid command. Try to set your command from the variable source instead. Use a new variable in which you express (using SSIS Expression): "INSERT INTO dbo.Source_File_Processing_History (Source_Filename) VALUES ('" + @[User::FileName1] + "')"Arthur My Blog

For the primary key violations you supposed to get an error. Or I am suspecting you may have a table trigger that rolls back some inserts (e.g. on before insert that check the violation constraints, and silently rolls the transaction back). Is that possible? One thing though why the same SQL works in SSMS? PS: Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('Insert into dbo.Source_File_Processing_History (Source_Filename)values (dts.variables('filename1').value )').value ) is not a valid command. Try to set your command from the variable source instead. Use a new variable in which you express (using SSIS Expression): "INSERT INTO dbo.Source_File_Processing_History (Source_Filename) VALUES ('" + @[User::FileName1] + "')"Arthur My Blog

I'm still not sure how to move the variable - filename into the history table column Source_filename using the sqltask.

I'm still not sure how to move the variable - filename into the history table column Source_filename using the sqltask.

When you say "does not work" does it mean it generates an error? Or the INSERT does not occur?Arthur My Blog

When you say "does not work" does it mean it generates an error? Or the INSERT does not occur?Arthur My Blog

I have just found you a very good blog post containing a very nice and easy method of adding the file name to a table. Please visit: FileNameColumnName property, Flat File Source Adapter : SSIS Nugget Arthur My Blog

I have just found you a very good blog post containing a very nice and easy method of adding the file name to a table. Please visit: FileNameColumnName property, Flat File Source Adapter : SSIS Nugget Arthur My Blog

I get an error [Execute SQL Task] Error: Executing the query "Insert into dbo.Source_File_Processing_History (S..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I get an error [Execute SQL Task] Error: Executing the query "Insert into dbo.Source_File_Processing_History (S..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Then the file name is the issue perhaps, thing is it needs to be quoted e.g. Insert into dbo.Source_File_Processing_History (Source_Filename)values ('test.dat' ) Is your user:vfilename2 has it in this format?Arthur My Blog

Then the file name is the issue perhaps, thing is it needs to be quoted e.g. Insert into dbo.Source_File_Processing_History (Source_Filename)values ('test.dat' ) Is your user:vfilename2 has it in this format?Arthur My Blog

I donn't want to add the filename as a column to my data file. I want to take and use the variable filename to populate a column in another table

I donn't want to add the filename as a column to my data file. I want to take and use the variable filename to populate a column in another table

I donn't want to add the filename as a column to my data file. I want to take and use the variable filename to populate a column in another table It is not a column in THE FILE, it is a new column added to the data pipe. How are you planning to populate the column in the table with the file name? Is it a SQL UPDATE statement? Or it needs to come in with in a single INSERT when you dump the data into the very target?Arthur My Blog

I donn't want to add the filename as a column to my data file. I want to take and use the variable filename to populate a column in another table It is not a column in THE FILE, it is a new column added to the data pipe. How are you planning to populate the column in the table with the file name? Is it a SQL UPDATE statement? Or it needs to come in with in a single INSERT when you dump the data into the very target?Arthur My Blog

I was planning to use the Execute SQL task, but I'm not sure how to use the variable to do this

I was planning to use the Execute SQL task, but I'm not sure how to use the variable to do this

The table I am trying to insert into has a keyidentity column. Do I need to account for that in the insert command as it is a primary key along with the source_Filename column

The table I am trying to insert into has a keyidentity column. Do I need to account for that in the insert command as it is a primary key along with the source_Filename column

Sure, it is a good learning opportunity, please familiarize yourself with how to pass/map parameters to Execute SQL Task.Arthur My Blog

Sure, it is a good learning opportunity, please familiarize yourself with how to pass/map parameters to Execute SQL Task.Arthur My Blog

How to I use the filename1 variable to populate a column(Source_filename) in my History table? Do I use execute SQL task?

keyidentity? Is this SQL Server? If this field is of type IDENTITY in SQL Server then no. And this insert would have had failed in SSMS, too. Arthur My Blog

keyidentity? Is this SQL Server? If this field is of type IDENTITY in SQL Server then no. And this insert would have had failed in SSMS, too. Arthur My Blog

Use a derived column block and assign the variable to a new column in the flow

Hello donnalc123, Once the file name is captured into a variable the most typical option to providing this variable to any downstream components would be via the Derived Column Transformation (DCT): Drag and drop a new DCT to the canvas andSet it to drive a new column (the <add as new column> needs to be chosen in Derived Column setting) from the file name variable;Use the newly created column (of say DT_WSTR type) in for example an OLEDB destination (or Execute SQL Task with a parameter) to map to the Source_filename column of the History table. Using SQL is also possible, it depends if you just update this single field, or you are inserting the data along with other.Arthur My Blog

Still not having any luck trying to use the variable as a parameter to populate a column