Dynamic File Name from an dstx project

[kxramse]

I am on my first SSIS project and I am kinda learning as I go. The wizard has worked great, I got my SQL statement into the .dtsx project and into a flat file, with the extension I need as well. However what I really want is to have a file name that is composed of different variables. I actually have a small SQL statement that can generate the file name:

select (
		rtrim(vendorID)
		+ '_'
		+ cast((year(LastRunDate)) as Char (4)) 
		+ case
			when (Month(LastRunDate)<=9) then
				'0' + cast((Month(LastRunDate)) as varchar (2))
			else cast((Month(LastRunDate)) as varchar (2))
			end
		+ case
			when (day(LastRunDate)<=9) then
				'0' + cast((Day(lastRunDate)) as varchar(2))
			else
				cast((Day(LastRunDate)) as varchar (2))
			end
		+ '_'
		+ rtrim(CampaignTrackingCode)
		+ '_'
		+'3'
		+ '_'
		+ rtrim( 
		  case
				when VersionNumber <=9 then
					'00' + cast(rtrim(VersionNumber) as char(3))
				when VersionNumber<=99 then
					'0' + cast(rtrim(VersionNumber) as char(3))				
				else
					cast(rtrim(VersionNumber) as char(3))
				end
			   )
		+ '.'
		+ FileExtension
		) 
from stmReceived

I have my data flow task working great, except I can't figure out how to tell it to accept a dynamic name. I'm gathering that this has something to do wtih running a "execute SQL task" and passing my SQL into an "expression" or perhaps a "parameter."

I might have to use a "file system task" to rename the static file name after the "data flow task" has finished creating the same named file everytime it runs? Is that the right direction to keep learning?

Is there a way to send that SQL statement into the data flow task and then into the file name, even though that SQL statement is different than the SQL that builds the actual data file?

Ok... I have to get it done pretty quick. I just need someone to point me into the right direction. Perhaps someone saw a nice tuturial on this somewhere? I can't find any. What I have found through google has been all over the map. Nothing too coherant, perhaps I'm using the wrong keywords.

Anyway, anyone know which way I should go?

Thanks,
Keith

 

[manmaria]

Try this link for 'exec sqltask'

http://www.sqlis.com/default.aspx?58

 

[Catadmin]

Does my post in the following thread help?

thread1555-1265108

It includes info on how to set up a dynamic flat file connection.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kxramse]

I'm still working through this, I may be close but I can't tell yet. I keep trying to mix variable types because it appears the file name is of string type and I don't know what the actual file name field is declared as. Its also complicated by the "string" type isn't avialble outside of the variables screen.

What should I use as the file type of the variable that will contain the file name brought from the above SQL?

 

[Catadmin]

I'm assuming from your last comment that you're trying the Execute SQL Task?

If so, try creating a variable of type NCHAR() where you set it to equal your file name. Here's an example of a T-SQL Job step we have here that runs Winzip.

DECLARE	@String	NCHAR(1000),
		@date	DATETIME

SELECT	@date = getdate()

SELECT	@String = '"C:\Program Files\Winzip\wzzip" -m D:\OFAC\Archive\' + CAST(YEAR(@date)*10000 + MONTH(@date)*100 + DAY(@date) AS NCHAR(8)) + "DailyOFAC.zip @D:\MyFolder\MyFiles.lst"

EXEC xp_cmdshell @String, no_output

However, I don't know how you can take the above and port it into a SSIS global variable. I haven't done that yet. I've only done it through the Script Task.

Does this help?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kxramse]

I came to the conclusion that I'd need to switch tactics and use an zp_cmdshell late last night as well. I was so excited about learning it in SSIS though. Anyway, sometimes I gotta perform a quick solution rather than a fun solution. Its not a bad solution though.

I got my example from:

http://www.sqlmag.com/files/09/49567/Listing_01.txt

This solution differs from you in one small way, it uses EXEC master..xp_cmdShell @winCmd, instead of EXEC xp_cmdshell @String, no_output, is difference what is causing what I came across below?

I get this when I run the command:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this
component is turned off as part of the security configuration for this server. A system
administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information
about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Is there any downside to my DBA giving me these permissions? I'm talking with the DBA today about it.

Anyway, assuming my DBA agrees to let me do this, I shall.

Thanks for your help! I litterally just recently found the same.

Keith

 

[mrdenny]

Enableing xp_cmdshell is a security hole that most good DBAs won't open.

Here's how you can do it in SSIS.

Setup a package wide variable named ExportFileName.
Add a Script Task to the package. In the Script Task editor tell it that you want ReadWrite access to the ExportFileName variable.
In the script window use something along these lines (This goes where it says to add your code).

