We now set up the source of the copy activity Sequential is ticked because we are going to move through all the worksheets names in the table (Ensure that your Worksheets have exactly the same name as what is specified in your table)Ĭlick on the Activities (1) to get to the activity Copy Activity within the Foreach Note that first row only is not ticked because we are bringing all the information through are going to get the entire data set (Value) fed into the GetLookups Lookup. This uses the SQL dataset because we are going to use our SQL table that contains all the names of the worksheets. Lookupįirst of all In Activities search for lookup and drag this into the pane This is the basic pipeline we are going to add. Now to create the pipeline specifically for the lookup This means we can use this one Data set for all the SQL data sources Pipelines Make sure First row as header is ticked (Unless you don’t have a header in Excel) We don’t want to specify any of the location values until we get to the pipeline, including the worksheet If we parameterise them then we can reuse a single data set for lots of other activities within the pipelineĬhoose the Format. Now to come up with the actual source and destination datasets. _LS is good because you can see exactly what are the linked services in the JSON script createdĪgain add in your details (We used a role that we created in SQL Server DB specifically for data factory with GRANT EXEC, SELECT, INSERT, UPDATE, DELETE on all the schemas)Įnsure the connection is successful Data Sets Next, create your Azure SQL Database Linked ServiceĪnd call is SQLDB_LS (Or what ever you feel is the right naming convention. Source and destinationĬall it ADLS_LS and select your Azure Subscription and Storage account.Īt this point the connection was tested and was successful so we didn’t need to do anything further We have populated it like this SELECT 'Lookup A' UNION SELECT 'Lookup B' UNION SELECT 'Lookup C' Data Factory Linked Servicesįirstly we need to provide our linked services. Lookup Names is our seed table and will provide us with the worksheet names Now we need to provide Data Factory with a list of worksheets CREATE TABLE. LabelName has also been added which will be the name of the worksheet.įinally ImportDate is added because we want to know exactly what time this data was imported into the table GO LabelKey has been added just to create a valid key for the table. I have an Azure SQL Database and on it I create the one table that all the reference lookups will go into GO CREATE TABLE. We are going to store the source data within a data lake. We want to do everything in one go in Data Factory.įor this Im going to use a simple example with just 3 worksheets Azure Data Lake Gen 2 This will have a Lookup Name, Code and Description that we can then use for the rest of the project We decide that we want every single lookup in one lookups table in SQL Server. A current project has an xlsx containing around 40 lookups in individual worksheetsĮach worksheet consists of a code and a description
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |