Zulfiqar's web

SSIS: Dynamic file naming

SSIS: Dynamic file naming

Some times, Files might need to be renamed dynamically based on current date (and or time) during a typical ETL process.

File Connection manager could come very handy for this. First initialize your package scoped string typed variable as DirectoryPath pointing to lets say "c:\mydir\mypath". Then use this user defined variable for connectionstring property in the expression for file connection manager. Following formula could be used for expression

@[User::DirectoryPath] + "\\" + (DT_WSTR,4)YEAR(GETDATE())
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + ".txt"

As you notice, Year(GetDate()) is typecasted as DT_WSTR. Refer to books online for further help on typecasting.

Simply point your data source such as ole db to the file task based on the above file connection manager and your resultant file would be created based on dynamic name.

HTH..

ZULFIQAR SYED

SSIS: Passing paramters to and from stored procedure

SSIS: Passing paramters to and from stored procedure

Execute SQL task could be used for this purpose. The applications are very practical and many. If you can not get this to work and are still getting confusing error messages then I recommend installing SQL SERVER 2005 SERVICE PACK 1.

Check out Kirk's post on parameter setup based on connection type such as ado.net or oledb etc.

Here is the test proc

CREATE PROCEDURE [dbo].[testproc]
    @i int ,
    @j int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @j = 33
END

and here is the test script to execute it.

USE ADVENTUREWORKS
GO
declare @m int
exec dbo.testproc @i = 2, @j = @m output

print @m

Inside the ssis package:

Variable settings are as follows:

Name = i
scope = execute sql task
data type = int32
value = 1

Name = i
scope = package
data type = int32
value = 1

In the Execute SQL Task Editor here are the important settings

--General Tab

Resultset = none
connection type = ole db
connection = servername.AdventureWorks
sqlsourcetype = direct input
sqlstatement = exec dbo.testproc @i = ?,@j = ? output

-- Parameter Mapping

variable name = User::i
direction = input
data type = long
parameter name = 0

variable name = User::q
direction = output
data type = long
parameter name = 1

Execute the package and if every thing goes well execute sql task will turn green. If not then please check the following

variables: check the scope, data type etc, upper case or lower case name, make sure that name is exactly the same.
Inside the execute sql task editor:

Check the following settings: Result Set, connection type, connection, specially check the sql statement and parameter mapping. Make sure that that parameter name have the correct ordinal numbers and directions are setup up correctly as input and output and double check the data type.

If it still does not work then check out the error messages in the output window and trouble shoot accordingly.

HTH..

ZULFIQAR SYED

SSIS: Web Service Task

Have you looked into this task. Pretty Cool !!!.

Under the general tab:

1.     Create HTTP connection pointing to free service (at the time of the writing and please try at your own risk) lets say http://www.webservicex.net/uszip.asmx?wsdl
2. Test Connection to make sure you connect.
3. You could download wsdl file to your local computer by setting wsdl file property as c:\GetInfoByZIP.wsdl

Under the input tab

1. Choose service as USZip
2. Method as GetInfoByZip
3. Under the service section, you could hard code uszip as 92660 (for newport beach)

Under the output tab

1. Choose output type as FileConnection
2. File as c:\myfilename.xml

Now execute the Web service task and check out the newly created xml file under c:\. It stores following information in the xml file which is truly awesome

- <NewDataSet xmlns="">
- <Table>
  <CITY>Newport Beach</CITY>
  <STATE>CA</STATE>
  <ZIP>92660</ZIP>
  <AREA_CODE>949</AREA_CODE>
  <TIME_ZONE>P</TIME_ZONE>
  </Table>
  </NewDataSet>

Try it out and applications are limited by your own imagination.

HTH..

ZULFIQAR SYED