dim ExportFileName as string
ExportFileName = "c:\FileName.txt"
dts.Variables("ExportFileName").value = ExportFileName

Setup the package so that after the Script Task is done the Data Flow Task is executed.
If you have not done so, setup the text file connection that you want to use (point it to any file that exists).
Rename it to something that makes more sense.
Right click on the file connection object and click properties (not edit). In the properties window, click to the right of Expressions and click the ... button. In the new window, left column select "Connection String" and select the ExportFileName variable in the right column. Click ok. On the Data Flow Task right click and properties. Set the Delay Validation option to true.

Keep in mind that the path should be a network path so that it's the same from your workstation and the server when you run it. If you don't use a network path you'll need to have the same path setup on the server and on your workstation.

If you have any questions let me know.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kxramse]

Thanks for helping.

I think I'm closer. My biggest problem is still getting the SQL results to the variable.

I did this with four sequential steps

1. (Execute SQL TASK) I have a step that prepares my database for the entire run. it basically updates a master table.
2. (Execute SQL TASK) I have a step that figures out the name of the file and from what I am guessing is putting the results in the "DynamicFileName" global variable (String).
3. (Script Task) I have what you gave me here, to the letter. The one difference I made was to replace ExportFileName = "c:\FileName.txt" with ExportFileName=DynamicFileName, and I had dimmed the variable as string right before this assignment. I made the DynamicFileName available for read access. I would have done this step exactly as you described but it would have given the file name a static name and I need it to be from SQL results.
4. (DATA FLOW TASK) runs new SQL to build the file and then save the file.

Every time I try things I get into variable problems with things like "The type of value being assigned to the variable differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type object"

Basically I'm where I was at the other day, in variable type limbo. I wish there was a file attachment option here to show people my dtsx package.

:-(

Any ideas? Its hard to communicate this because there are so many settings that can be changed.

 

[Catadmin]

At what step in the package are you running into the errors? Do the Debug -> Start Debugging and it will tell you what step fails with a big bright red color when it finishes processing.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kxramse]

Depending on whether I set the ExecValueVariable to either the dynamic variable or the export variable it can happen in step 2 or step 3. Right now I turned the ExecvalueVariable to <none> on both step 2 and 3 and so the red box is in step 3. I also see that there is the same problem in step 2 with the variable when you look at the progress tab, but somehow it passes the step green. I've tried all kinds of combinations and they all turn out the same end result, a variable incompatability.

What it makes me feel like is that the result set of my sql statement that comes back in one column and one row is turned into a variable type that isn't "string" but perhaps varchar, or char, or some other variable type.

The SQL is listed at the top of this post. The only change to it was that I gave the entire results of the SQL an alias so that the column wasn't un-named so that after the last ")" it says As DynamicFileName.

Does that make sense?

 

[Catadmin]

kxramse,

Maybe it's just me, but it looks like step 2 and 3 are pretty much duplicates of the same process. If you're figuring out your filename in Step 3, you don't need the execute SQL task to do it (and vice versa). Pick one. I only use the Script Task because that's how I figured out how to do it myself.

If you use the Script Task, don't set the variable in the ExecValueVariable. Go to Edit -> Script and in the ReadWriteVariables enter in the Variable name that you are setting. Make sure the variable is set in the package namespace and not just the script task namespace and make sure it is set to string. Make sure DynamicFileName (I mean the script's internal variable) is set to String also.

The line dts.Variables("ExportFileName").value = DynamicFileName should set the external package variable by itself once you've got everything else in the correct place.

Did that make sense or did I just confuse the issue again?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kxramse]

I don't know if you confused it. I appreciate your interactions and assistance.

Step 2 is there because step 3 can't run the SQL to get the file name from the database. The only reason 3 is there is in hopes that mrdenny's script step 3 idea could somehow bridge my gap and get the SQL into the file name variable.

It all comes down to getting the SQL results into the file name variable so that somewhere it can be passed into the new file name.

Keith

 

[mrdenny]

To get the output from the select statement into a variable in the Execute SQL Task in step 2:

In the "General Section" set Result Set to Single Row.
In the "Result Set" in the left column enter the name of the column that you are returning the filename as. In the right column select the variable to save the filename to.

Be sure that the data is returned as varchar or nvarchar. Be sure that the SSIS variable is setup as string.

If you are pulling the filename from T/SQL in step 2 then step 3 isn't going to be needed. I assumed that you weren't getting the file name from T/SQL. What you have currently for step 3 will allow you to generate the file name without having to connect to the SQL Server.

You'll still want to do this part.

