4

I am using a GUID for a batch identifier in SSIS. My final output goes to SQL Server.

I know how I can generate one using Select NewId() MyUniqueIdentifier in Sql Server - I can generate one using a query and an Execute SQL task.

I am however looking to do this within a SSIS package if possible without SQL Server available.

Can I generate a GUID within SSIS?

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • do you want to load it to new table, if yes do have data in that table – koushik veldanda Sep 02 '15 at 12:43
  • You could do this is a derived column (DT_GUID)@[User::MyVariable] – MiguelH Sep 02 '15 at 13:23
  • 1
    Why is it not as simple as using an Execute SQL task to populate a SSIS variable with NewId()? – Tab Alleman Sep 02 '15 at 13:35
  • 1
    @Tab, it is that simple, and it is the solution I have implemented. However, I am wondering of a way to do this within SSIS for my own knowledge. – Raj More Sep 02 '15 at 13:48
  • @MiguelH, how do I populate User::MyVariable with a GUID value? – Raj More Sep 02 '15 at 13:49
  • To me, an execute SQL task IS "within SSIS", so can you be more exact about what you mean? – Tab Alleman Sep 02 '15 at 13:49
  • 1
    Sorry! My comment applied to a GUID I had previously generated. A script component would do the trick. See http://microsoft-ssis.blogspot.co.uk/2011/02/create-guid-column-in-ssis.html – MiguelH Sep 02 '15 at 14:20
  • @Tab, what does one do in an environment without SQL Server - only has SSIS and text files to deal with. – Raj More Sep 02 '15 at 15:42
  • SSIS doesn't exist without SQL Server, but assuming you mean you want a solution where you don't have to do a query to a database engine, you can use a script task as per my answer below. – Tab Alleman Sep 02 '15 at 18:22

2 Answers2

3

I had a similar problem. To fix it, I created an SSIS "Composant Script" in which I created a "guid" output. The script VS C# code was the following :

Row.guid = Guid.NewGuid();

Finally, I routed the output as a derived column into my database "OLE DB Destination" to generate a guid for every new entry.

Michael Fayad
  • 1,216
  • 1
  • 17
  • 38
2

Simply do it in an Execute SQL Task.

  • Open the task

  • Under General -> SQL Statement, enter your query Select NewID() MyID in the "SQLStatement" field

  • Under General -> Result Set, choose "Single row"

  • Under Parameter Mapping, Enter your User::myID in Variable Name, "Input" as direction, 0 as Parameter Name, and -1 as Parameter Size

  • Under Result Set, enter "MyID" for your Result Name and type the variable in Variable Name

-Click OK

Done. Note that "MyID" is a value you can choose. EDIT: "User::myID" corresponds to the SSIS variable that you create.

rwking
  • 1,032
  • 6
  • 18