Right click on the file connection object and click properties (not edit). In the properties window, click to the right of Expressions and click the ... button. In the new window, left column select "Connection String" and select the ExportFileName variable in the right column. Click ok. On the Data Flow Task right click and properties. Set the Delay Validation option to true.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kxramse]

It ran!!!

Your advice to check and make sure that instead of the result set in the left column being equal to the column number, in this case 0, making sure it was equal to the name of the alias I gave it inside the TSQL. There were a bunch of articles here and elsewhere on the web saying to put a 0 in that field so that it pointed to the relative column number. I had even tried using "1" and using the alias as 0 in the TSQL but that didn't work. Anyway now it says "DynamicFileName".

So now it works!!!

YEAH!!!

Obviously you guys are well past this point, but if I combine everything I learned from each of you, would it be helpful if I wrote a FAQ? Would people need it?

I look at it this way, there was plenty of stuff out here to teach me how to build the nuclear weapon, but not how to build one of the smaller components of it. LOL.

 

[manmaria]

Go Keith Go.
: )

It is always good to write what you learned.

 

[mrdenny]

If you'd like to write an FAQ feel free. I'm sure that people would read it. These days I don't have time to write many FAQs on this stuff. I bairly have time to post on the site.

You'll want to put 0 for the column name in the left hand column if you set the output type to XML (which can present it's own set of problems if your XML document is larger than 2033 characters). Other than that you'll want to use the column name/alias.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

This forum needs more FAQs, so I say go for it!



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kxramse]

Want to proof my FAQ?

If you need to grab some data from your SQL 2005 database and place it into a flat file, and you wish to give your flat file a dynamic name from the database, follow these instructions.


1. Create yourself an Integrated Services project.
2. Go to Project > Add New Item > SQL Server Import and Export Wizard.
3. Follow instructions from here on using the wizard.
4. Once you have your Data Flow Task you basically have the foundation of everything you need to do. If you double click into the "Data Flow Task" you will see that you have a source query leading into your destination flat file. Ensure your "DelayValidation" parameter is set to True in the properties of your Data Flow Task so that it will wait for your SQL to finish to provide its file name.
4. Go back to the control flow tab.
Now you will need to create yourself a variable to hold your dynamic file name:
5. Go to > View > Other Windows > Variables
6. Create yourself a Package variable by clicking on the "Add Variable" icon in the variables window. Call it DynamicFileName (for this example).
7. Name the variable, use a scope of "Global Variable" and a Data Type of String. ** Come back to this.
8. Create a "Execute SQL Task".
9. Right click on it and choose "Edit".
10. Result set=Single Row
11. Set your connection.
12. SQLSourceType=Direct Input
13. Copy in your SQL in the SQL Statement. See example below:

 select (name 
  + '_'   
  + city 
  + cast(Year(getdate())as char(4)) 
  + '_' 
  + cast(Month(getdate())as char(2))
  + '_' 
  + cast(Day(getdate())as char(2))
   + '.csv') as SQLDynamicFileName
 from companies

The example SQL was simplified in order to show you this can be done. You could easily obtain the year, month and date without using this method if need be. Use SQL if the parts of your file name are from the database.

14. Click on Result Set on the left.
15. Click Add.
16. Your Result Name is the name of the column you have from your SQL. In my case, its SQLDynamicFileName. I used an alias because I was combining many tables. I'm told that if you are writing to an XML File you might need to select 0 as the result Name for the first column.
17. In the Variable Name choose your "User:ynamicFileName" variable
18. Click Ok.
19. Right click on your Execute SQL Task, select Properties.
20. In the "Expressions" field click the drop down and select your variable "User:ynamicFileName".
21. Click Ok.
21. In the Connection Managers, go to your DetinationConnectionFlatFile, right click and select properties.
22. Click the ellipsis and enter in a "ConnectionString" with your variable as the Expression, in my case its DynamicFileName.


(This is my first FAQ, I apologize if I am imprecise in my instructions as I am a novice at SSIS.)

Regards,
Keith


This FAQ is the result of thread1555-1269514, in which manmaria, Catadmin & mrdenny walked me through how to get this task accomplished. I hope this FAQ helps others like they helped me.

 

[kxramse]

Welp, I noticed some errors.

I have a link for the Wizard at:

http://www.databasejournal.com/features/mssql/print.php/10894_3580216_2

Also
7. Name the variable, use a scope of Your package and a Data Type of String.

 

[bt7403]

The FAQ shown works great minus item #20.

20. In the "Expressions" field click the drop down and select your variable "User:ynamicFileName".

Setting this expression does not work. Because the resultset has the variable this expression would not be needed anyway.

I want to thank those who took the time to work this issue to resolution. I have spent 4 days trying to get a variable from a column value to pass through object tasks in SSIS.